Sunday, March 27, 2016

FETCH FIRST N ROWS Vs OPTIMIZE FOR N ROWS in DB2

Very Often we use these two statements in our SQL query,ie, OPTIMIZE FOR N ROWS and FETCH FIRST N ROWS ONLY.
Although it may sound similar,but both of them have specific usage when it comes to the optimizer. This post we will try to see difference between the two.

As the name suggests FETCH FIRST 5 ROWS ONLY, it actually puts a limitation on the number of rows the query is returning.I am assuming here n=5. So, here user gets only 5 rows even though there can be 100 qualifying rows for that query.

OPTIMIZE FOR 5 ROWS will straight way influence the optimizer.It does not limit the number of rows SQL will return. So using this , we will get all the qualifying rows,may be incrementally.Intent here is to give priority to retrieve first few rows.Once optimizer understands this ,it will give preference to access plan that will minimize the response time.So,DB2 will send the rows in a single block. Consider the below scenario where this will come handy.

You have an online application where the screen can hold details of 5 customers at a time. Suppose you have the below query to fetch the customer details

Select Fname, Lname, Dept, Sal,Rollno
  From Dept table 
Order by Sal Desc;

We are assuming the index to be defined on Rollno. If a descending index also exists on SAL column, its likely to have low cluster ratio.
Without using the OPTIMIZE clause, DB2 will do a full table space scan and sort on sal column to fetch you the details.

Now on Adding  'ÓPTIMIZE FOR 5 ROWS' to the query, DB2 will use SAL index directly because it knows you need  the details of 5 highest paid employees.
So, it displays 5 rows to the screen and depending on user input will process the  next 5 or do something else.Here DB2 encourage matching index scan and would not prefer list or sequential prefetch. Access plan selected for 5 records may not be good for 100 rows.

Thus, this OPTIMIZE clause comes handy for queries where you can process incrementally.We can use it to get rows directly from table without going through any buffering operations like sorting. 

Saturday, March 19, 2016

DB2 unload utility using the program DSNTIAUL

IBM provides two methods of unloading data from a DB2 table. One is DSNTIAUL program that is run under IKJEFT01 or IKJEFT1A, or 1B and the other being  DSNUTILB program. DSNUTILB is a fairly new and is considered IBM’s Fast Unload Utility. Each has unique functionality that you may want to explore prior to choosing one. 

Outlined below are functions of DSNTIAUL process. 

1. DSNTIAUL Program: The DSNTIAUL program allows two type of unloads to be invoked against a DB2 table. When invoking DSNTIAUL you may specify an entire table unload or a selective SQL unload. The key DD statements that are being used are the SYSIN and the SYSTSIN. Both types of unloads are being run under IKJEFT1B.

Basic JCL example:
//UNLOAD EXEC PGM=IKJEFT1B, DYNAMNBR=20
//STEPLIB DD DSN=SYSDB2.DQA0.SDSNLOAD, DISP=SHR
// DD DSN=SUBSYS.DB2.DSNEXIT, DISP=SHR
//*************************************************************
//SYSPRINT DD SYSOUT=*
//SYSTSPRT DD SYSOUT=*
//SYSUDUMP DD SYSOUT=*
//SYSOUT DD SYSOUT=*
//SYSREC00 DD DSN=userid.tablerecords.dataset,
//          DISP=(,CATLG,DELETE),
//       UNIT=SYSDA,SPACE=(CYL,(300,300),RLSE)
//*
//SYSPUNCH DD DSN=user-id.tablelayout.dataset,
//     UNIT=SYSDA,SPACE=(CYL,(1,1),RLSE),DISP=(,CATLG,DELETE)
//SYSTSIN DD *
SYSTEM (SUBSYS) RUN PROGRAM (DSNTIAUL) PLAN (DSNTIAUL) -
LIBRARY (‘SUBSYS.DB2.RUNLIB.LOAD’)
END
//SYSIN DD *
DBCREATOR.TABLENAME 
/*
//*


For selective Unload, like when you are using a where clause or using select statements (like SELECT * from user_table;)  we need to use PARM('SQL') in SYSTSIN statement; else you may endup with SQLCODE -104.
Your SYSTSIN would look like

//SYSTSIN DD *
SYSTEM (SUBSYS) RUN PROGRAM (DSNTIAUL)
         PLAN (DSNTIAUL) -
LIBRARY (‘SUBSYS.DB2.RUNLIB.LOAD’) PARM('SQL')
END 
//SYSIN DD *
SELECT * FROM schema.Tablename ;