Technical Articles
New Query Block Prefetch Feature with Db2 11.5.6
Introduction
With IBM Db2 11.5.6, the Db2 CLI client provides a new “query block prefetch” feature (not to be confused with the Db2 prefetching features on the database server side). In this blog I would like to explain how SAP ABAP stack systems can benefit from this new feature and how it can be activated.
The Importance of Low Network Latencies for Database Response Times
Database response times comprise both database execution times and network roundtrip times between database server and SAP application servers. Therefore, in addition to database tuning on the database server side (for example, SQL tuning), it’s also very important to monitor the network roundtrip times between database server and SAP application servers. These roundtrip times can easily dominate the average database response time.
SAP provides multiple tools to make monitoring of network roundtrip times easier. For example, you can use the DBA Cockpit with its “Performance -> Network Statistics” screen to get an overview of network latencies in your system. You can also use the tool “db6util -ping” to get an ad-hoc measurement of network roundtrip times between one application server and the database server.
The new Db2 CLI query block prefetch feature helps to reduce the effect of network latencies for SQL queries with a large result set. I will describe all details in the following sections. For simplicity in the following the new feature will be called “CLI query prefetch”.
CLI query prefetch does not help with SQL queries with a small result set or with writing SQL statements. Therefore, monitoring of network latencies will continue to be important.
The “Standard” CLI Query Block Fetch Mechanism
The diagram above shows how the standard block fetch mechanism of the Db2 CLI client works:
When a new query is opened, a request is sent to the database server. The database server executes the query and calculates the first block of the result set. By default, the block size is 64K of data. , I’ve simplified a bit, so the first block contains only 3 rows of data in the diagram.
After the block is received by the application server, the application starts to fetch data. Depending on what the application does with this data, this will take some time on the application server. The Db2 CLI client can serve fetch request from the application without additional database roundtrips until all rows are retrieved from the first result block. In the diagram example, the Db2 client will start another synchronous database request when the application requests row number 4. After this, it will take another network roundtrip and some more database execution time until the next query result block will arrive at the application server. Only then can the application continue its processing.
With the normal query block prefetch mechanism, no additional result block is prepared while the application works. Total execution time of the application is a sum of application times plus database execution times plus network roundtrip times.
The “New” CLI Query Block Prefetch Mechanism in Db2 11.5.6
The Query Block Prefetch Mechanism is a new Db2 CLI client feature. The client will automatically request a new result block in the background whenever a result block arrives, and the result is not yet complete.
CLI query prefetch does not change anything when the query is opened. What’s new is that, when the first query result block arrives at the application server, the Db2 client will automatically request the next result block in the background. The application can start processing the rows from the available result block. When all rows in this block are processed, the application needs to wait for a much shorter time until the next result block is available.
In the diagram example above, the result block containing row 4-6 may have already arrived at the application server when the application requests row 4. The possible performance win depends on the time used by the application, the database processing time, and the network roundtrip time.
For example, CLI query prefetch may not make a large difference when the application processing time is very low or if the sum of the database processing time and the network roundtrip time is low. However, in real scenarios CLI query prefetch may compensate larger network roundtrip times for queries with larger result sets.
For example, in tests, we compared R3load export times of table E071K on a local application server with low network roundtrip times ( < 0.1 ms ) on the one hand with export times on a remote application server with high network roundtrip times ( > 0.8 ms ) on the other. Without CLI query prefetch, the export times were 18 seconds on the local application server and 22 seconds on the remote application server. With CLI query prefetch, the export times were 17 seconds on both application servers.
Db2 CLI will not enable query block prefetch for result sets containing LOB values. So, you won’t see any performance difference for such queries.
CLI query prefetch can come with a slight performance penalty when the application executes and fetches from multiple cursors in parallel, such as in this example
OPEN SELECT1
FETCH ROW 1 FROM SELECT1
OPEN SELECT2
FETCH ROW 1 FROM SELECT2
FETCH ROW 2 FROM SELECT1
In such cases, the OPEN of the second query will have to wait for the database server to return the second result set block for the first query. CLI will automatically disable query prefetch for the following result set blocks of SELECT1 in such cases. This slight performance penalty in rare cases should be easily out weighted by the performance win in other cases. (In the example above, both queries could benefit from Query Block Prefetching if they were executed on different connections. The collision only happens because one connection used only one communication socket.)
Query Block Prefetch Configuration
To enable CLI query prefetch, you only need to add the key QUERYPREFETCH=1 to your db2cli.ini file. Db2 11.5.6 or higher is a prerequisite for using Query Block Prefetching. You must use the Db2 11.5.6 SAP build listed in SAP Note 101809 because this build contains some last-minute fixes for CLI query prefetch.
The DBSL shared library may set QUERYPREFETCH=1 automatically in the future. However, since CLI query prefetch is a new client feature, we would like to collect some more feedback before setting it as a default. All standard Sap test scenarios have been tested with QUERYPREFETCH=1 and no regressions have been found. On the other hand, significant performance improvements have been found in some scenarios.
Performance results from BW test scenarios
CLI query prefetch is a pure Db2 client feature and does not depend on the server-side table organization (Db2 BLU Acceleration or non BLU). It does not even depend on the target database type. IBM Db2 for z/OS databases can also benefit from this feature. CLI query prefetch can be applied to SAP ERP and also to SAP BW systems. We tested SAP BW test cases first because the query result sets in SAP BW tend to be large and CLI query prefetch helps most for queries with large result sets.
Here’s the result from an SAP-internal test of CLI query prefetch that we ran on an SAP BW system:
Test Scenarios shown in the diagram:
- Infocube Load: Lookup of Dimension Table (ROW based)
- Infocube Load: Lookup of Dimension Table (BLU)
- Infocube Load: Lookup of Master Data SID Tables (ROW based)
- Infocube Load: Lookup of Master Data SID Tables (BLU)
- Infocube Load: Lookup of Master Data SID Tables (flat Infocube)
- PSA to DataStore object: SELECT from PSA Table (ROW based)
- PSA to DataStore object: SELECT from PSA Table (BLU)
- PSA to Infocube: Lookup of Master Data SID Tables (ROW based)
- PSA to Infocube: Lookup of Master Data SID Tables (BLU)
- PSA to Infocube: SELECT from PSA Table (ROW based)
- PSA to Infocube: SELECT from PSA Table (BLU)
- PSA to Infocube: Lookup of Dimension Table (ROW based)
- PSA to Infocube: Lookup of Dimension Table (BLU)
In the diagram, orange bars show the test duration with normal query block fetch. Grey bars show the test duration with CLI query prefetch. The first good news is that all grey bars are shorter than the orange bars. In some scenarios, the performance difference is relatively small, maybe because these scenarios execute more SELECT SINGLE statements or execute a mixture of SELECT and INSERT/UPDATE/DELETE workload. In other scenarios, the performance win is larger (up to 40%).
The tests were done on an application server with medium network roundtrip times of 0.2 – 0.3 ms.
Conclusion
Query block prefetch is a very promising new feature of the Db2 CLI client available as of 11.5.6. All tests in the SAP lab environment with the new feature have been positive. I suggest that you try to enable the new feature in your SAP system running on IBM Db2 on Linux, UNIX, and Windows after you have upgraded to Db2 11.5.6.
As always, feel free to provide any feedback.
When I recently explained the query block prefetch feature to a colleague we came up with an analogy involving a bar and a beer order. I would like to present this analogy here 🙂
The standard query block fetch algorithm works as follows:
You enter a bar and order a beer. After the waiter serves the beer you take your time to drink the beer and afterwards you may want to look for the waiter to order a second beer. And so on ...
The more efficient query block prefetch feature works as follows:
You enter a bar and order a beer. When the waiter arrives and serves your first beer you immediately order a second beer. After taking your time to drink the first beer there is a good chance that your second beer has already arrived or will arrive much sooner since the waiter has been able to work on your second beer while you were still drinking your first one. Don't forget to order your third beer when you start drinking your second beer ! And so on ...
Several customers gave positive feedback after activating the new query block prefetch feature on their systems and saw no problems. However, one customer reported intermittent core dumps after activating this feature.
After an analysis we found that the intermittent problems were related to SELECT ... WITH HOLD cursors with frequent COMMITs while the data from those cursors were fetched. The problem turned out to be caused by a defect in the Db2 CLI client which will be fixed in 11.5.7.0 SAP5 .
The reported SELECT ... WITH HOLD problem scenario is not common but to avoid it completely, we recommend using at least Db2 11.5.7.0 SAP5 or higher when activating the new query block prefetch feature.