Extra query block
Extra query block support on servers with DB2® Universal Database (UDB) for OS/390® and z/OS® Version 7 or later is configured via the EXTRA BLOCKS SRV parameter on the DB2 DDF installation panel. This support is configured by way of controlling the maximum number of extra query blocks that DB2 can send back to a client for a request. You can set this parameter to a value between 0 and 100. Setting the parameter value to 0 disables the return of extra query blocks. The default value of 100 should always be used to get the most benefit out of this feature, barring any idiosyncrasies in the network that would render this setting less than ideal.
On the client side, where the application accesses DB2 for z/OS either directly through a co-located DB2 Connect™ installation, or through a separate DB2 Connect server installation, there are various means for activating the corresponding DB2 Connect support on a per cursor or statement basis:
- The use of a query rowset size for a cursor
- The use of the 'OPTIMIZE for N ROWS' clause on the select statement associated with a cursor
- The use of the 'FETCH FIRST N ROWS ONLY' clause on the select statement associated with a cursor
DB2 Connect can enable extra query block support using different SQL APIs: - Embedded SQL
-
- The user can invoke extra query block support for a query by specifying either the 'OPTIMIZE for N ROWS' clause, or the 'FETCH FIRST N ROWS ONLY'
clause, or both on the select statement itself.
- With the 'OPTIMIZE for N ROWS' clause, DB2 for OS/390 and z/OS will attempt to block the desired number of rows to return to DB2 Connect, subject to the EXTRA BLOCKS SRV DDF installation parameter setting. The application can choose to fetch beyond N rows as DB2 for z/OS does not limit the total number of rows that could ultimately be returned for the query result set to N.
- The 'FETCH FIRST N ROWS ONLY' clause works similarly, except that the query result set is limited to N rows by DB2 for OS/390 and z/OS. Fetching beyond N rows would result in SQL code +100 (end of data).
- CLI/ODBC
-
- The user can invoke extra query block support for a query through its SQL_MAX_ROWS statement attribute.
- The 'FETCH FIRST N ROWS ONLY' clause is used instead for a DB2 UDB for OS/390 and z/OS 7.1 or later server.
- For Version 7, the query result set is limited to N rows by DB2 for OS/390 and z/OS. Fetching beyond N rows would result in SQL_NO_DATA_FOUND.
- For Version 8 or later, the CLI ensures that only the first N rows are returned to the application via the client Cursor Manager.
- JDBC
- The user can invoke extra query block support for a query through the setMaxRows method. Similar to the CLI/ODBC enablement, DB2 Connect will tag on the 'OPTIMIZE for N ROWS' clause for a DB2 for OS/390 and z/OS 6.x server. DB2 Connect will also tag the 'FETCH FIRST N ROWS ONLY' clause for a DB2 for z/OS 7.1 or above server.
Parent topic: DB2 Connect tuning