DB2 for z/OS Database Administrators - Boot Camp Training
DB2 for z/OS Database Administrators - Boot Camp Training
This comprehensive ten-day 'Boot Camp' course provides attendees with an accelerated learning approach to the DBA role in a DB2 for z/OS environment. The course is ideal for anyone new to the DBA role or those who coming to DB2 for z/OS from another database environment. The regular, hands-on lab exercises give students the opportunity to try out their newly-gained skills immediately. On successfully completing this boot camp, attendees will have reached the skill level needed to enable them to efficiently and effectively manage their DB2 for z/OS database environment.
On successful completion of this course attendees will be able to:
- describe the relational concepts and theory, and the advantages of a RDBMS
- list the components of the DB2 system, their function, and how they are controlled
- describe the internal structure and components of DB2 UDB for z/OS databases
- utilise all the functions available with DB2 Interactive (DB2I
- design and assess a logical database model
- design and implement efficient physical databases from the logical model using relevant options to create and amend storage groups, databases, table spaces, tables, indexes, aliases, synonyms, etc.
- describe how large object and XML data is held, and its implications on data recovery
- design and use materialized query tables, clone tables and temporal tables and the additional SQL statements associated with them
- explain the use of distinct data types and user-defined functions within DB2 for z/OS
- understand how and when triggers may be used and code them
- understand and control the locking strategy used by DB2 to maintain data integrity and concurrency
- assess security and related requirements
- describe the concept of online schema management and versioning
- use DB2 commands to monitor and control DB2 objects, processes and operations
- describe and use the full complement of DB2 online data and recovery utilities
- understand and use the DB2 stand-alone utilities
- explain the program preparation process for DB2 applications
- understand the DB2 optimizer and the use of EXPLAIN in determining access path, statement efficiency and choosing optimal database and code design
- plan for application monitoring and tuning
- use DB2 traces to record performance monitoring information to identify problem areas and identify tuning opportunities
- understand and interpret information in DB2PE accounting and statistics reports.
New z/OS Database Administrators and/or those coming new to DB2 for z/OS from another database environment.
The abitiy to use TSO/ISPF and a working knowledge of z/OS JCL.
Outline of 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.