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 ?