Multi-row fetching lets you return up to 32,767 rows in a single API call with a potential CPU performance improvement near 50 percent. Multi-row fetching works for static or dynamic SQL, scrollable or non-scrollable cursors, and UPDATEs and DELETEs. The sample programs DSNTEP4, which is DSNTEP2 with multi-row FETCH, and DSNTIAUL also can exploit multi-row FETCH.
The following list highlights two key reasons to implement multi-row FETCH capability:
The first way to take advantage of multi-row FETCH is to program for it in your application code. The second way to take advantage of multi-row FETCH is in your distributed applications that are using block fetching. After you are in compatibility mode in DB2 V8, the blocks that are used for block fetching are built using the multi-row capability without any code change on your part, which results in significant savings for your distributed SQLJ applications. In one situation, when using this feature for a remote SQLJ application migration from DB2 V7 to DB2 V8, the CPU did not increase.
The results of implementing this feature can affect performance. In our tests of a sequential batch program, the use of 50-row FETCH (the point of diminishing return for our test) of 39 million rows of a table reduced CPU consumption by 60 percent over single-row FETCH. In a random test where we expected on average 20 rows per random key, our 20-row FETCH used 25 percent less CPU than the single-row FETCH.
Note: Multi-row FETCH is a CPU saver, but it is not necessarily an elapsed time saver.
When using multi-row FETCH, the GET DIAGNOSTICS statement is not initially necessary. We recommend that you limit its use due to the high CPU overhead. Instead, use the SQLCODE field of the SQLCA to determine the status of your FETCH:
If you receive an SQLCODE 100, check the SQLERRD3 field of the SQLCA to determine the number of rows to process.
|
Copyright © 2013 CA Technologies.
All rights reserved.
|
|