Popular Courses

Browse Our Free Resources

  • whitepapers
  • whitepapers
  • webinars
  • blogs

Our Locations

Training Centres

Vancouver, BC
Calgary, AB
Edmonton, AB
Toronto, ON
Ottawa, ON
Montreal, QC
Hunt Valley

locations map


550 6th Av SW
Suite 475
Calgary, AB
T2P 0S2


821A Bloor Street West
Toronto, ON
M6G 1M1


409 Granville St
Suite 902
Vancouver, BC
V6C 1T2

U.S. Office

436 York Road
Suite 1
Jenkintown, PA

Other Locations

Dallas, TX
Miami, FL

Home > Training > DB2 > DB2 for z/OS: Application & Database Design Training

DB2 for z/OS: Application & Database Design Training

Course#: WA2159

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.


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.


IT developers and designers, DB2 database administrators and application programmers.


Attendees should have a basic knowledge of DB2 concepts (as provided by the RSM course DB2 for z/OS: Introduction, Features & Facilities).


4 days


Outline of WA2159 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
  • Using EXPLAIN
  • Optimisation hints.

5. SQL Coding Techniques

  • Single row vs cursors
  • 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
  • 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
We regularly offer classes in these and other cities. Atlanta, Austin, Baltimore, Calgary, Chicago, Cleveland, Dallas, Denver, Detroit, Houston, Jacksonville, Miami, Montreal, New York City, Orlando, Ottawa, Philadelphia, Phoenix, Pittsburgh, Seattle, Toronto, Vancouver, Washington DC.
*Your name:

*Your e-mail:


*Company name:

Additional notes:

We have received your message. A sales representative will contact you soon.

Thank you!.

more details
buy this course

Register for a courseware sample

It's simple, and free.


Thank You!

You will receive an email shortly containing a link to download the requested sample of the labs for this course.