AUDIENCE:   Application programmers and designers seeking more in-depth knowledge about IBM's DB2 database (versions 8 and 9) under z/OS and LUW platforms. PREREQUISITES:   The participant should have some knowledge of the relevant application language (eg. COBOL, Java, C++ etc...). Please inform Verhoef of the language and platform in use when booking the course. DURATION:   5 days. Hands on. OBJECTIVES:   This course focuses on using efficient SQL on all platforms and using appropriate host languages in the business applications environment. Through a combination of lectures and a progressive series of workshops, the audience will gain a solid grounding in SQL and how it is used interactively and embedded in host language programs. Many other DB2 issues are also covered, including how to address performance concerns, exploring the system catalog, batch and interactive issues, and DB2 product enhancements in versions 8 and 9. COURSE CONTENT:   Introduction to DB2 and the Relational Model Origin and history of the RDBMS and DB2 Advantages and disadvantages of the Relational Model DB2's basic logical and physical objects Subsystems/Instances Databases Tablespaces storage groups tables views MQT The SQL Language ANSI standard SQL data-types and relational extenders Principles of SQL data-retrieval Projection Selection Join Basic SQL data-retrieval Boolean Operators Operators ORDER BY GROUP BY HAVING UNION LIKE DISTINCT BETWEEN, etc. Case Expressions Using Case in the Select list, and in the WHERE clause Built-In Functions Aggregate functions Scalar functions User-defined functions Full sets Sub-Queries Standard and correlated nested sub-queries Joins Inner Joins Nested table expressions Common table expressions Resolving join performance issues Data modification INSERT, UPDATE, and DELETE statements Rules of referential integrity CTE's Introduction to SPUFI and/or QMF for z/OS or Control Center and/or Command Editor and/or Command Prompt for LUW Hands on progressive SQL workshops Embedded SQL Host language variables and the DCLGEN facility Structured Error-handling techniques SQLCA information, WHENEVER, and related issues Working with NULLS Cursor Processing statements DECLARE OPEN CLOSE FETCH ROWSETS Working with Updateable cursors Commit and Rollback processing 2-phase commit protocol Compiling embedded SQL programs Host language considerations The DB2 precompiler and BINDing Options Issues Locking protocols, isolation levels, etc. Stored Procedures When and how to utilise, in each applicable version The Development Center and cross-platform procedures Triggers Performance Issues Understanding the relationship between design and performance Normalization and de-normalization Row layout and free-space considerations Understanding the DB2 Optimizer Detailed examination of access paths in DB2 Query and CPU parallelism RUNSTATS/REORG Optimizer enhancements in the latest release Stage 1 vs. Stage 2 predicates Types of indexes Clustered Partitioned Type of Index Volatile Advantages and disadvantages of indexes Locking and contention issues Locking Strategy Handling deadlocks Update anomalies Lock escalation As Security Labels if applicable (z/OS only) Overview of DB2 internals DB2 internals from the standpoint of performance Examining Data Manage RDS Buffer Manager EXPLAIN/ VISUAL EXPLAIN Examining and interpreting EXPLAIN data New EXPLAIN columns in the latest release DSN_STATEMNT_TABLE (z/OS only) The DB2 System Catalog and History Tables Examining the key metadata affecting optimization decisions IC06/01