Why are we still giving away thousands of dollars to IBM? Is it because we are too lazy to rewrite our code to leverage Multi-row FETCH and Multi-row INSERT vs using Single-row FETCH? Let me show you why it is so important to leverage these very neat features, which were introduced with DB2 for z/OS Version 8 in March 2004. They should dramatically improve the performance of DB2 queries. When I heard about these features, I thought “wow, cool stuff, and not too difficult to implement”, so I expected DB2 sites to adopt these new functionalities quickly.
12 years later I am still seeing things like this:
As we will find out below, the majority of these 7,097 SQL statements are Single-row FETCHes. Just to make it clear: Single-row FETCHes are not automatically something bad or worrying. It makes no sense to implement all Cursors using Multi-row FETCH or convert all existing Single-row FETCHes into Multi-row FETCHes, as changing these relates to efforts changing program code and testing this. It makes most sense for programs, which fire off hundreds of FETCH statements for a specific Cursor.
Before we have a closer look at the real-world example above we should take a glance at the differences and determine how to best leverage best performance gains according to benchmarks. Multi-row FETCH retrieves multiple rows at once into an array in the application program, so with one FETCH you can obtain more data with less overhead.
Benchmarks have shown that best performance improvements can be achieved when FETCHing 50-100 rows into an array instead of one row after the other. This implies that it is not necessary to convert FETCHes which — on average — only get executed 5-10 times. However, new Cursors — FETCHing more than 10 rows from the Database should be implemented using Multi-row FETCH.
The following image provides an impressive real-world example, which has been captured with Dynatrace’s Application Monitoring CICS Agents. It shows all FETCHes on the DB2 Database sorted by Executions per transaction:
With a drilldown into the Dynatrace PurePath it becomes obvious where in the CICS transaction, and in which program (COBOL, PL/1, Assembler or any other language), the Cursor is opened, and how many FETCHes are executed after the OPEN, and also how much CPU each FETCH is consuming:
Comparison of CPU consumption (Single-row vs. Multi-row FETCH)
Let’s use the transaction above — with an average of 6,000 FETCH statements in a Cursor — and do some math on the costs.
If each of these FETCH statements consumes 0.2 milliseconds of CPU, then it equals to 6,000*0.02 ms =120 ms of CPU for these FETCHes in this single CICS transaction.
Assume a Multi-row FETCH uses 50 times more CPU than a Single-row FETCH, which is in this case 1 ms.
If you FETCH rows in blocks of 100, you only need 60 Multi-row FETCHes instead of 6,000 Single-row FETCHes. The CPU usage is then 60 * 1 ms, which is 60ms for the FETCHes.
CPU Usage using Single-Row FETCH: 120 ms per transaction
CPU Usage using Multi-Row FETCH: 60 ms per transaction
In this example using Multi-Row FETCH a 50% CPU savings is attained, which is confirmed by numbers from IBM and on different forums which project 25%-60% savings.
Comparison of actual cost (Single-row vs. Multi-row FETCH)
As 50% savings in CPU time for these FETCHes is a very abstract figure, let’s do some calculations to get an idea about possible savings in real money.
If we take a cost of € 720 per Mainframe CPU hour, this is € 0.20 per CPU second.
The Single-row FETCHes in one transaction take 0.12 CPU seconds. If this CICS transaction is executed 50,000 times per day, this results in 6,000 CPU seconds per day, multiplied by € 0.20 we get € 1,200 per day, multiplied by 250 working days per year, this results in annual costs of € 300K when using Single-row FETCH. With Multi-row FETCH this can be reduced by 50%, which is €150K savings per year! Even if the transaction would only be executed 10,000 times per day, the savings would still be 30K per year. Remember that this is for just one Cursor using Single-row FETCH in the application, and there could be several of these burning unnecessary CPU every day.
Make an assessment of your Mainframe SQLs to identify Cursors which execute a high number of FETCHes per transaction, using Dynatrace’s End to End Application Monitoring solution. Convert the most excessive ones to Multi-row FETCH. As the example above shows this exercise pays for itself very quickly. And please note that the Multi-row concept is also available for INSERT!