DB2: Row and Table Manipulation

Synopsis DB2: Row and Table Manipulation teaches how to embed SQL statements in a COBOL program to select rows and update tables. Audience This course is designed for COBOL programmers who want to use SQL in their programs to access data in DB2 tables. Time 3 - 6 hours Product Code: DB2113

After completing this course, the student should be able to:   Select a single row into a host structure Select multiple rows Updates tables -- directly and with a cursor

Selecting a Single Row Covers selection of a single row from a table.  All variations of the SELECT INTO statement are covered, with separate lessons devoted to NULL and VARCHAR columns.   Course Tables Data found in the tables used in the course.   SELECT INTO Purpose and format of the SELECT INTO statement.  Selecting into host variables and host structures.  Compatibility of DB2 columns and COBOL fields.   Variable Length Columns SYSIBM.SYSCOLUMNS to check column lengths.  Use of a length indicator for variable length columns.  49-level fields in a host structure.   Nulls DB2's indicator value for null columns.  Coding an indicator variable in the SELECT statement. Selecting Multiple Rows Shows how SQL, which can select many rows at once, and COBOL, which can process only one row at a time, are able to reconcile their differences.   Introduction SQL's active set and cursor to handle multiple rows that meet the selection criteria.   Selecting Data With a Cursor Purpose and format of the DECLARE CURSOR statement.  Use of WITH HOLD, ORDER BY and OPTIMIZE FOR as part of the DECLARE CURSOR statement.  Purpose and format of the OPEN CURSOR statement.   Fetching Rows Purpose and format of the FETCH statement.  Use of the host list for data from the cursor.  Closing and re-opening the cursor.   Workshop Coding SQL statements in a COBOL program to access DB2 data. Updating Tables Covers all aspects of updating tables from application programs.  Special attention is given to using the cursor to apply changes to many records one at a time.   Direct Updates Possible values for updates and inserts.  Updating or deleting a row without first reading it.  Updates and deletes affecting many rows.  Checking SQLERRD, SQLCODE or SQLSTATE after updates, deletes or inserts.   Indirect Updates Reasons to read a row before updating it.  Updates with a cursor.  Use of the FOR UPDATE OF clause.  Deleting the current row of the active set.   Nulls Updating a column with nulls via COBOL versus SQL.  Coding an indicator variable in the SET statement.   Variable Length Columns Providing a length value in an UPDATE or INSERT statement.  DB2's use of the length provided.