DB2 for z/OS: Application & Database Design


This course teaches the skills required by Applications Developers and Data Base Administrators to develop and design efficient DB2 applications and databases in all DB2 for z/OS environments, up to and including DB2 11.

This course is available 'on demand' (minimum 2 students) for public presentations or for one-company, on-site presentations.

Objectives

On successful completion of this course you 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.

Who Should Attend

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

Fee (per attendee)

P.O.A.

Course Code

DADD

Contents

Logical Database Design

Data concepts; E-R diagrams; Normalisation; Denormalisation.

Table Design

Data types, Null attributes; Column considerations; Constraint and domain management; Temporal tables; System period data versioning; Temporary tables; Views, synonyms and aliases.

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.

Physical Database Design

DB2 objects including storage groups, databases, tablespaces, etc.; Performance considerations; Accessing currently committed data.

DB2 Optimiser & EXPLAIN

Optimiser input; Catalog statistics; RUNSTATS; Using EXPLAIN; Optimisation hints.

SQL Coding Techniques

Single row vs cursors; OPTIMIZE FOR; Scrollable cursors; Subqueries; joins; Nested table expressions; Common table expressions; Unions vs case expressions.

Object Oriented SQL Extensions

Distinct types; Triggers; User-defined functions.

Locking & Concurrency

Lock control; locking options; lock compatibility; bind options; lock avoidance.

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.

Online Application Design

Hot spots; Concurrency; Deadlocks; Save points; Thread reuse.

Batch Application Design

Caching data; Commit frequency and performance; Commit and cursors; Utilities vs application programs.

Program Preparation & Execution

Precompiler; Bind options; Package options; Versions; Program execution.

Practicals

Normalisation & Table design; Table creation; Using EXPLAIN; Triggers.


© RSM Technology 2017