Db2 for z/OS: Application Programming Workshop

• use SPUFI to code SQL statements
• code SQL SELECT statements including those using techniques such as joins, nested table expressions, subqueries, unions and case expressions
• code SQL INSERT, UPDATE, DELETE and MERGE statements to modify data
• identify access paths chosen by Db2 by using the EXPLAIN feature
• embed SQL statements into an application programming language such as COBOL, PL/I or Assembler
• understand and control the locking strategy used by Db2 to maintain data integrity
• prepare and execute Db2 applications and understand the use of precompile and bind steps in the preparation process.

Database Management Systems; Hierarchical database structure; Network database structure; Relational database structure; Database comparison; Table structure; A brief history of Db2.

Dr. E F Codd's 12 rules; The relational model; Structure - tables; Structure - rules for columns and rows; Structure: candidate keys, primary and alternate key, foreign keys; Manipulation: relational algebra, projection, restriction, join, union, intersection, difference, product; Integrity - domain and user-defined integrity; Integrity - entity and referential integrity; Referential integrity - terminology; Tablespace set; Referential integrity - defining constraint; Constraint definition restrictions; Data manipulation restrictions; Catalog entries; Referential Integrity and INSERT; Referential Integrity and UPDATE; Referential Integrity and DELETE; Referential Integrity review.

Architecture overview; Working Storage Areas; Buffer, Sort, RID & EDM pools; Buffer Pools - 64 bit addressing; EDM pool; Sort pool; RID pool; Db2 Attachments; System datasets & databases; DB2 objects; Object characteristics; SQL overview; Db2 Interactive (Db2I); Basic Db2 operations; Db2 commands; Db2I commands panel.

Structured Query Language; Data Definition Language; Data Control Language; Data Manipulation Language: SELECT, INSERT, UPDATE, DELETE, MERGE; COMMIT / ROLLBACK; DB2 optimizer..

Db2I primary option menu; Db2i option D - DB2i defaults; Db2i option 1 - SPUFI; Current SPUFI defaults; SPUFI SQL statement creation; Browsing SPUFI output; SPUFI commit or rollback panel; SPUFI AUTOCOMMIT options; Db2i option 2 - DCLGEN (Declarations Generator); DCLGEN output; Db2i option 3 - Program Preparation; Db2i option 4 - Precompile; Db2i option 5 - Bind / Rebind / Free; Db2i option 6 - Run; Db2i option 7 - DB2 commands; Db2i option 8 - Db2 utilities; Query Management Facility; Reporting options - SPUFI versus QMF 154; SPUFI report; QMF report.

Supplied database structure; Supplied table formats; Specifying table names; Using view, alias or synonym; Specifying column names; Selecting all columns; |Column sequence; Naming columns; Eliminating duplicate rows; Row sequence; Row restriction; Comparing columns from the same row; Multiple conditions; The BETWEEN operator; The IN operator; The LIKE operator; The SQL ESCAPE character; Searching for apostrophes; Arithmetic in SELECT statements; Arithmetic in the WHERE clause; Naming derived columns; Using literals in the SELECT list; Special registers; The concatenation operator; Date and time columns.

Column functions; GROUP BY; HAVING; Rules for GROUP BY / HAVING; Scalar functions; Data conversion functions; Date manipulation functions; Numeric manipulation functions; String manipulation functions; Handling null values; User defined functions.

Inner JOIN; Correlation names; A JOIN of three tables; Joining a table to itself; A three-way join of two tables; Cartesian product; Outer join; Full outer join; Full outer join using COALESCE; Left / right outer joins; Nested table expressions - example.

UNION, INTERSECT and EXCEPT; UNION and UNION ALL; UNION with named derived result columns; INTERSECT and EXCEPT; CASE expressions; CASE expression formats; CASE expressions in functions; CASE expressions in the WHERE clause.

Simple (non-correlated) subquery; Simple subquery with multiple rows; Subquery with multiple columns; Quantified predicates; Correlated subquery; Tests for existence with correlated subqueries; Performance considerations; Null values and SQL.

.The INSERT statement; Inserting all columns, single row; Inserting a subset of columns, single row; Inserting multiple rows using SELECT; Inserting multiple rows using row set; Inserting using expressions; The UPDATE statement; Updating rows; Updating & case expressions; Updating and subselects; The DELETE statement; Deleting rows; The MERGE statement; Merging rows; Referential Integrity & INSERT; Referential Integrity & UPDATE; Referential Integrity & DELETE.

Db2 Optimiser; Access path selection; Access paths - index usage; Access paths - direct row access; Access paths - matching index access; Access paths - non-matching index access; Access paths - table(space) scan; Input to the optimiser; Filter factors; Filter factor and clustering; Filter factor and column cardinality; Filter factor and column correlation; Filter factor and column distribution; Influencing the optimiser; Influencing the optimiser - manually adjusting statistics; Influencing the optimiser - using optimisation hints; Influencing the optimiser - modelling production values; Catalog statistics; Updating catalog statistics using RUNSTATS; Statistics columns; RUNSTATS examples.

XPLAIN; EXPLAIN table enhancements; EXPLAIN syntax enhancements; PLAN_TABLE (DB2 version 1); PLAN_TABLE (Versions 2 to 6); PLAN_TABLE (Db2 versions 7 to 11); DSN_STATEMNT_TABLE; DSN_FUNCTION_TABLE; EXPLAIN example 1 (basic access paths); EXPLAIN example 2 (multi-index access); EXPLAIN example 3 (nested queries).

Embedded SQL; The DECLARE TABLE statement; Host language variables; Host language variable data types; Variable length columns; Variable length columns and LIKE; Handling null values; Setting null values; Extended indicator variables; Using host language structures; Nulls and host language structures; Indicator variables and insertions; COMMIT and ROLLBACK; Explicit COMMIT and ROLLBACK; SAVEPOINTS; Error handling; SQLCODE & SQLWARNn flags; Other SQLCA fields; The Declarations Generator (DCLGEN); DCLGEN output - DECLARE TABLE; DCLGEN output - Host language structure; Including DCLGEN output.

Multi-row processing; Cursor processing; Using a cursor to update data; Cursor WITH HOLD; Ambiguous cursors; Read-only cursors; Scrollable cursors; Insensitive scrollable cursors; Sensitive static scrollable cursors; Sensitive dynamic scrollable cursors; Controlling fetch sensitivity; Controlling cursor scrolling; Scrollable cursor review.

Processing SQL statements; Program preparation overview; Db2 precompile; Db2 BIND; BIND alternatives - plan only; BIND alternatives - packages; Packages & collections; BIND parameters; Plan management & access path stability; Plan management (RE)BIND parameters; Packages - varying BIND options; Packages - using mirrored tables; Packages - using versions; Program execution - TSO; Program execution - IMS; Program execution - CICS.

Controlling concurrent access; Claims and drains; Claim classes; Drains; Utility restrictive states; Transaction locking; Reasons for locking - preventing lost updates; Reasons for locking - preventing reads of uncommitted data; Reasons for locking - allowing repeatable reads of data; Lock control; Lock options - installation parameters; Lock options - tablespace creation; Lock options - SQL statements; Locking hierarchy; Lock compatibility - row and page locks; Lock compatibility - table and tablespace locks; Lock options - BIND parameters; BIND - ACQUIRE and RELEASE parameters; BIND - ISOLATION; BIND - CURRENTDATA; Lock avoidance; Lock avoidance example; Locking and concurrency recommendations; Monitoring locking - DB2 commands; Monitoring locking - LOCKINFO; Monitoring locking - DB2PM / DB2PE reports.