What I'm Thinking

Use FOR READ/FETCH ONLY or WITH UR for retrieval only cursors. Use OPTIMIZE when you know the accurate number of rows that will be fetched Use ORDER BY only when sequence is important Use WITH HOLD statement to prevent COMMIT from destroying the cursor position in batch Programs. Select only those fields that you truly need Use only DCLGEN variables as predicates While declaring CURSOR in handler we should use OPTIMIZE FOR n ROWS, if we want pass only n rows from DB2 handler back to calling program. ... Read More

Common DB2 SQL Codes
21 March 2012

Successful Execution SQLCODE 000 SUCCESSFUL EXECUTION Warning SQLCODE +012 THE UNQUALIFIED COLUMN NAME column-name WAS INTERPRETED AS A CORRELATED REFERENCE +098 A DYNAMIC SQL STATEMENT ENDS WITH A SEMICOLON +100 ROW NOT FOUND FOR FETCH, UPDATE OR DELETE, OR THE RESULT OF A QUERY IS AN EMPTY TABLE +110 SQL UPDATE TO A DATA CAPTURE TABLE NOT SIGNALED TO ORIGINATING SUBSYSTEM +111 THE SUBPAGES OPTION IS NOT SUPPORTED FOR TYPE 2 INDEXES +117 THE NUMBER OF INSERT VALUES IS NOT THE SAME AS THE NUMBER OF OBJECT COLUMNS +162 TABLESPACE database-name. ... Read More
SYSCHECKDEP Column references for CHECK constraints SYSCHECKS CHECK constraint specifications SYSCHECKS2 (V7) Information about CHECK constraints on DB2 Catalog tables created for V7 or later SYSCOLAUTH The UPDATE privileges held by DB2 users on table or view columns SYSCOLDIST Cardinality, frequent value, and non-padded frequent value distribution statistics for the first key column of an index key SYSCOLDIST_HIST (V7) Column distribution statistics history SYSCOLSTATS The partition statistics for selected columns ... Read More

GDG - A Basic Introduction
9 February 2012

Introduction GDG stands for Generation Data Group. Here a group of files are related functionally or chronologically and can be accessed individually or as a group. These files share a unique name. Every dataset in a GDG has a Generation number and version number. Eg.TESTGDG.DATA.G0001.V00 Subsequent files are named by incrementing the Generation number(G0001-G9999). The current generation is referred a 0. The next generation as +1 an previous generation as -1 Every time a new gen is created, it becomes the current gen and the current gen becomes the previous generation. ... Read More