Saturday, 14 September 2013

DB2 Embedded Select and Cursor Coding Guidelines.

"Prevention is always better than cure" so to ensure better performance of application program. A programer should consider all important points while designing application module by following subsequent guidelines :
  • Use Singleton Selects to Reduce Overhead. Whenever possible, try to use singleton SELECTs rather than cursor because the defination and processing of cursors add extra overhead to a DB2 application program. If singleton SELECT statement is returning more than one row then you need to implement cursor.
  • Use SQL to check data existence. Some it is required to check existance of data in table and does not actually need to retrieve data from table. The most efficient way it to use SQL correlated query against SYSIBM.SYSBUMMY1 table. For example 
                 SELECT 1 FROM SYSIBM.SYSDUMMY1
                    WHERE EXISTS (SELECT 1 FROM EMP WHERE
                                           firstnme = 'RUCHIN' AND A.IBMREQD = A.IBMREQD)
             above query will result '1' incase data exists in employee table and SQLCODE is zero.
  • Consider CURSOR free browsing. When program need to browse through the rows of column based on a single column where an unique index exists. SQL statement can be looped till no more rows exists in table.
  • Avoid using joins, union, distinct caluse, order by, group by, subquery etc in cursor for data modification.
  • Place the DECLARE CURSOR statement first, although declare cursor statement is not an executable statement and should not be coded in PROCEDURE DIVISION of application program.
  • Always include columns that need to be updated. Avoid using * in SQL statement.
  • Use FOR UPDATE OF When updating with a cursor although it is not mandatory. This technique effectively locks before it is delected so that no other process can access it.
  •  Use WHERE CURRENT OF to delete single row using cursor. Use the where current of the caluse on update and delete statement that are ment to modify only a single row. Failure to code the WHERE CURRENT OF clause result in the modification or deletion of all rows in the table being processed.
  • Avoid using FOR UPDATE OF clause for non updateable cursor. 
  • Initialized all host varaible and Open cursor before executing FETCH statement in application program.
  • Explicitly close cursor, moreover DB2 implicitly clause cursor when application program terminates.
  • Use WITH HOLD Clause to Retain Cursor Position. When a Commit is issue by application program, open cursor are closed unless the WITH HOLD option is coded for the cursor.

Created with Artisteer

1 comment:

  1. check the blog http://mframes.blogspot.in/ for more mainframe related stfz

    ReplyDelete