DB2 for z/OS Administration

The materials in the course reflect DB2 13, but can cover earlier releases on request. 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.

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

No prior experience with DB2 is required, although an understanding of data processing in general is useful. Familiarity with the z/OS operating systems and TSO/ISPF would also be of benefit.

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:

• understand data modelling techniques / normalisation
• mapping data models to physical Db2 objects
• define / maintain Db2 objects using DDL statements CREATE, ALTER and DROP
• understand pageset layouts and storage estimations
• 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 security / GRANT and REVOKE statements to control access
• 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 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)

Db2 OVERVIEW
The Relational Model
Data Representation
The Db2 Environment
Db2 Architecture
Db2 Datasets
Interfaces to DB2
SQL Structure
Embedded SQL
Access Path Selection
DB2 Table Structure
DB2 Data Types
DB2 Catalog
Db2 Directory
The Optimiser
DB2 Interactive
SPUFI
DB2 Logging And Recovery Overview
Backup And Recovery Overview
Image Copy/Logging/Recovery Cycle

Db2 INTERACTIVE (DB2I)
DB2I Options
SPUFI - SQL Processor Using File Input
Running Queries
SPUFI Defaults
SPUFI - Setting Autocommit to NO
DB2 Commands
DB2 Utilities
The DB2I Defaults Panel
Running SQL in Batch

DATABASE DEFINITION
The Structure Of Db2 Objects
Definition Of Db2 Objects - Ddl
Storage Groups
Databases

TABLESPACE DEFINITION
Tablespaces
Pages
Tablespace Organisation
Page And Row Organisation
The Simple Tablespace
The Segmented Tablespace
The Partitioned Tablespace
Maximum Number Of Partitions
Create Tablespace Parameter Reference
Universal Tablespaces
Universal Tablespace Benefits
Partition-By-Growth Tablespace
Creating A Partition-By-Growth Tablespace
Partition-By-Growth Tablespaces - Behaviour
Partition-By-Growth - Partition Allocation
Partition-By-Growth Tablespaces - Insert
Partition-By-Growth Tablespaces - Reorg
Partition-By-Growth Tablespaces - Other Utilities
Partition-By-Range Tablespace
Partition-By-Range Tablespaces - Considerations
Universal Tablespaces - Catalog Information
The Lob Tablespace
LOB Base Table Definition
LOB Tablespace Definition
LOB Auxiliary Table Definition
LOB Auxiliary Table - Index Definition

TABLE DEFINITION
Tables
Copying Table Definitions
Rename Table
Db2 Column Types
Design Tips for Columns
Implicitly Hidden Columns
Reordered Row Format
Moving to Reordered Row Format
Null Values
Nulls - Design Tips
Global Temporary Tables
Declared Temporary Tables
Declared Temporary Table Considerations
Declared Temporary Tables - Comparisons
Db2 Synonym
Db2 Alias
Views
Read Only Views
Views - With Check Option
Creating A View Of Two Tables
View Materialisation
View Design Considerations
Altering a Table
Altering Tables and Indexes
Data Type Changes
Alter Data Type - Performance Implications
Column Renaming
Column Renaming Restrictions
The Drop Statements
Create Table - Implicit Database Creation
Implicit Database Creation - Considerations
Create Table - Implicit Tablespace Creation
The Truncate Statement
Truncate Examples

CLONE TABLE DEFINITION
Clone Tables - Fast Data Replacement
Why Use Clone Tables?
Creating A Clone Table
Dropping A Clone Table
Clone Table Considerations
Creating a Clone - Catalog Activity
Creating a Clone - Index and Lob Catalog Activity
Clone Tables - The Exchange Command
Clone Tables - Exchange Considerations
Clone Tables - Locking Considerations
Clone Tables - DB2 Commands
Clone Tables - Authority

INDEX DEFINITION
Db2 Indexes
Index Organisation - The B Tree Index
Backwards Index Scan
Index Clustering
Changing the Clustering Sequence
Non-Unique Indexes
Defining An Index
Index Definition - Parameter Reference
Partitioning Indexes
Index Page Size
Index Page Size Specification
Index Compression
Index Compression Considerations
Index Compression - Estimation
Index Compression - Dsn1 Sample Output
Table vs. Index Compression Comparisons
Index On Expression
Index On Expression Considerations
Index Design Considerations
Altering a Column's Data Type - Impact Upon Indexes
Alter Data Type - Index Availability
Index Renaming

