Target Audience

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

Pre-requisites

Attendance of DB2 Database Administration Workshop - Part 1.

Course Objectives

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

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

•     understand the DB2 backup / recovery process
•     understand and use the COPY Utility to backup tablespaces / indexes
•     understand and use the RECOVER Utility to perform normal and PIT recovery
•     understand and use the COPYTOCOPY Utility
•     understand and use the MERGECOPY Utility
•     understand and use the QUIESCE Utility
•     understand and use the REBUILD INDEX Utility
•     understand and use the REORG Utility
•     understand and use the RUNSTATS Utility
•     write generic Utilities that use lists, wildcards and templates (LISTDEF and TEMPLATE)
•     understand and define Schemas
•     describe and define Stored Procedures and User Defined Functions
•     describe and define User Defined Types
•     understand and create Triggers
•     define Tables / Tablespaces that are required to store Large Objects

Course Environment

Development will be performed using:

•     DB2 running on a z/OS platform

Course Details

UTILITIES

Introduction

COPY Utility

COPY - The Changelimit Parameter

Changelimit Defaults

Changelimit Examples

The Reportonly Parameter

Using Conditional Copy with GDGs

COPY Utility Phases and Datasets

COPY Utility - Index Copies

COPY Utility - Index Restrictions

COPY Utility - Support for Object Lists

COPY Utility - Parallel Parameter

COPYTOCOPY Utility

Copytocopy Parameters

Copytocopy Examples

Copytocopy Considerations

MERGECOPY Utility

MERGECOPY Utility Phases and Datasets

MODIFY Utility

MODIFY Utility Phases and Datasets

QUIESCE Utility

QUIESCE Utility Phases and Datasets

RECOVER Utility

RECOVER Utility Phases and Datasets

Recovery of a Concurrent Copy

Recovery to a Load / REORG Inline Image Copy

Index Recovery Options

REBUILD Index

REBUILD Index Utility Phases and Datasets

REORG Utility

REORG - Partition Rebalancing

Partition Re-Balancing - REORG Pending (Reorp)

Removing REORG Pending Status

Partition Re-Balancing - RECOVERY Issues

REORG - Partition Rebalancing Considerations

REORG - Unload External

REORG - Discard

REORG - Intelligent Control

REORG - Inline Statistics

Running REORG On-Line

REORG Shrlevel None Phases

REORG Shrlevel Reference Phases

REORG Shrlevel Change Phases

REORG Index Shrlevel Reference - Phases

REORG Index Shrlevel Change - Phases

REORG Shadow Datasets

Reorg - Switch Phase for Fastswitch Yes

Reorg - Switch Phase for Fastswitch No

User Managed Shadow Datasets

REORG Mapping Table

Mapping Table Considerations

The Alter Utility REORG Command

REORG - Inline Image Copies

REORG - Display Utility Messages

REPAIR Utility

REPORT Utility

Report Utility Phases and Datasets

The RUNSTATS Utility

Catalog Statistics Updated by RUNSTATS

RUNSTATS Considerations

Runstats - Historical Data

Deleting Historical Statistics

The EXEC SQL Utility

Cross Loader

DSN1 Service Aids

UTILITY LISTS & DYNAMIC ALLOCATION

Dynamic Utility Jobs

Using LISTDEF / LIST for Dynamic List Processing

LISTDEF Syntax

LISTDEF Parameters

LISTDEF Specification

LISTDEF Examples

Recovery Related Lists

LISTDEF Expansion Steps

LISTDEF Considerations

How Many Times Does The Utility Execute?

LISTDEF Restartability

Using Template to Allocate Datasets

Template Syntax

Template Parameters

Substitution Variables for Dataset Names

Template Specification

Template Examples

Space Allocation with Templates

Disposition Allocation with Templates

Considerations for Dataset Allocation

Template and LISTDEF Combined

Storing LISTDEFS and Templates in Libraries

Additional Options

DB2I Support for LISTDEF and TEMPLATE

LARGE OBJECTS

Large Objects (LOBs)

LOB Definition and Manipulation

Base Table Definition

LOB Tablespace and Auxiliary Table Requirements

LOB Tablespace Definition

Auxiliary Table Definition

Auxiliary Table - Index Definition

Loading the LOB Data

LOB Data Options

LOB Data Manipulation

LOB Programming Issues

Declaring LOB Variables

Example using LOB Variables

LOB Manipulation

Data Spaces for LOB manipulation

Using LOB Locators

LOB Locator Considerations

Example using LOB Locators

LOBs and Utilities

LOB Limitations

SCHEMAS

The Grant Schema Statement

Schema Path - Bind Option

Current Path - Special Register

Overriding the Search Path

STORED PROCEDURES

Overview

Reduction in Network Traffic

Stored Procedure Advantages

DB2 Address Spaces

Defining an External Stored Procedure

Executing a Stored Procedure - the Call Statement

Error Handling within Stored Procedures

Execution Flow

STORED PROCEDURE DEFINITION

The Create Procedure Statement

Stored Procedure Parameters

Allowable SQL Statements

Create Procedure Example

The Alter Procedure Statement

Deleting a Stored Procedure Definition

WORKLOAD MANAGER

Overview

DB2 SPAS

WLM Address Spaces

Setting Up The WLM Environment

Comparison Of WLM vs SPAS Procedure Management

Summary of WLM Advantages

Access To Non-SQL Resources

Resource Recovery Services Attach Facility (RRSAF)

The Display Procedure Command

Starting and Stopping Procedures

WLM Operational Commands

Using Explain for Function Resolution

STORED PROCEDURE AUTHORITIES

Stored Procedure Authorities

Stored Procedure Authorisation

Authorisation Checking when Calling a Procedure

DISTINCT TYPES

Distinct Types (User-defined Data Types)

Create Distinct Type Statement

UDT Allowable Operations

Generated Cast Functions

Using Cast Functions

Defining a Sourced Function with Distinct Types

Defining a Sourced Function for an Operation

CAST Specifications

Cast Specifications in Application Programs

Distinct Type Privileges

Catalog Information

Dropping Distinct Types

USER DEFINED FUNCTIONS

User-Defined Functions

Built-in Functions

Creating External Functions

Sourced Functions

Sourced Function Examples

External Scalar Functions

External Scalar Function Examples

External Table Functions

Table Function Example

User-Defined Function Parameters Summary

Function Authorisation

Function Execution Environment

Dropping a Function

Using Explain for Function Resolution

The Stop Function Command

The Start Function Command

The Display Function Command

Changes to Catalog Tables

Supplied Functions

New Column Functions

New Scalar Functions

TRIGGERS

Trigger Parts

The Create Trigger Statement

Before and After Triggers

Invoking Stored Procedures and User-Defined Functions

Using Transition Tables

Allowable Combinations

Error Handling

Trigger Cascading

Ordering of Multiple Triggers

Triggers and Referential Integrity

Trigger Authorisation

Trigger Packages

Catalog Information for Triggers

Removing Triggers

Performance Considerations

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.