IntroductionCourse Outline

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

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