New Query Block Prefetch Feature with Db2 11.5.6

Frank-Martin · ‎2021 Aug 11

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.