Outline of WA2162 DB2 for z/OS Database Administrators - 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.
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 & INSTEAD OF triggers; Trigger examples; Trigger performance.
Online Schema Management & Versioning
Enhanced ALTER statement; altering data types; altering index keys; altering tablespace types and attributes; restrictions on altering objects; impact of altering objects; pending definition changes; versioning; reclaiming versions.
Index defined vs table defined partitioning; adding new partitions; rotating partitions; changing partition boundaries; rebalancing partitions.
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.
DB2 Security & DCL
The grant and revoke statements; Explicit and implicit privileges; Object privileges; Administrative privileges; Install SYSADM/SYSOPR; Using RACF profiles; Multilevel security; row & column level security.
Pageset structure; Tablespace types and defaults; Index structure; Data compression.
DB2 Utilities Overview
Online utilities; stand-alone utilities; control statements; DB2I Utilities option; using LISTDEF; using TEMPLATE; OPTIONS statement.
Online Data Utilities
RUNSTATS; LOAD; UNLOAD; CHECK DATA; CHECK INDEX; CHECK LOB; REORG; STOSPACE; REPAIR.
Online Recovery Utilities
Recovery components; COPY; COPYTOCOPY; MERGECOPY; REPORT; QUIESCE; RECOVER; REBUILD; MODIFY; BACKUP SYSTEM; RESTORE SYSTEM.
DSN1COPY; DSN1PRNT, DSN1LOGP; DSN1COMP; DSN1CHKR; DSNJU004; DSNJU003; DSNJLOGF.
Catalog & Directory Recovery
Catalog / Directory recovery implications; Recovery sequence; Catalog & Directory point-in-time recovery; Recovery implications - DEFINE option; Recovery implications - FASTSWITCH; Recovery implications - identity columns & sequences; System-wide points of consistency.
Program Preparation & Execution
Precompile, bind, program preparation and execution; plans and packages; BIND PLAN; BIND PACKAGE; BIND QUERY; invoking TSO attachment; CICS attachment; Resource Control Table; DB2CONN using CEDA; DB2ENTRY using CEDA; DB2TRAN using CEDA; controlling CICS attachment; IMS attachment; IMS Subsystem Member (SSM); Subsystem Member relationships; Resource Translation Table (RTT).
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.
Database Design Factors Review
Normalisation forms; De-normalisation considerations; Table creation; Null attributes; Variable length columns; Column considerations; Constraint and domain management; Tablespace considerations; Space allocation and free space; Work file considerations; Data set placement; Index structure; Creating indexes; When and when not to index; Indexes and clustering; non-key columns in unique indexes; Composite keys; Reorganising indexes.
Application Design Factors Review
Predicates; Access paths; Indexable and non-indexable predicates; SQL statement processing; Stage 1 and stage 2 predicates; Summary of predicate processing; Predicate evaluation sequence; Sequential prefetch; List prefetch; Index lookaside; Joins; Join transformation; Join method; Subqueries; Non-correlated vs. correlated subqueries; UNION; Case expressions; Union and case performance; Table expressions; Temporary tables; Table comparisons.
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 response times; Suspensions; The accounting reports; Elapsed and class 2 time distribution; Highlights of application performance; Class 1, class 2, and class 5 times; Wait times (class 3 suspensions); Package times and suspensions (class 7 and class 8); Application termination data; Locking activity; Buffer pool activity; Group buffer pool activity; SQL activity; RID list and ROWID processing; Stored procedures, UDFs and triggers; Dynamic SQL and LOB storage; Query parallelism; DDF activity; Data sharing locking.
EXPLAIN information; DB2PM reports; short accounting reports; long accounting reports; report usage summary; a general approach to problem analysis in DB2.