While the blocking of rows for a query result set is nothing new, DB2 for z/OS (formerly called DB2 for OS/390) since its Version 6.1 release has had the capability of returning multiple query blocks in response to an OPEN or FETCH request to a remote client, such as DB2 Connect. Rather than repeatedly sending requests to the DB2 for z/OS server requesting one block of row data at a time, the client can now optionally request that the server send back an additional number of query blocks. Such additional query blocks are called extra query blocks.
This new feature allows the client to minimize the number of network line turnarounds, which has a major impact on network performance. The decrease in the number of requests sent by the client to the server for query blocks translates into a significant performance boost because switching between a send and receive is an expensive operation in terms of performance. DB2 Connect can now exploit this performance enhancement by requesting extra query blocks by default from a DB2 for z/OS server.
To take full advantage of the return of extra query blocks (each can be up to 32K bytes long) for the preferred network protocol of TCP/IP, Window Scaling extensions are also enabled as architected under RFC-1323 in DB2 Connect. This feature allows TCP/IP to dynamically and efficiently adjust the send and receive window sizes to accommodate the potentially large amounts of data returned by way of the extra query blocks.
Extra query block support in DB2 for z/OS servers on Versions 6.1 or later is configured via the EXTRA BLOCKS SRV parameter on the DB2 DDF installation panel. This parameter controls the maximum number of extra query blocks that DB2 can send back to a client for a request and can be set 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 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 through the use of:
Option 1 is not covered under in this section because it was already implemented as part of DB2 for z/OS Scrollable Support in DB2 Connect Version 7.1 FixPak 2. Our focus is on the use of options 2 and 3 instead to enabling extra query block support using different SQL APIs as follows:
Window Scaling is supported as of FixPak 4 on all Windows and UNIX platforms that support the RFC-1323 extensions for TCP/IP. This feature can be enabled on DB2 for Windows and UNIX via the DB2 registry variable DB2SORCVBUF. To enable Window Scaling, set the DB2 registry variable DB2SORCVBUF to any value above 64K (for example, on DB2 for Windows or UNIX, you can issue db2set DB2SORCVBUF =65537). The maximum send and receive buffer sizes are dependent on the specific operating system. To ensure that buffer sizes configured have been accepted, the user can set the database manager configuration parameter DIAGLEVEL to 4 (informational) and check the db2diag.log file for messages.
For Window Scaling to take effect, it must be enabled on both ends of a connection. For example, to enable Window Scaling between the DB2 Connect workstation and the host, this feature must be active on both the workstation and the host, either directly through the operating system TCP/IP stack, or indirectly through the DB2 product. For instance, for DB2 for z/OS, Window Scaling can currently only be activated through the operating system by setting TCPRCVBUFRSIZE to any value above 64K.
If a remote DB2 client is used for accessing host DB2 through a DB2 Connect server workstation, Window Scaling can be enabled on the client also. By the same token, Window Scaling can also be enabled between a remote DB2 client and a workstation DB2 server when no host DB2 is involved.
While Window Scaling is designed to enhance network performance, the expected network performance improvement does not always materialize. Interaction among factors such as the frame size used for the Ethernet or token ring LAN adapter, the IP MTU size, and other settings at routers throughout the communication link could even result in performance degradation once Window Scaling has been enabled. By default, Window Scaling is disabled with both the send and receive buffers set to 64K. The user should be prepared to assess the impact of turning on Window Scaling and perform any necessary adjustments to the network. For an introduction to tuning the network for improved network performance, refer to the white paper at http://www.networking.ibm.com/per/per10.html.