Target Audience

This course provides advanced DB2 for z/OS (Version 8) training, and covers many of the key tasks normally performed by a Database Administrator.

Pre-requisites

Familiarity with the z/OS, ISPF host environment is required. Prior exposure to DB2 or another relational database would be advantageous but not essential.

Course Objectives

The aim of this course is to provide the delegate with the basic skills required to function as a DBA.

On completion of this course the student will be able to:

•     define / maintain DB2 objects using DDL statements CREATE, ALTER and DROP
•     use data compression techniques within a tablespace
•     understand the application development process
•     bind plans and packages
•     resolve Referential Integrity violations
•     resolve Table Check Constraint violations
•     use DCL statements GRANT and REVOKE to control access to
•     understand and resolve locking issues
•     understand and use the LOAD Utility
•     understand use the UNLOAD Utility
•     LOAD and UNLOAD data in multiple formats
•     understand and use the CHECK DATA Utility
•     define and use Materialized Query Tables (MQTs)
•     understand Automatic Query Rewrite

Course Environment

Development will be performed using:

•     DB2 running on a z/OS platform

Course Details

DB2 OVERVIEW

The Relational Model

Data Representation

Normalisation

SQL

The DB2 Environment

DB2 Architecture

DB2 Datasets

The Structure of DB2 Objects

Structured Query Language (SQL)

Data Definition Language (DDL)

Data Control Language (DCL)

DB2 Catalog

DB2 Directory

The Optimiser

Overview of DB2 Locking

DB2 Logging and Recovery Overview

Image Copy/Logging/Recovery Cycle

PHYSICAL DATABASE REQUIREMENTS

Storage Groups

Databases

Tablespaces

Pages

Tablespace Organisation

Page and Row Organisation

The Simple Tablespace

The Segmented Tablespace

The Partitioned Tablespace

Large Tablespaces (EA Enabled)

Extended Addressability (EA) Enabled Tablespaces

Creating Large Tablespaces

Altering DSSIZE

Tablespace Thresholds

Create Tablespace Parameters:

Alter Tablespace

Tables

Copying Table Definitions

Rename Table

DB2 Column Types

Null Values

User Defined Default Values

Unique Table Rows

Rowid Data Type

Rowid Characterstics

Rowid Restrictions

Using a Rowid Column as the Partitioning Key

Identity Columns

Identity Columns - Definition

Identity Columns - Examples

Temporary Tables

Global Temporary Table

Declared Temporary Tables

Declared Temporary Table Considerations

Declared Temporary Tables - Comparisons

Synonym

Alias

Indexes

Index Organisation - The B Tree Index

Index Clustering

Defining an Index

A Partitioning Index

Create Index Paramaters

Indexes of Large / EA Enabled Tablespaces

Index Piecesize

Index Design Considerations

Alter Index

Views

Read Only Views

Views - With Check Option

Creating a View of Two Tables

ASCII Server Support

Space Search Algorithms

PARTITIONING DATA

The Partitioned Tablespace

Maximum Number of Partitions

Considerations when Partitioning

Create Tablespace - DSSIZE Parameter

Data Set Names

Table-Controlled Partitioning

Table-Controlled Partitioning Example

Altering a Table to Add Table Partitioning

Converting to Table-controlled Partitioning

Table-controlled Partitioning Catalog Changes

Table-controlled Partitioning Terminology

Index-controlled Partitioning Terminology

Index classification

Clustering

Clustering Within Partition

Changing the Clustering Sequence

Data Partitioned Secondary Indexes

Creating a DPSI

Design Considerations - Why Partition At All?

Design Considerations - Non-Partitioned Index Problems

Design Considerations - DPSI Benefits

Design Considerations - DPSI Problems

DPSIs and Utilities

DPSIs and Planning

Partition Management

Adding Partitions

Adding Partitions - Considerations

Index-controlled to Table-controlled partitioning

Rotating Partitions

Rotate Partition Syntax

Rotating Partitions - Considerations

Altering Partition Boundaries

Rebalancing Partitions using REORG

Rebalancing Partitions - Considerations

Considerations for User Applications

Display Database Command - Increased Partition Support

APPLICATION PROGRAMMING OVERVIEW

DB2 Environments

Important Note

Development Cycle With DB2

SQL Statement Format

SQL Statements used in Application Programs

Multi-row processing

Multi-row Fetch and Insert

New Syntax for Declare Cursor

Fetching Rowsets

Fetch Examples

Host Variable Arrays

SQL Include

SQL Communication Area

Table Declaration

Cursors

Declare Cursor

Open Cursor

Fetching Rows

Row Update

Row Deletion

Close Cursor

Scrollable Cursors

With Hold Option

The Optimize Statement

The Fetch First Statement

Scrollable Cursors

Dynamic Scrollable Cursors

Insensitive Cursor

Sensitive Static Cursor with Insensitive Fetch

Sensitive Static Cursor with Sensitive Fetch

Fetching from a Scrollable Cursor

Sensitive Fetches - Update and Delete Holes

Sensitive Fetches - Updated Data

