Remote Query Performance

Team-Fly

---

 

	DB2 Universal Database for OS/390 v7.1 Application Certification Guide By Susan Lawson
	Table of Contents
	Chapter 10.  Accessing Distributed Data

---

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 ROWS

Use 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:

• Read-only queries fetching a large number of rows.

• SQL cursors rarely being closed before fetching entire result set.

• When cursor is open, no statements other than FETCH are used.

• Multiple cursors defined with OPTIMIZE FOR n ROWS not issuing FETCHes.

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 Threads

Access 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 Threads

Type 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

• Use stored procedures to reduce network traffic.

• Use COMMIT ON RETURN YES to issue an implicit commit for a stored procedure when there is a return from the CALL statement.

• Commit frequently.

• The fewer SQL statements, the better.

• Use the SQL RELEASE statement to release the remote connection at commit time, and also use the DISCONNECT(EXPLICIT).

• Use the CURRENT RULES of DB2 (not STD) special register so DB2 can send multiple blocks of data back to the program when requesting LOB data.

• Use DRDA type connections instead of the DB2 private protocol.

---

Team-Fly

Top