DB2 v9 for z/OS Administration

Course Overview

The materials in the course reflect DB2 UDB for z/OS Version 8 and DB2 9. This course provides attendees with a comprehensive introduction to designing and administering DB2 databases. The course covers the complete spectrum from initial data model, through logical and physical database design, data security, and ongoing administration to ensure that the data remains complete, consistent, and in its most efficient state.

Audience

This course will be of interest to anyone needing an understanding of the design, implementation, support and maintenance of DB2 databases up to DB2 9.

Skills Gained

• Data modelling techniques.
• The process of normalisation of data.
• Mapping data models to DB2 objects.
• Choosing the correct data types, and physical design characteristics.
• Data integrity issues.
• Page layouts and storage estimations.
• Indexing, locking strategy and other performance implications.
• Use of utilities and service aids in ongoing maintenance.
• Security and auditing considerations.

Prerequisites

A fundamental understanding of the DB2 relational database management system is assumed, as is an understanding of data processing in general. Familiarity with the z/OS operating systems and TSO/ISPF would also be of benefit.

Course Outline

Data Modelling

• Entities and attributes; entity-relationship diagrams; normalization.

Relational Table Design

• DB2 data types; null values; generated values; key columns; referential integrity; other constraints; table exit routines; performance considerations; creating tables.

Physical Database Design

• Catalog and directory databases; other system databases; DB2 data objects; creating
• and modifying DB2 objects; deleting DB2 objects.

Physical Data Storage

• VSAM dataset requirements; naming convention; page set structure; large object considerations; storage calculation and estimation.

Indexes & Access Paths

• Index structure; index keys and pointers; choosing indexes; index creation; access path determination; filter factors; using EXPLAIN; optimization hints.

Data Integrity & Locking

• Entity integrity; referential integrity; reasons for locking; lock types; lock compatibility; lock size; lock duration; lock avoidance.

Security and Authorisation

• Security overview; security terminology; implicit, explicit and grouped privileges; maintaining security using data control language; ownership considerations.

Introduction to Utilities

• Utility packaging; on-line utilities; stand-alone utilities; LISTDEF and TEMPLATE; invoking and controlling utilities.

Data Integrity Utilities

• CHECK DATA; CHECK INDEX; CHECK LOB; REPAIR.

Back-up and Recovery Utilities

• COPY; COPYTOCOPY; MERGECOPY; QUIESCE; RECOVER; REBUILD; REPORT; MODIFY; BACKUP SYSTEM; RESTORE SYSTEM.

Data Utilities

• LOAD; REORG; RUNSTATS; STOSPACE; UNLOAD.

DB2 Stand-alone Utilities

• DSN1COPY; DSN1PRNT; DSNJU003; DSNJU004; DSN1LOGP; DSN1CHKR, DSN1COMP; DSN1LOGF; DSNJCNVB.

Program Preparation

• Precompile; bind; bind authorizations; collections, packages and plans.