Objectives
On successful completion of this course attendees will be able to:
- normalise data and know when to denormalise
- understand how and when to implement constraints
- design and implement physical databases covering: storage groups, databases, table spaces, tables, indexes, aliases, synonyms, etc.
- design and implement efficient indexes
- design efficient SQL
- understand the differences in design for online and batch tasks
- assess security and related requirements
- be aware of the implication of locking, commit frequency, etc., on concurrency
- use EXPLAIN to evaluate design performance.
Audience
IT developers and designers, DB2 database administrators and application programmers.
Prerequisites
Attendees should have a basic knowledge of DB2 concepts (as provided by the RSM course DB2 for z/OS: Introduction, Features & Facilities).
Duration
4 days
Outline for DB2 for z/OS: Application & Database Design Training
1. Logical Database Design
- Data concepts
- E-R diagrams
- Normalisation
- Denormalisation.
2. Table Design
- Data types, Null attributes
- Column considerations
- Constraint and domain management
- Temporal tables
- System period data versioning
- Temporary tables
- Views, synonyms and aliases.
3. Index Design and Usage
- When to index
- Index structure
- Clustering; Composite keys
- Indexes on expressions
- Non-key columns in unique indexes
- Access paths
- Predicate evaluation
- Indexable and non-indexable predicates
- Stage 1 and Stage 2 predicates
- Predicate evaluation sequence.
3. Physical Database Design
- DB2 objects including storage groups, databases, tablespaces, etc.
- Performance considerations
- Accessing currently committed data.
4. DB2 Optimiser & EXPLAIN
- Optimiser input
- Catalog statistics
- RUNSTATS
- Using EXPLAIN
- Optimisation hints.
5. SQL Coding Techniques
- Single row vs cursors
- OPTIMIZE FOR
- Scrollable cursors
- Subqueries
- joins
- Nested table expressions
- Common table expressions
- Unions vs case expressions
4. Object Oriented SQL Extensions
- Distinct types
- Triggers
- User-defined functions
5. Locking & Concurrency
- Lock control
- locking options
- lock compatibility
- bind options
- lock avoidance
6. DB2 Security and DCL
- The grant and revoke statements
- Explicit and implicit privileges
- Administrative authorities
- Install SYSADM/SYSOPR
- Using RACF profiles
- Multilevel security
- Row & column level security
7. Online Application Design
- Hot spots
- Concurrency
- Deadlocks
- Save points
- Thread reuse
8. Batch Application Design
- Caching data
- Commit frequency and performance
- Commit and cursors
- Utilities vs application programs
9. Program Preparation & Execution
- Precompiler
- Bind options
- Package options
- Versions
- Program execution
10. Practicals
- Normalisation & Table design
- Table creation
- Using EXPLAIN
- Triggers