PARTITION MANAGEMENT
Increased Number Of Partitions
Maximum Number Of Partitions
Considerations when Partitioning
Data Set Names
Table-Controlled Partitioning
Table-Controlled Partitioning Example
Altering a Table to Add Table Partitioning
Converting from Index to Table Partitioning
Table-controlled Partitioning Catalog Changes
Index-controlled Partitioning Terminology
Table-controlled Partitioning Terminology
Index classification
Partitioned / Non-Partitioned Example
Partitioned / Partitioning Example
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
DPSI Benefits - Partition Pruning
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
Display Database Examples

BUFFERPOOL MANAGEMENT
Bufferpools
64-bit Architecture Support
DB2's Exploitation of 64-bit Architecture Support
Bufferpool Storage
The Edmpool
Alter Bufferpool
RID Pool Storage
Default Bufferpool Usage
Buffer Manager Enhancements

IDENTITY COLUMNS AND SEQUENCES
Identity Columns
Identity Columns - Examples
Altering Identity Columns
Altering Identity Columns - Parameters
Identity Columns - Retrieving the Generated Number
Identity Columns - Data Sharing Implications
Using Identity Columns with the Load Utility
Sequences
Create Sequence Syntax
Sequence Ordering
Altering Sequences
Dropping Sequences
Using Sequences in Applications
Sequences - Considerations and Restrictions
Sequence Application Examples
Sequences and Identity Columns Comparison

APPLICATION PROGRAMMING OVERVIEW
Db2 Environments
Static and Dynamic Programming
Static Development Cycle With Db2
Sql Statement Format - Cobol
Sql Statement Format - Pl/I
Table Declaration
Sql Statements Used In Application Programs
Sql Include
Sql Communication Area
Retrieving Data Into Host Variables
Ambiguous Host Variables
Sql Error Codes
The Sqlca - Sql Communications Area
Decoding The Sqlca In A Program
The Whenever Statement
Singleton Selects
Using A Cursor To Retrieve A Result Set
Declare Cursor
Open Cursor
Fetch A Row
Row Update
Row Deletion
Close Cursor
Handling Nulls
Retrieving System Registers
With Hold Option
The Optimize Statement
Fetch First 'n' Rows Only Clause
Fetch First vs Optimize For
Table Names In Application Programs
Batch Execution
Dynamic Sql
Dynamic Sql - Without Parameter Markers
Dynamic Sql - With Parameter Markers

PLANS AND PACKAGES
Db2 Bind
Rebind
Packages Overview
Explanation Of Packages
Advantages Of Using Packages
Binding Packages
Binding Plans
Defaults For Binding
Binding In Batch
Why Do I Get An Sqlcode -805 When Using Packages?
Identifying A Collection Within A Program
Program Execution In Batch
Plan Names In Application Programs

DYNAMIC DATABASE ACCESS OVERVIEW
Database Environments
Dynamic SQL - What is JDBC?
What are JDBC Drivers?
JDBC SQL Statements
Java Statement Example
Using Prepared Statements
The DB2 Universal Driver
Prepared Statement Cache

LOCKING
Implications Of Concurrent Processing
Ims Resource Lock Manager (Irlm)
Db2 Locking Methods
The Lockmax Parameter
The Lock Table Statement
Lock Modes
Table And Tablespace Lock Modes
When Locks Are Acquired
When Locks Are Released
Isolation Levels
Controlling The Isolation Level At Sql Level
Skip Locked Data
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
The Two Phase Commit Process
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
Lock Avoidance With Copy And Runstats Utilities
Controlling Concurrency For Utilities / Commands
The Claim Process
The Drain Process
The Drain / Claim Mechanism
Locking Design Considerations

TRIGGERS
Triggers
Trigger Parts
Before and After Triggers
Trigger Examples
Allowable Combinations
Error Handling
Trigger Cascading
Ordering of Multiple Triggers
Trigger Authorisation
Catalog Information for Triggers
Instead Of Triggers
Instead Of Triggers - Restrictions
Instead Of Triggers - Authorisation
Instead Of Triggers - Catalog Changes
Removing Triggers

REFERENTIAL INTEGRITY
What Is Referential Integrity?
Parent And Dependent Tables
The Primary Key
The Foreign Key
Referential Constraint Rules
Constraint Names
More Complex Referential Structures
Check Pending Status
Resetting Check Pending Status
Definition Of A Tablespaceset
Ri And The Load Utility
Ri And The Check Utility
Ri And The Report Tablespaceset Utility
Referential Integrity Access
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
Create MQT Example
Altering an MQT
Alter MQT Example
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 / Package Privileges
Primary, Secondary And Current Authids
Implicit Privileges Of Object Owners
Revoking Privileges
DB2 Multilevel Security - Seclabel Definition
Seclabel Behaviour
Seclabel Behaviour with Sql
The Existing 3 Tier Security Model
3 Tier Problems
Security Enhancement - Trusted Contexts / Roles
Trusted Contexts
Creating Trusted Contexts
Roles
Trusted Context / Role Examples