Sensitive Static Cursor with Sensitive Fetch

Sensitive Dynamic and Asensitive Cursors

Sensitive Cursors - Fetching Rows

Scrollable Cursor Considerations

Table Names in Application Programs

Error Handling

SQL Error Codes

The SQLCA

Get Diagnostics

Diagnostic Information for Multi-Row Fetch

Diagnostic Information for Multi-Row Insert

PLANS AND PACKAGES

Bind

Rebind

Plans and Packages

Advantages of Using Packages

Binding Packages

Binding Plans

Defaults for Binding

Binding in Batch

Identifying a Collection Within a Program

Program Execution in Batch

Plan Names in Application Programs

IDENTITY COLUMNS AND SEQUENCES

Identity Columns

Identity Column Parameters

Altering Identity Columns

Identity Columns - Data Sharing Implications

Using Identity Columns with the Load Utility

Sequences

Create Sequence Syntax

Sequence Ordering

Altering Sequences

Dropping Sequences

Sequence Authorities

Using Sequences in Applications

Sequences - Considerations and Restrictions

Consumed Values / Gaps in a Sequence

Duplicate Sequence Values

Sequence Cycle Considerations

Defining a Constant Sequence

Cache Considerations

Sequences and Identity Columns Comparison

LOCKING

Implications of Concurrent Processing

IMS Resource Lock Manager (IRLM)

DB2 Locking Methods

The Lock Table Statement

Lock Modes

When Locks are Acquired

When Locks are Released

Isolation Levels

Controlling The Isolation Level At SQL Level

Keep Update Locks

Unit of Work in TSO

Commit and Rollback

Declaring Cursors With Hold

CICS Issues

IMS Issues

Savepoints

Savepoint Definition

Savepoints - Considerations and Restrictions

Transaction Deadlocks

Design Considerations

The Need for Application Restart

Transaction Deadlocks

Displaying Tablespace Locks

DB2 Locks On Objects Other Than User Data

Lock Avoidance - Latch Processing

Lock Avoidance Flow

Applications Affected

Currentdata (No) Implications

Controlling Concurrency For Utilities / Commands

The Claim Process

The Drain Process

The Drain / Claim Mechanism

Locking Design Considerations

REFERENTIAL INTEGRITY

What is Referential Integrity?

Implementation of Referential Integrity in DB2

Parent and Dependent Tables

The Primary Key

The Foreign Key

Referential Constraint Rules

Constraint Names

More Complex Referential Structures

DML Restrictions of RI

Check Pending Status

Definition of a Tablespaceset

DML Restrictions of RI

RI and The Load Utility

RI and The Check Utility

RI and The Report Tablespaceset Utility

Design Considerations

TABLE CHECK CONSTRAINTS

Overview of Check Constraints

Constraint Syntax

Allowable Constraints

When are Constraints Enforced

Current Rules

When is Check Pending Set

Catalog Changes

Authority Changes

MATERIALIZED QUERY TABLES

What Are Materialized Query Tables?

MQT Features

Creating an MQT

Altering an MQT

MQT Fullselect Features / Restrictions

Refresh Table

Populating User Maintained MQTs

Automatic Query Rewrite using MQT

Enabling Automatic Query Rewrite

Enabling Automatic Query Rewrite - DDL Options

Enabling Automatic Query Rewrite - Special Registers

AQR - Using Both Registers

Enabling Automatic Query Rewrite - System Properties

Enabling Automatic Query Rewrite - Query Properties

Determining if Query Rewrite Occurred

MQTs and Referential Integrity

MQTs and RI - Informational Constraints

DATABASE SECURITY

System Privileges

Database Privileges

Use Privileges

Table Privileges

Plan Privileges

Primary, Secondary and Current Authids

Implicit Privileges of Object Owners

Revoking Privileges

Version 8 Security Enhancements

Multilevel Security - Background

Defining RACF Security Labels

DB2 Multilevel Security - Seclabel Definition

Seclabel Behaviour

Seclabel Behaviour with Sql

Seclabel Behaviour with Utilities

DB2 Multilevel Security at Object Level

Encrypting Data

UTILITIES

Introduction

LOAD Utility

LOAD Utility Phases and Datasets

LOAD Partition Parallelism

Parallel Load Syntax

Online Load Resume

UNLOAD Utility

UNLOAD Syntax

UNLOAD Parameters

UNLOAD Options

UNLOAD Examples

Unloading from Copy Datasets

UNLOAD Restrictions

CHECK DATA Utility

CHECK Utility Phases and Datasets

Course Format

The course includes many practical sessions, designing, implementing, and tuning a Case Study system, using all DB2 utilities applicable to the role of the DBA. On completion of this course delegates will be ready to perform the daily tasks associated with a DBA role.. The comprehensive Student Guide supplied is fully indexed serving as a useful reference tool long after the course has finished. Delegates will also be able to access a free help-line with technical questions relating to topics covered on the course. An IBM SQL REFERENCE SUMMARY HANDBOOK is provided free to all attendees as part of the training course.