Skip to main content
COMPUTER  AND  SOFTWARE  EDUCATION   
HOME
VIEW FULL PUBLIC COURSE SCHEDULE
CONTACT US
COURSE
SEARCH
   Tel: 01275 859666
DB11 - DB2 Database Administration Workshop - Part 2
Duration3 Days (customisation)
Availability

Course Description

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.

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

Environment

Development will be performed using:

  • DB2 running on a z/OS platform

Customisation

For on-site courses (i.e. at your premises), we are more than happy to tailor the course agenda to suit your exact requirements. In many cases, we are able to build your in-house standards and naming conventions into the delivered course.

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.