ACCESS PATHS
Introduction
Tablespace Scan
Non-Matching Index Scan
Matching Index Scan
Multiple Index Access
Index Only Access
Prefetch Processing
List Sequential Prefetch
SQL In List Processing - Dynamic Prefetch
Sequential Detection
Query Parallelism Review
Enabling Parallelism
Parallel Sorting
Parallelism Terminology
Table Join Methods
Merge Scan Join
Nested Loop Join
Hybrid Join
Star Join
Star Join Example
Referential Integrity Access
Rowid - Direct Row Access
Direct Row Access - Example
Direct Row Access - New Plan Table Column

TUNING
The Db2 Explain Function
Sql Explain Statement
Plan Table Layout
Plan Table Column Definitions
The Dsn_Statemnt_Table
Explaining the Statement Cache
Sql Tuning Considerations
Stospace
Deciding When To Reorganise
Deciding When To Rebind

DATA MOVEMENT AND HOUSEKEEPING UTILITIES
Introduction
Restarting Utilities
Check Utility
Check Utility Syntax
Check Utility Phases and Datasets
Datasets Used By Check Data
Exec Sql Utility
Exec Sql Utility and Cross Loader
Load Utility
Load Utility Syntax
Load Utility Phases and Datasets
Load Utility - Partition Parallelism
Load Utility - Online Load Resume
Load Utility - Online Load Considerations
Load Utility - Online Load Restrictions
Load Utility - Online Load Utility Phases
Load Utility - Delimited Load
Load Utility - Delimited Data Parameters
Load Utility - Unload / Load Examples
Modify Utility
Modify Utility Syntax
Modify Utility Phases and Datasets
Reorg Utility
Reorg Utility Syntax
Reorg - Partition Re-Balancing - Index Control
Reorg - Partition Re-Balancing - Reorg Pending (Reorp)
Reorg - Removing REORG Pending Status
Reorg Rebalance - Tablespace Control
Reorg - Unload External
Reorg - Discard
Reorg - Intelligent Control
Reorg - Inline Statistics
Reorg - Running On-Line
Reorg - The Fastswitch Parameter
Reorg - Shrlevel None Phases
Reorg - Shrlevel Reference Phases
Reorg - Shrlevel Change Phases
Reorg - Index Shrlevel Reference Phases
Reorg Index Shrlevel Change Phases
Reorg - Switch Phase for Fastswitch Yes
Reorg - Switch Phase for Fastswitch No
Reorg - Mapping Table
Reorg - Mapping Table Considerations
Reorg - The Alter Utility Command
Reorg - Inline Image Copies
Reorg - Display Utility Messages
Runstats Utility
Runstats - Catalog Statistics Updated
Runstats Parameters - Sample
Sample Considerations
Runstats - Historical Data
Runstats - Invalidating the Dynamic Statement Cache
Runstats - Deleting Historical Statistics
Runstats - Distribution Statistics
Runstats - Volatile Tables
Unload Utility
Unload Utility Syntax
Unload Parameters
Unload Options
Unload Examples
Unloading from Copy Datasets
Unload Restrictions
Unload Utility - Delimited Data Parameters
Dsn1 Service Aids

DATA RECOVERY UTILITIES
DB2 Utilities Introduction
DB2 Logging And Recovery Overview
Backup And Recovery Overview
Image Copy/Logging/Recovery Cycle
Syscopy Information
Copy Utility
Copy Utility Syntax
Copy Utility - Changelimit Paramer
Copy Utility - Changelimit Defaults
Copy Utility - Changelimit Examples
Copy Utility - Reportonly Parameter
Copy Utility - 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 Syntax
Mergecopy Utility Phases and Datasets
Modify Utility
Modify Utility Syntax
Modify Utility Phases and Datasets
Quiesce Utility
Quiesce Utility Phases and Datasets
Rebuild Index Utility
Rebuild Index Utility Phases and Datasets
Recover Utility
Recover Utility Syntax
Recover Utility Phases and Datasets
Recovery of a Concurrent Copy
Recovery to a Load / Reorg Inline Image Copy
Index Recovery Options
Repair Utility
Report Utility
Report Utility Syntax
Report Utility Phases and Datasets
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