This section discusses how to use the PROTOCOL parameter and different block sizes in a DRDA protocol environment to obtain maximum performance from the database manager. For information on setting up this type of environment, refer to the DB2 Server for VM System Administrationor the DB2 Server for VSE System Administration manuals.
The PROTOCOL parameter specifies the types of protocols that the application server can process and the types of protocol under which the application requester runs.
On the application server, the PROTOCOL parameter is specified in the SQLSTART EXEC. The PROTOCOL parameter has two options on the application server, SQLDS and AUTO. When PROTOCOL=SQLDS is specified, the DB2 Server for VM application server allows access from DB2 Server for VM application requesters only. (The application requesters and application servers can be in either a local or remote environment.) This is the default option. When PROTOCOL=AUTO is specified, the DB2 Server for VM application server allows access from DB2 Server for VM application requesters and non-DB2 Server for VM application requesters.
On the application requester, the PROTOCOL parameter is specified in the SQLINIT EXEC. The PROTOCOL parameter has three options on the application requester, SQLDS, AUTO and DRDA. When PROTOCOL(SQLDS) is specified, the DB2 Server for VM application requester cannot connect to a non-DB2 Server for VM application server. This is the default option. When PROTOCOL(AUTO) or PROTOCOL(DRDA) is specified, the DB2 Server for VM application requester can connect to DB2 Server for VM application servers and non-DB2 Server for VM application servers.
When a connection is made between the application requester and the application server, the combination specified by these parameters determines the protocol to be used (either SQLDS protocol or DRDA protocol).
Table 5 shows the protocol used between the application requester
and the application server.
Table 5. Protocol Used Between the application requester and the application server
| Application Requester | Application Server | |||
| DB2 Server for VM (SQLSTART) | Non-DB2 Server for VM (including DB2 Server for VSE) | |||
| SQLDS | AUTO | |||
| DB2 Server for VM (SQLINIT) | SQLDS | SQLDS | SQLDS | Not Allowed |
| AUTO | SQLDS | SQLDS | DRDA | |
| DRDA | Not Allowed | DRDA | DRDA | |
| Non-DB2 Server for VM | Not Allowed | DRDA | Not Applicable | |
When the DB2 Server for VM application server is started with PROTOCOL=AUTO, DRDA "handshaking" occurs (unless the application requester is a DB2 Server for VM application requester that has been initialized with PROTOCOL(SQLDS)). Handshaking is an identification exchange between the application server and the application requester. During this handshaking sequence, information is exchanged between the application requester and the application server. This exchange includes CCSID information and generation of an LU 6.2 LUWID.
For more information on handshaking, see the discussion on accessing a remote relational database manager in the Distributed Relational Database Architecture Reference manual.
The PROTOCOL parameters used also affect CCSID conversion. If either the application requester or application server specifies SQLDS for the PROTOCOL parameter, the application requester default CCSIDs are ignored, and the application server CCSIDs are assumed.
Application requester CCSIDs are used when:
When communication is between a DB2 Server for VM application server and a DB2 Server for VM application requester, the AUTO option yields the same performance advantages as the SQLDS option except that it has a slight overhead when establishing a connection with the application server. Specifying the AUTO option on the DB2 Server for VM application server has many advantages. This option allows the application server to receive both SQLDS protocol or DRDA protocol, from both DB2 Server for VM application requesters and non-DB2 Server for VM application requesters. If you specify the AUTO option on the DB2 Server for VM application requester, it makes the necessary adjustments for both DB2 Server for VM application servers and non-DB2 Server for VM application servers.
When PROTOCOL(DRDA) is specified on the application requester, DRDA protocol is forced for connections, even if the target is a DB2 Server for VM application server. The DRDA option is useful when you are doing prototype testing between a DB2 Server for VM application requester and a DB2 Server for VM application server to model problems that may occur in communications with a non-DB2 Server for VM application server. You can also use the DRDA option to test SQL extensions only available in a DRDA protocol environment, for example, a larger block size.
The database manager lets you use blocking for row insertion and row retrieval. Blocking improves performance in multiple user mode because data is sent between your program and the database manager in blocks of rows (rather than one row at a time). This reduces overhead from communications between the application server and the requester. Most applications that do multiple-row insertions or retrievals would benefit from blocking.
To use blocking, specify the BLOCK parameter when preprocessing the program. (For extended dynamic statements, specify the BLOCK parameter on the CREATE PROGRAM statement.) When you run the program, blocking is automatically used for:
It is unnecessary for programs to explicitly handle the blocks because they are managed by the database manager. With the SQLDS protocol, 8KB blocks are used for both fetch and insert blocking. With the DRDA protocol, insert blocking is disabled and the rows are inserted one at a time; however, the block size for fetch blocking can be set by the application requester from 1K-byte to 32K-byte by using the QRYBLKSIZE option of the SQLINIT EXEC (see the DB2 Server for VSE & VM Database Administration manual).
| For VSE Users |
|---|
|
In VSE with the DRDA protocol, insert blocking is disabled and the rows are inserted one at a time; however, the block size for fetch blocking can be set by the application requester from a minimum value of 512 bytes to a maximum value of 32KB - 1 byte (32767). |
| Note: | Blocking is only useful if the block size is sufficiently large that many rows can be blocked (that is, it must be greater than the maximum row length). |
For retrievals, as many rows as the block will hold are sent to the application requester on the first fetch (with the DRDA protocol, the first block is sent with the OPEN statement). When the program fetches all the rows in the block, the next fetch that it issues causes another block to be sent. The program never needs to explicitly request a block.
For insertions, the blocks are also handled automatically. Whenever the program issues a PUT, a row is added to the block. When another row cannot fit into the block, the resource adapter sends the block to the database manager.
The database manager does not do blocking for single user mode applications. Because both it and the application run in the same partition (for VSE) or machine (for VM) there is no cross-partition/machine communication overhead to be saved. Programs that have been preprocessed using the BLOCK parameter do not need to be re-preprocessed to run in single user mode. There is an automatic suppression of the blocking; no warning is sent to the program at run time. Some programs, however, process SQL statements dynamically at run time by using the PREPARE statement. These programs, when preprocessed with the BLOCK option, will receive a runtime warning if a dynamically processed statement is disqualified for blocking.
In some instances, there is also suppressed blocking in multiple user mode. Suppressed blocking for a cursor occurs when:
In all cases, a warning is sent to the program, in the SQLCA, to let it know that blocking was suppressed, and execution continues. Notice that the database manager suppresses blocking on a cursor level. It may be doing blocking for some cursors in a program even though the blocking for other cursors is suppressed.
The advantages of fetch and insert blocking are not limited to user programs. DB2 Server for VSE & VM facilities take advantage of blocking as well. The DBS utility and ISQL take advantage of blocking. Refer to the DB2 Server for VSE & VM Database Administration, and the DB2 Server for VSE & VM Interactive SQL Guide and Reference manuals. (ISQL use of blocking is limited to fetch blocking.)
One minor performance disadvantage to using blocking is that the database manger uses extra virtual storage (equal to the block size) for every open cursor. The storage is freed when the user closes the cursor or when the user ends the logical unit of work (whichever comes first). This not only applies to user applications, but to the DBS utility. (ISQL only has one cursor open at a time because a user can only issue one SELECT statement at a time.)
Another minor performance disadvantage is that in not using a block worth of data, you pay the overhead of that block. For example, only 10 rows are returned in a block capable of holding 200 rows.
For more information on using fetch or put operations in programs with blocking, refer to the DB2 Server for VSE & VM SQL Reference and DB2 Server for VSE & VM Application Programming manuals.
The SYNCHRONOUS parameter of SQLINIT EXEC determines whether synchronous or asynchronous communication is used between the user and database machines. Synchronous communication performs better than asynchronous communication but has the following restrictions:
We recommend that you use synchronous communication primarily when running a well-tested production batch application against local application servers. Always use the default, asynchronous communication, with interactive programs such as ISQL.