Remote Query Performance There are many ways to improve the performance of remote queries. We take a look at some of those options in the next sections. OPTIMIZE FOR n ROWSUse of the OPTIMIZE FOR n ROWS for distributed applications to limit the number of rows returned in a DRDA client query block so that only a portion of the answer set is fetched. It can also optimize cases when a large number of rows are fetched by allowing a client to request multiple query blocks from a DRDA requester in a single network transmission. This allows you to specify the number of query blocks to retrieve. OPTIMIZE FOR n ROWS is supported for result sets returned by a SELECT statement or by a stored procedure. DRDA Level 3 is implemented to provide the client the ability to specify the number of extra query blocks it wants to receive (instead of the default of one at a time). Any value n will allow the DRDA server to send multiple query blocks in a single transmission. DB2 will send n number of rows of the result set in each network transmission if the result set fits in a query block. If the rows do not fit in a query block, extra query blocks will be sent in the same transmission up to either the query block limit set for the server (DSNZPARM EXTRASRV) or the query block limit set by the client (DSNZPARM EXTRAREQ, which is also the value in DDM MAXBLKEXT on the DRDA requester). You want to be cautious of these parameters, since the default is 100 (query blocks per transmission) and this could negatively affect the performance of your applications depending on your workload mix. You would want to take advantage of this clause when the value n is less than the total number of rows that will fit in a DRDA query block and the application is only going to process the same number of n rows. You can also significantly reduce elapsed time for large query result sets by using OPTIMIZE FOR n ROWS if n is greater than the number of rows that can fit in one DRDA query block, because this will allow the requester to get multiple query blocks with each network transmission. If your DRDA application meets the following criteria you may want to use OPTIMIZE FOR n ROWS:
You do not want to set a large number of query blocks for cursors defined WITH HOLD, because if the requester application commits while several blocks are in the network, DB2 will buffer the blocks in memory. Inactive ThreadsAccess threads that do not hold any cursors are known as inactive threads. Two types of inactive threads are supported: type 1 and type 2. Type 2 Inactive ThreadsType 2 threads are available only for DRDA connections, use a pool of database threads (which can be switched as needed among connections), and use less storage than type 1. You want to use type 2 inactive threads when possible. Distributed Application Tuning Guidelines
|
Team-Fly |
Top |