Outline of WA2163 DB2 for z/OS Application Developers - Boot Camp Training
What is a database?; comparison of hierarchical, network & relational database management systems; a brief history of DB2.
Relational Theory & Concepts
Relational theory; relational model; relational algebra; entity, referential and user-defined integrity.
DB2 System & Operations
DB2 address spaces; DB2 attachments; storage pools; logging methodology; basic operations; DB2 commands.
DB2 Interactive (DB2I)
DB2I panels; SPUFI; other functions.
DB2 Data Objects
DB2 objects and the relationships between them; DB2 catalog & directory; interrogating the DB2 catalog.
DB2 Table Design
Data concepts; E-R diagrams; Normalisation; Table creation; DB2 data types and attributes; Cconstraint & domain management; Temporal tables; System period data versioning; Temporary tables.
DB2 Index Design & Usage
Data access paths; Predicates, index usage and I/O types; Index structure; When to index; Composite keys; Clustering; Index key randomisation; Indexes on expressions; Non-key columns in unique indexes; Creating indexes.
DB2 Physical Database Design & DDL
Pageset structure; Tablespace types and defaults; DB2 object definition; Understanding and choosing options for performance.
Basic Data Manipulation Language
Selecting all columns; row & column control; multiple conditions; special operators; the escape character; arithmetic in DML; using constants; special registers; concatenation; date and time columns.
SQL Built-in Functions
Column functions; scalar functions; 'group by' and 'having' clauses; User-defined functions overview; Inline and non-inline scalar functions.
Joins & Nested Table Expressions
Joins; correlation names; Cartesian product; inner and outer joins; nested table expressions.
Unions, Intersections,Exceptions & Case Expressions
Union, union all, and case expressions.
Simple subqueries; correlated subqueries; 'IN', 'NOT IN' 'NOT EXISTS'; quantified predicates, effect of nulls on SQL statements.
Table Modification Commands
Insert; inserting multiple rows; update and delete statements; the impact of referential integrity constraints
Basic Application Programming
Language support; embedded SQL; the declare table statement; the SQLCA; SQLCODE; host language variables; handling variable length columns & nulls; using host structures; commit and rollback.
Cursor processing, cursors for update, cursor with hold, scrollable cursors.
Precompile, bind, program preparation and execution; plans and packages; BIND PLAN; BIND PACKAGE; BIND QUERY.
DB2 Locking & Concurrency
Resource serialisation; Claims and drain; Concurrent applications and utility processing; Lock control; Locking options; Lock compatibility; Bind options; Lock avoidance; Monitoring locking.
Using Views & Temporary Tables
Views; Nested table expressions; Common table expressions; Recursive SQL; View options; Created temporary tables; Declared temporary tables; Table comparisons.
Materialized Query, Clone & Temporal Tables
Materialized query tables; maintaining data in MQTs; Using MQTs; Automatic Query Rewrite (AQR); Clone tables; Using clone tables; Exchanging data; System period temporal tables; Application period temporal tables; Using temporal tables; FROM period specification.
Distinct Data Types & User Defined Functions
Distinct data types; Casting data; Sourced user defined function; External user defined function; User defined SQL functions; Table functions; Inline and non-inline scalar functions; Invoking functions; Identifying functions; Function resolution.
Triggers; Trigger components; Trigger options; Trigger body statements; BEFORE, AFTER & IINSTEAD OF triggers; Trigger examples; Trigger performance.
Advanced DML options
SELECT FROM INSERT/UPDATE/DELETE; INCLUDE with INSERT & UPDATE; Multi-row processing with INSERT & MERGE; Multi-row condition handling; GET DIAGNOSTICS; Statement information; Condition information; Ranking data; ROW_NUMBER; RANK; DENSE_RANK; Moving sums; Moving averages: Identity columns; Sequences; Changing attributes; Using identity columns & sequences in SQL statements.
The DB2 Optimizer
Input to the Optimizer; Catalog statistics; Filter factors; Filter factor and clustering; Filter factor examples; Influencing the Optimizer; Influencing the Optimizer by manually adjusting statistics, modelling production values & using optimization hints'; Catalog statistics; RUNSTATS options; Statistics columns; RUNSTATS - examples.
EXPLAIN; PLAN_TABLE; PLAN_TABLE additions; DSN_STATEMNT_TABLE; DSN_FUNCTION_TABLE; EXPLAIN: basic access paths, multi-index access, nested queries examples.
DB2 Performance Overview
What is 'performance'?; Performance objectives; Workload categories; Service Level Agreements; Performance factors; System parameters; EDM pool size; Buffer pool sizes; Bufferpool thresholds; Bufferpool development; Database design factors; Application design factors; DB2 traces.
Trace types; Trace destination; Accounting trace classes; Audit trace classes; Statistics trace classes; Performance trace classes; Monitor trace classes; Global trace classes; IFCID types; Other trace options; Controlling traces.
Monitoring Application Performance
Monitoring overview; DB2 Performance Expert; DB2PE reports and traces; accounting short report; accounting long report; time distribution reports; times, suspensions & highlights report; SQL activity report; dynamic SQL caching report; other processing activity report; locking activity report; parallelism report; buffer pool and group buffer pool activity reports; statistics long report.
EXPLAIN information; DB2PM reports; short accounting reports; long accounting reports; report usage summary; a general approach to problem analysis in DB2.