OverviewThis intensive, five-day, hands-on course has been designed to provide a comprehensive understanding of the Db2 for z/OS Structured Query Language and how it is embedded in application programs. Throughout the course reference will be made to best practices to ensure that the SQL and application programs will run efficiently and effectively.<br>This course is suitable for software developers and programmers working in Db2 for z/OS environments.<br><br>This course is also available for one-company, on-site presentations and for live presentation over the Internet, via the Virtual Classroom Environment service.
PrerequisitesAttendees must be familiar with a major application programming language, e.g. COBOL, PL/I or Assembler. Previous exposure to the z/OS environment and the ability to use TSO/ISPF and JCL would also be a big advantage.
Delegates will learn how to
- 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.Relational Theory & Concepts
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.Db2 System Architecture
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.Introduction to Structured Query Language
Structured Query Language; Data Definition Language; Data Control Language; Data Manipulation Language: SELECT, INSERT, UPDATE, DELETE, MERGE; COMMIT / ROLLBACK; DB2 optimizer..Introduction to Db2 Interactive (Db2i)
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.Basic SELECT Statements
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.SQL Built-in Functions
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.Joins & Nested Table Expressions
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.Unions, Exceptions, Intersections & CASE Expressions
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.Subqueries
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.Table Modification Statements
.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.The Db2 Optimiser and Access Path Selection
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.Db2 EXPLAIN
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).Basic Db2 Application Programming
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.Cursor Processing
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.Program Preparation & Execution
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.Db2 Locking & Concurrency
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.