Wednesday, May 14, 2014

Static cursors and Dynamic cursors in DB2

Coming to the cursor types, before DB2 v7, we could only move/scroll through  the cursor in forward position.So when the cursor is opened, the pointer would be positioned before the first row in the result set. Once the FETCH as been issued, the pointer would move one row forward.
NO SCROLL is the default and indicates that the cursors are not scrollable.This is the normal cursor which commonly gets used in application program.

DB2 V7 introduced STATIC SCROLLABLE CURSOR  which can be scrolled backwards or
forwards, and to an absolute position or to a relative position.When the CURSOR is opened, the qualifying rows would be placed in a  declared temporary table automatically created by DB2.Scrolling is performed on the temporary table in both the forward direction and backward direction. DB2 deletes the result table when the cursor is closed.

With the introduction of DB2 v8, the concept of DYNAMIC SCROLLABLE CURSOR has been introduced. Dynamic cursors DO NOT need any temporary table.This is one of the main difference between static and dynamic cursors. They SCROLL on the main table itself, and therefore are sensitive to all the  committed inserts, updates, and deletes. Thus the overhead in building the temporary table(as in static cursor)  and maintaining the synchronization between the base table and result table is not there in case of dynamic cursor.

Declare cursor statements:
DECLARE CURSOR statements for static and dynamic cursors

NO SCROLL is the default and indicates that the cursors are not scrollable. If you specify
SCROLL, the cursors become scrollable.
For  Dynamic CURSORS we can have the below attributes:
1. SENSITIVE DYNAMIC SCROLLABLE
2. ASENSITIVE SCROLLABLE

 ******* NOTE: There is no DYNAMIC option for INSENSITIVE CURSOR. *********

Tuesday, May 6, 2014

Scrollable Cursor in DB2 : Sensitive cursor,insensitive cursor and asensitive cursor!!

Having completed the Basics of CURSOR in db2 ,we know there can be scrollable and non scrollable cursor. Further more There are two types of DB2 scrollable cursors .(a).Sensitive (b) Insensitive.
(We will difference between static and dynamic cursors in next post. Here we will see broadly what is sensitive/insensitive cursor)
To start with we will see first what is an insensitive cursor.Let us see how the  keyword 'INSENSITIVE' is used to declare this type of cursor. 
(We will see difference between STATIC and DYNAMIC cursor in next post)

DECLARE C1 INSENSITIVE SCROLL CURSOR FOR
 SELECT STAFF_ID,NAME  FROM EMP
....
OPEN C1
...
FETCH C1
..
CLOSE C1 
We will see how and where the INSENSITIVE feature is shown below.

Once we open the C1 cursor, a temporary table is created in the WORK File database  which holds the data from the base table ,ie, EMP table. (We must know that to use scrollable cursors we must use declared temporary  tables.DB2 places the rows returned from the cursor into these Temporary tables.)
TIMELINE T1: The Cursor is opened and result set is fetched. We will see that the  base table EMP and the result table(the result of cursor open statement) is same


STAGE1: The cursor is fetched and Main table and result table looks same
TIMELINE T2 (T2>T1) :  After some time T2,user deletes one row from the main table ie the EMP table.But since the cursor is INSENSITIVE, it does not sense the modification done to the base table, thereby shows the same rows even though main table has changed.

STAGE2: Row deleted from main table , yet the cursor result table shows the deleted row!!

Lets see now SENSITIVE CURSOR:
DECLARE C1 SENSITIVE STATIC SCROLL CURSOR FOR
 SELECT STAFF_ID,NAME  FROM EMP
....
Proceeding in the same way, let us review the Timelines T1 and T2 of the cursor life cycle.
TIMELINE T1: The Cursor is opened and result set is fetched. We will see that the  base table EMP and the result table(the result of cursor open statement) is same.  This is the same as shown above.
TIMELINE T2 (T2>T1) :  After some time T2,user deletes one row from the main table ie the EMP table.But since the cursor is SENSITIVE, the Temporary table will sense the modification done,ie the delete and will also delete the corresponding row in the Temporary table.
In Timeline T2, the cursor fetched reflects the same change as the base table EMP.
SENSITIVE CURSOR comes with the combination of  SENSITIVE STATIC and SENSITIVE DYNAMIC
Lets look at the FETCH statements for these scrollable type cursors.
All the possible FETCH options are shown here

To FETCH 5th Row from beginning of the Result set:
FETCH SENSITIVE ABSOLUTE +5 FROM C1
Fetch the FIRST ROW
FETCH SENSITIVE FIRST FROM C1
Move forward:
FETCH NEXT FROM C1
Move Backward:
FETCH PRIOR FROM C1 
Fetch 5th last row:
FETCH ABSOLUTE -5 FROM C1

RELATIVE n - will FETCH the row that is n rows away from the last row fetched
ABSOLUTE n - will FETCH the row that is n rows away from the first row in the results set

CURSORS in DB2 and how to use CURSOR in DB2?


A mechanism allowing an application program (cobol-db2 program for example) to retrieve a set of rows. Suppose our SQL query fetches many-rows result table. Using CURSOR our COBOL program can gain access to individual row of  the result table

