Comparing batch SQL programs - DSNTEP2 & DSNTEP4

Most of us still choose to run batch SQL's using DSNTEP2, why ? Did you know DB2 9.1 for z/OS has introduced DSNTEP4 ? By changing DSNTEP2 to DSNTEP4 - It will still help you in executing batch SQL's. Then why should we use DSNTEP4 ?

DSNTEP4 has an additional feature of MULTI ROW FETCH ( defaulted to 100 rows/fetch ) while executing batch SQL's. Multi Row Fetch mechanism enhances the performance of the query and saves DB2 CPU, thus DSNTEP4 is faster compared to DSNTEP2 considering the CPU part. The Multi Row fetch has a tendency to disable DB2 parallelism, not so good when it comes to DB2 parallelism part.

But wait, just changing DSNTEP2 to DSNTEP4 may be a good way to start of your day. But you will have to look at other areas which may be very important for your shop

Here is how a DSNTEP2 batch SQL step would look :

//EXECQERY EXEC PGM=IKJEFT01,DYNAMNBR=20
//SYSPRINT  DD SYSOUT=*
//SYSTSPRT  DD SYSOUT=*
//SYSTSIN     DD *
DSN SYSTEM(TEST)
RUN PROGRAM(DSNTEP2) PLAN(DSNTEP2) PARMS('/ALIGN(LHS) MIXED')
END
/*
//SYSIN         DD *
SELECT * FROM USER01.TEST_TABLE WITH UR;

DSNTEP4 batch SQL step :

//EXECQERY EXEC PGM=IKJEFT01,DYNAMNBR=20
//SYSPRINT  DD SYSOUT=*
//SYSTSPRT  DD SYSOUT=*
//SYSTSIN     DD *
DSN SYSTEM(TEST)
RUN PROGRAM(DSNTEP4) PLAN(DSNTEP4) PARMS('/ALIGN(LHS) MIXED')
END
/*
//SYSIN         DD *
SELECT * FROM USER01.TEST_TABLE WITH UR;

Question while using DSNTEP4 for batch SQL :

1) Do you want to consider running batch SQL using DSNTEP4 in production ? - If yes, then make sure you run it in an outage window. Why ? DSNTEP4 has a tendency to disable DB2's parallelism which may hamper other queries running on the table being accessed by your DSNTEP4 batch

2) Do you consider running batch SQL using DSNTEP4 in test/development region ? - If yes, then you have made a good choice on saving precious DB2 CPU seconds and reducing the elapsed DB2 time.

I ran test to see if it actually works ( well, you can say I challenged them ). I ran a batch SQL - SELECT * FROM USER01.TEST_TABLE using DSNTEP4 and DSNTEP2, TEST_TABLE contained 86222 rows using the above STEP example, here are the results

DSNTEP4 :

DSNTEP4 performed 1 OPEN Cursor statement, 863 fetches and 1 CLOSE Cursor statement to complete the SELECT query processing. Since the default MULT_FETCH value is 100, to retrieve 86222 rows DSNTEP4 used : 862 Fetches * 100 Rows/Fetch + 1 Fetch  = 86222  rows , The 863rd fetch statement retrieved 22 rows.

DSNTEP2 :

DSNTEP2 performed 1 OPEN Cursor statement, 86223 fetches and 1 CLOSE Cursor statement to complete the SELECT query processing

Summary :

This pretty much explains the concept of multi row fetch cursor which was explained in my previous blog post. Note the drop in DB2 CPU times. You might see a considerable reduction in DB2 CPU time for a perfectly tuned query if you are comparing DSNTEP2 and DSNTEP4, try it out in your test subsystem - the satisfaction is truly immense.

To be researched :

1) For DSNTEP2, Why the number of fetches = numbers of rows + 1 ?
2) For DSNTEP2 and DSNTEP4, Need to analyze why the number of GETPAGES increased, in our case increased by 10 pages ?

OPTHINT - A step by step approach

The word OPTHINT can be new to many, I will try to keep the post simple and easy to understand - So that you can along with a pop corn be able to grasp good amount of information posted here. To begin with - OPTHINT also called as an OPTimzer HINT is a method to teach DB2, yes teach DB2 to use a particular access path for your SQL query. When I say - particular access path, it means I have an access path in mind and I want DB2 to use it - Right away !!

If you look at the PLAN_TABLE of your application program, you will find two columns OPTHINT and HINT_USED. You may find some data populated or left blank in most of the cases when you query it with SELECT statement. These are the mastermind columns of the PLAN_TABLE which help us establish a user defined access path for the query.

Grab a test module in your test subsystem and try the following steps. Needless to mention, after seeking appropriate approvals etc from your application programers, DM's etc - Just to let them know you want to ' learn ' and ' help ' - It's a good practice and I follow it in my organization

Why do you need OPTHINT ?

I have a query that is performing bad/good/worse by accessing one of the secondary indexes present on the table. Lets name the index as IX_0002. I am keen to check the behavior of the query with IX_0001, which is the primary index for the table.

How do I establish a OPTHINT rule ?

Step 1 :

All the queries in the PLAN_TABLE are designated with a QUERYNO, So now you know your query for which you wish to apply the method. First query the PLAN_TABLE values for the QUERYNO of your choice using

SELECT  QUERYNO, ACCESSNAME, TNAME, ACCESSTPYE FROM PLAN_TABLE WHERE QUERYNO = 1234 ;

Notice the ACCESSNAME - IX_0002, it is the secondary index and we want DB2 optimizer to choose IX_0001 which is the primary Index for our table TAB0001

Step 2 : Optional

If your shop happened to use CA or BMC tools you can very well try to use the edit function and edit the PLAN_TABLE data and repeat the row on which you wish to apply OPTHINT

Step 3 :

Set your SPUFI session on ROLLBACK mode to avoid any unnecessary updates or wrong query executions etc.

Use the UPDATE statement to update the access path, i.e. ACCESSNAME column ( your Index name ) for the QUERYNO of your choice. You should also update the OPTHINT column with a value - Preferred value would be the Index name itself

Here is how the UPDATE statement would look for the above scenario

UPDATE APPLN.PLAN_TABLE
SET ACCESSNAME='IX_0001' WHERE QUERYNO = 1234 ;

Commit your SPUFI session if ' 1 ' row is affected else ROLLBACK and check your UPDATE query !! There are times where ' 2 ' or more rows can get affected due to the JOIN methods in your query - You can proceed with such results.

UPDATE APPLN.PLAN_TABLE
SET OPTHINT='IX_0001' WHERE QUERYNO = 1234 ;

Commit your SPUFI session if ' 1 ' row is affected else ROLLBACK and check your UPDATE query !! There are times where ' 2 ' or more rows can get affected due to the JOIN methods in your query - You can proceed with such results.

Step 4 :

In the BIND card of your application program use the OPTHINT parameter and pass the OPTHINT value as shown below and BIND the program. Once the BIND is successful you will be notified as to how many rows were involved in the OPTHINT operations.

Step 5 : Optional

Revisit the PLAN_TABLE and look for the value of the column HINT_USED against the same query no. Use the below query :

SELECT * FROM APPLN.PLAN_TABLE WHEREHINT_USED = 'IX_0001' ;

The SPUFI result should display one row for our test case. The ACCESSNAME would have changed from IX_0002 to IX_0001, the MATCHCOLS value also change depending upon the index arrangement and WHERE predicates.

Step 6 :

Run your application program and validate the savings if any. Most of the times OPTHINT does not give expected results - So you may want to roll it back ?

How to remove OPTHINT rule ?

Assuming we performed the above steps -

Step1  :

Populate the BIND card for the program and remove the OPTHINT parameter and issue a BIND to rollback. The populated BIND card for your program will by default contain the OPTHINT parameter and you will have to manually delete the line containing the OPTHINT from the BIND card

Step 2 :

Verify the access path roll back by using

SELECT * FROM APPLN.PLAN_TABLE WHERE QUERYNO = 1234 ORDER BY BIND_TIME

Two or more rows would be populated, the value of the OPTHINT in the first row should be '            ' ( spaces ) and ACCESSNAME should be IX_0002

Conclusion and Summary :

Its always good to test and check for a better access path. OPTHINT does not always provide or guarantee optimum results due to various factors such as Index definition, usage, cluster ratio etc. But you can always roll it back and bring the systems back to how they were prior to the OPTHINT operation.

The UNION Clause - Saving DB2 CPU

I am about to reveal a thumb rule for performance tuning and I want you to try it out as well on your test tables to see if it works. Have you ever encountered an SQL query while performing the famous ' SQL HUNT ' with lots of open and close brackets, AND clause, OR clause and several other predicates ? If I were to post an example of such a query it would be -

SELECT * FROM TEST.TABLE WHERE
(( COL1 = :HV-01 AND COL2 = :HV-02 AND COL3 = :HV-03 )
OR
( COL1 = :Hv-01 AND Col2 <> :HV-02 AND COL3 = :HV-03 )) ;

This is a complex query to the DB2 optimizer. When I ran an explain on this query, the ACCESSTYPE returned a value of I and the MATHCOLS had a value of 0. The Index was present on COL1, COL2, COL3. The query was taking a long time to run and I was figuring out what can be done to improve the performance.

Eureka !! Something strikes my mind. If you have read my earlier post, you will find exactly a similar query that was tuned using the UNION clause. Well, why not apply the same rule to the above query and improve performance ?

The query was changed to -

SELECT * FROM TEST.TABLE WHERE
COL1= :HV-01 AND COL2= :HV-02 AND COL3= :HV-03

UNION

SELECT * FROM TEST.TABLE WHERE
COL1 = :HV-01 AND COL2 <> :HV-02 AND COL3 = :HV-03  ;

The total number of rows selected were exactly same and now comes performance - ACCESSTYPE I and MATCHCOLS value of 3.

Thumb Rule if you agree to the above fact -

Replace the open brackets, OR clause and close brackets with the UNION clause wherever possible and split the query. It works all the time for such scenarios and we can perform the SELECT operation and validate the results !. Making the query simpler for the DB2 optimizer to decide the best possible access path by using the UNION clause is a boon for the application.

Revisiting the SQL Queries

One way to improve your application performance is to scan through the queries used in them. Most of the time, the design of the query would have been pretty complex and the DB2 optimizer was unable to determine a ' good ' access path. A good practice would be to visit the application plan table and look for query numbers ( QUERYNO ) having ACCESSTYPE & MATCHCOL value of I and 0 respectively. You can also look as to why it is R & 0. Using your shops administration tools ( CA - Platinum or IBM Administration tool or any other ) list out the queries which you plan to re-visit.

Here is my experience on one of the query that I modified  -

SELECT * FROM PRDNSCH.TABLE01 WHERE
( ( COL1 = :HV-01 AND
COL2 > :HV-02 ) OR
(   COL1 > :HV-03 ) ) ;

This query had an ACCESSTYPE value of I and a MATCHCOLS value of 0, its obvious isn't it - open and close brackets, OR clause : DB2 optimizer went dizzy and could not help much with respect to access path even if an index was present on COL1 & COL2.

I managed to split the query into two queries using the UNION clause. Here is how the query looked after the split -

SELECT * FROM PRDNSCH.TABLE01 WHERE
COL1 = :HV-01 AND
COL2 > :HV-02

UNION

SELECT * FROM PRDNSCH.TABLE01 WHERE
COL1 > :HV-03     ;

The first query above the UNION clause now had an ACCESSTYPE & MATCHCOLS value of I & 2 while the query below the UNION clause had an ACCESSTYPE & MATCHCOLS value of I & 1 respectively. The access path improved and the application program run time efficiency improved proportionally.

How was I sure that the these queries would result the same amount of rows ? Here is how you ensure it. You will have to first get some unique values from the table or approach your application development team for the same. Feed those values and perform a SELECT operation for the two queries as shown below. To double check, apply two or three test cases and verify the results.
First perform a SELECT operation on the table without modifying the original query.

SELECT * FROM PRDNSCH.TABLE01 WHERE
( ( COL1 = 1 AND
COL2 > 2 ) OR
(   COL1 > 3 ) ) ;

Number of rows retrieved - 100 Rows

Perform a SELECT operation on the table by applying the new query that you just designed.

SELECT * FROM PRDNSCH.TABLE01 WHERE
COL1 = 1 AND
COL2 > 2

UNION

SELECT * FROM PRDNSCH.TABLE01 WHERE
COL1 > 3     ;

Number of rows retrieved for 1st half of the query - 23 Rows
Number of rows retrieved for 2nd half of the query - 77 Rows

Number of rows retrieved - 100 Rows

Amazing !

Granular Transcations - OLTP's

By the time you login to see what the transaction does - It's out of your sight !, may be that's why they named it as granular. Going technical - If you have a shop that is composed of CICS - DB2 hub, you as a DBA or developer should always ensure that these transactions execute for less than a minute ! OLTP's usually have a short interval zone - they creep in and creep out within 5 - 15 minutes of time frame differences.  In my experience with OLTP's ( On Line Transaction Processing ), I have seen few guys having a '' slight '' more run time duration than what it should be. So what's the big deal again ?

If your OLTP program is not well " tuned " then it can cause an overlap with the next program which is set within the interval zone. Believe it or not - After a long analysis I managed to find out in my account there is just a 3 second breather between transactions. A slight delay would trigger a chain of '' escalations " in the system and your subsystem master file will be loaded with them.

Usually OLTP's will not have a complex code or a logic in them, well most of the cases. But if you manage to see any complex SQL - try tuning it. It will be fun working with these little guys.

Here is my experience in tuning an OLTP -

The application developers used to intimate me that a particular OLTP which runs twice every hour, 24 x 7 is consuming lot of time and is causing delays especially during the month end processing ( when the load is high on the systems ). Here is how the transaction looked like

why would I bother on an application program consuming 85  seconds of application duration and 28 seconds of DB2 CPU ?  ? But wait - you sum the application and DB2 duration of the OLTP for a day and then for a month, the math would say - 1 hour of application time every day which would pile up to 30 hours every month with a DB2 duration of 704 mins/month . WOW Big Numbers !

I looked at the stats for the tables - It looked good. My aim now was looking for any access path issues. I did find one for the below query -

SELECT * FROM PRDNVW00
WHERE
COL_2 = :HV1 AND
COL_3 = 'Y'     AND
COL_4 LIKE 'SUCCESS%'

The access path for the above query was MATCHCOLS 0, ACCESSTYPE I , The index was defined on COL_1, COL_2 and COL_3 of the table. But the WHERE clause had COL_2 and COl_3. I tried my luck with the developers asking them to introduce COL_1 in the query and they managed to do that ( this is not easy, they had to check their business logic and impact ). Everything went as planned and the OLTP run time reduced drastically due to the query change -

SELECT * FROM PRDNVW00
WHERE
COL_1 = :HV0 AND
COL_2 = :HV1 AND
COL_3 = 'Y'     AND
COL_4 LIKE 'SUCCESS%'

Here is the run of the tuned OLTP program post the query change -

The OLTP now consumes 7 mins of application time every day which will pile up to 3.5 hours every month with a DB2 duration of 14 mins/month. HUGE SAVINGS and a great amount of breather time created between these transactions

Importance of PCTFREE & FREEPAGE

May be I should have done it earlier. There is a tablespace in production which is meant for " INSERT " operation, yes ! Only INSERT DML's run on this table. The application performs SELECT on this table, Ok - So what's the big deal ? , The tablespace was designed long back with DSSIZE as 2Gigs - fair ? I felt so, Average LRECL of 1,098 and with 3 million rows. Now, with such a configuration why should this tablespace have 2 extents ?? The size of the tablespace was beyond 3.5 GB ( 3.86 GB to be precise ). What causing this massive space utilization and of course high number of active pages

FREEPAGE and PCTFREE is defined for the table -

FREEPAGE 9
PCTFREE 30

The LDS of this tablspace with the above values -

The application run time on this table -

Is it really required to have PCTFREE as 30 ? Like i mentioned above, this table is a history table where records get inserted on a daily basis.

So here is what I did - I altered the tablespace and set the FREEPAGE value to 0 and PCTFREE to 5 ( I could have made it 0 as well ), I felt what if in case without my notice there is a update query that triggers on the table, what about REORG's ? 5 is a good value for PCTFREE if you know your table very well and administer what is happening on it. I then ran a REORG with share level CHANGE, performed a REBIND on the program

The LDS of this tablespace after alteration & REORG -

The REORG worked like a charm and there was a dip in the space utilization on the underlined dataset for this tablespace. The LDS now accounted for just 1.8 GB as compared to its earlier size of 3.8 GB, we have successfully optimized space - very fruitful indeed !

The application run time on this table post tuning activities -

There was a reduction in GETPAGES value, around 30,000 pages were saved from reading in. Efficiency gain achieved on the application was around 86 %.  After reading this you may want to go back to your tablespace and check with your application developers as to what these tables are and follow up on the PCTFREE and FREEPAGE values and save !

Note : You can try such options out with indexes as well. The above savings were influenced due to various other factors such as system availability, number of records, tablespace definitions, job start time, CPU utilization etc. Due to the drop in the application run time there was no wait for I/O on the systems that led to such drastic downsized figures. You may not get the exact numbers, but digging into what the table does and tweaking the parameters - does not stop you, at anytime !

Exploring the Multi-row FETCH Cursor

I have a program that has been in my performance tuning radar ever since we met. High Elapsed time and High wait I/O for 192K fetches. It had a very low DB2 CPU time of 97 seconds. I used to monitor this program and couldn't make out what is wrong. Where is this DB2 elapsed time is coming from ? Table sizes are not that huge and get pages are considerable for this application and buffer hits.



I felt, for those many fetches why do you need to OPEN and CLOSE cursor 79K times ?. Is there a way to reduce the repeated OPEN & CLOSE mechanism with cursors. I did some search of my own and came across the Multi-Row Fetch cursor. When I read about it - I wanted to try it out.



This is how the FETCH statement of normal cursor would look -

EXEC SQL
FETCH P16-CUR
INTO :HV-EMPNO
END-EXEC.

Designing a Multi-Row Fetch cursor for improving application performance -
First, Lets Design the working sotrage section handling for multi-row fetch cursors :

01 EMP-REC.
05 HV-EMPNO         PIC X(10) OCCURS 50 TIMES.
05 HV-EMPNAME   PIC X(20) OCCURS 50 TIMES.

We will have to change the declarations to accommodate the rowset size ( discussed below ). Usually the number in the OCCURS parameter should greater than or equal to the rowset size.

1) Declaring the Multi-row fetch cursor with ROWSET parameter :

EXEC SQL

DECLARE P16-CUR WITH ROWSET POSITIONING FOR

SELECT EMPNO, EMPNAME

FROM PRDNSCH.TABLE00

END-EXEC.

By using the ROWSET parameter the cursor is positioned on rowset of the result table.

2) Opening the row set cursor :

EXEC SQL

  OPEN P16-CUR

END-EXEC.

3) Fetching the data :

EXEC SQL

     FETCH NEXT ROWSET FROM P16-CUR

     FOR 50 ROWS

     INTO :HV-EMPNO, :HV-EMPNAME

END-EXEC.

4) Handling SQL +100 ( end of rowset ) :

If the P16-CUR is fetching 220 rows and you design the rowset size as 50 then

1st first will result in SQLCODE 0 and SQLERRD(3) will be 50

2nd fetch will result in SQLCODE 0 and SQLERRD(3) will be 50

3rd fetch will result in SQLCODE 0 and SQLERRD(3) will be 50

4th fetch will result in SQLCODE 0 and SQLERRD(3) will be 50

5th fetch will result in SQLCODE +100 and SQLERRD(3) will be 20

The 5th fetch has calculated that there are no more rows satisfying the rowset size designed and hence you need to ensure a mechanism to handle those remaining rows ( 20 ).

5) Closing the rowset cursor :

EXEC SQL

   CLOSE P16-CUR

END-EXEC.

I have recommended this option to my application developers who will be setting this up for the program. I will keep you posted on the savings if any with the help of this method.