|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.