There are two types of cursor:
1.Row-set positioned cursor : Here the result table are stored into Host variable arrays.
2.Row-positioned cursor : This type points to the current row/particular row in the result set.
It points to the next row to be passed to the application program.

The primary steps for using any  cursor are as follows:
Step1. Declare the cursor
Step2 Open the Cursor. This causes Db2 to build the result set.
Step3. Fetch each row ,one at a time, and pass it onto application program
Step4: Close the cursor.

DECLARE  the cursor:
EXEC SQL
 DECLARE EMP_CURSOR CURSOR FOR
 SELECT EMP_NAME,EMP_ID
 FROM EMP
 WHERE EMP_JOIN_DATE > '20000101'
END-EXEC.

DECLARE statement does not build result set.It simply forms the SQL statement we want to use to fetch the data. At DB2 precompile time, the definition of the CURSOR is stored in the bindfile, and the statement is commented.

OPEN  the cursor:
The open statement actually runs the SQL statement and builds the result set and makes it ready ready to be processed to be used to the application program in subsequent steps.
EXEC SQL
 OPEN EMP_CURSOR
END-EXEC.

Once the open is done, we need to fetch the data

FETCH the cursor:
EXEC SQL
 FETCH  EMP_CURSOR
 INTO :EMPNAME,:EMPID
END-EXEC.


FETCH is the only way to get the result into application program Host variable. We fetch each row into the Host variable and process the data. We can continue fetching of the data until we get a SQLCODE of +100  which implies END of the result table.
CLOSE the CURSOR
Once we are done with fetching of the records, we need to close the cursor.
EXEC SQL
 CLOSE EMP_CURSOR
END-EXEC.


How to UPDATE AND DELETE  records in cursor.
There can be instances where we need to modify the data in db2 table based on some values in a programmatic way. Had it been a spufi or QMF we have our update and delete statements ready to run and modify. But  we are in cobol program. Nevertheless we can do the same modifications using cursors again.!! We need to add special clause while declaring the cursor with  'FOR UPDATE OF'.
Even if we want to delete, we need to mention 'FOR UPDATE OF'  in declare statement  and we need to delete the entire row. Also we need to use WHERE CURRENT OF to update or delete the last row fetched.
Lets see the below example where we will update and delete some row in the cursor :

EXEC SQL
 DECLARE EMP_CURSOR CURSOR FOR
 SELECT EMP_NAME,EMP_ID
 FROM EMP
 WHERE EMP_JOIN_DATE > '20000101'

 FOR UPDATE OF NAME       <== DECLARE THE CURSOR WITH THIS CLAUSE
END-EXEC.

...............................
EXEC SQL
 UPDATE EMP
 SET EMP_NAME=:EMPNAME
 WHERE CURRENT OF EMP_CURSOR
END-EXEC

EXEC SQL
 DELETE  FROM EMP
 WHERE CURRENT OF EMP_CURSOR
END-EXEC.

****Important point Regarding the Usage of 'FOR UPDATE OF'  in CURSOR.****
If the select statement defining the cursor contains ORDER BY,GROUP BY, DISTINCT, SET OPERATORS ( UNION,EXCEPT, INTERSECT), JOIN, FOR FETCH ONLY/FOR READ ONLY, then the usage of 'FOR UPDATE OF' is NOT permitted. The above keywords make the cursor READ ONLY cursor.


Read about Cursor types: Sensitive and insensitive cursors

Saturday, May 3, 2014

SAS in Mainframes(z/Os) Tutorial : Use of symbolic parameter in SAS

Having completed SAS part 1and SAS part 2 , we will see the use of Symbolic in SAS
With the help of symbolic parameters, we can substitute the values dynamically in any place we want to. Say for example we are building the date, or reading the date from one input file and want to use it as a header in title or to use it in any calculation down the flow of the program while doing the condition checks like  <=,>= checks. In these cases the use of symbolic fits the best.
Say like below
DATA DATE;                     
  INFILE DATEIN;               
  INPUT @01  LOWDTE   $CHAR08.  
        @01  HEADER   $CHAR06.  
        @10  HIGHDTE   $CHAR08.  
        ;                      
  PUT "L-DATE >>>>>>>> " LOWDTE;
  PUT "H-DATE >>>>>>>> " HIGHDTE;
                               
   CALL SYMPUT('HEADR',HEADR); 

........
DATA PROCESS1;
 ...
TITLE ' test Heading' "&HEADR" ;
Here.. we are reading the input file, for the Dates and assigning the variables in INFILE DATEIN.
After that there may be several processes occurring in the program.Once done we now want to print the title taking the date read above in INFILE step.  The SYMPUT parameter is used for invoking symbolic parameter in sas. Later down the line we use  &variable-name as used in JCL for the value substitution.
Now ,lets see how we can use this value in sas comprison .
DATA DATA1;  
INFILE IMFL08;
RETAIN CUDATE;   <= Need to declare this variable as RETAIN
INPUT          

  @01   ANO $CHAR 01
  @02   BNO $CHAR02.
.........
CUDATE = "&RUNDATE"; <<== We are assigning the symbolics here
IF EFFDATE LE CUDATE <<= Use symbolic paramter in condition check
    THEN OUTPUT;