On successful completion of this course attendees will be able to:
- describe the internal structure and components of DB2 UDB for z/OS databases
- 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.
- understand and implement an effective locking strategy and use DB2 commands and other facilities to monitor locking for performance
- use EXPLAIN to evaluate the effectiveness of the database design.
Those responsible for the design and implementation of DB2 for z/OS databases.
Attendees should be familiar with the DB2 environment and have knowledge of SQL or have attended RSM's courses DB2 for z/OS: Introduction, Concepts & Facilities and DB2 for z/OS: SQL for Query Users.
Outline for DB2 for z/OS: Database Design & Implementation
1. The Relational Concepts Overview
- Data structure
- Data manipulation
- Data integrity.
2. Table Design
- Data concepts
- E-R diagrams
- Table creation
- DB2 data types and attributes
- Cconstraint & domain management
- Temporal tables
- System period data versioning
- Temporary tables.
3. Index Design
- Data access paths
- Predicates, index usage and I/O types
- Index structure
- When to index
- Composite keys
- Index key randomisation
- Indexes on expressions
- Non-key columns in unique indexes
- Creating indexes.
4. Physical Database Design & Data Definition Language
- Pageset structure
- Tablespace types and defaults
- DB2 object definition
- Understanding and choosing options for performance.
5. DB2 Locking
- Resource serialisation
- Claims and drain
- Concurrent applications and utility processing
- Lock control
- Locking options
- Lock compatibility
- Bind options
- Lock avoidance
- Monitoring locking.
6. Evaluating Database Design
- The DB2 Optimizer
- Input to the Optimizer
- Filter factors and column distribution
- EXPLAIN tables
- Using EXPLAIN to evaluate access paths
- Influencing the Optimizer
- Optimisation hints.