DB24 - DB2 Version 9 Transition - 2 Days

View Dates and Locations

Course Description

This course is aimed at Systems Programmers, Programmers and DBA's who need to understand the new features and facilities of DB2 Version 9. This lecture based course covers all of the new features in detail.


A strong working knowledge of DB2 Version 8 is required to attend this course.


The lectures apply to DB2 running in a z/OS environment.


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.

On-site course enquiry (from £165 pp per day)

Course Details

View Dates and Locations

Version 8 Recap - Types Of Tablespace
Version 8 Recap - Tablespace Definition
Version 9 - Universal Tablespaces
Universal Tablespace Benefits
Maximum Number Of Partitions
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 Changes
Implicit Object Creation
Automatic Database Creation
Implicit Database Creation - Considerations
Automatic Tablespace Creation
Default Bufferpool Usage
Other Implicit System Required Objects
SMS Storage Classes with Db2 Defined Datasets
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
Clone Tables - Utilities
Column Renaming
Column Renaming Restrictions
Alter Long Varchar to Varchar
Refresh EARLY Code Without an IPL
Conditional Restart Log Truncation by Timestamp
Improved Insert Performance - The Append Parameter
Index Renaming
Catmaint Enhancement - Update Schema
Update Schema Restrictions

Trigger Recap
Trigger Recap - Before and After Triggers
Instead Of Triggers
Instead Of Triggers - Restrictions
Instead Of Triggers - Authorisation
Instead Of Triggers - Catalog Changes
The Merge Statements
Merge Statement Restrictions
Merge Statement Example
Get Diagnostics Recap
Get Diagnostics - Statement Information
Get Diagnostics - Condition Information
Get Diagnostics - Merge Example
Explaining Merge Statements
Merge Statement Authorisation
Select From Merge / Update / Delete Statements
Select from Insert - Version 8 Recap
Select from Merge
Select from Update
Select from Delete
Order By and Fetch First in Subselect
The Order By Order Of Clause
The Truncate Statement
Truncate Examples
Using Truncate With Multi-Level Security
Truncate Restrictions
Union, Intersect and Except
Version 8 Recap - Union
Intersect and Except
Intersect and Except Examples
Set Current Schema Enhancement
Cultural Sort and Case Insensitive Comparison
Collation Key Examples
Implicitly Hidden Columns
Bigint - Data Type
Bigint - Scalar Function
Binary / Varbinary - Data Types
Binary - Data Type and Function
Decfloat - Data Type
Decfloat - Scalar Function
New Scalar Functions
New Scalar Functions

System Z and zIIP Exploitation
Virtual Storage Management
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
Resource Limit Facility Enhancement
Buffer Manager Enhancements
Logging Recap
Not Logged Tablespaces
Switching On and Off Not Logged
Logging Considerations
Not Logged with XML Tablespaces
Not Logged with LOB Tablespaces
Recovering Not Logged Tablespaces
Load / Reorg with Not Logged Tablespaces
Rebuild Index with Not Logged Tablespaces
Check Data with Not Logged Tablespaces
Queisce with Not Logged Tablespaces
Internet Protocol Version 6 Support

Run Time Reoptimization Enhancement
Locking Recap - Page / Row Lock Modes
Locking Recap - Isolation Levels
Locking Recap - Sensitive Static Cursors
Locking Recap - Sensitive Cursor Update / Delete Holes
Locking Recap - Sensitive Cursor Positioned Updates
Optimistic Locking
Optimistic Locking Example
Row Change Timestamp Selection
Row Change Timestamp Insertion
Skip Locked Data
Index On Expression
Index On Expression Considerations
Relief for Sequential Key Insert
Randomized Index Ordering
Reordered Row Format
Moving to Reordered Row Format
Histogram Statistics
Page-Range Screening Optimization
Global Query Optimization

Stored Procedures Recap
An SQL PL Procedure Example
Procedure Preparation
Native (Internal) SQL Procedures
How Native SQL Procedures Work
Creating Native and External SQL Procedures
Creating Native SQL Procedures
Altering Native SQL Procedures
Native SQL Procedures - Versioning
Native SQL Procedures - Binding Across Servers
Native SQL Procedures - Command Enhancements
Other Sql-Pl Enhancements
Sql Pl - Get Diagnostics Enhancement
Sql Pl - FOR Statement
Sql Pl - Nested Compound Statements
SQL Procedure Example - Nested Compound Statement
Sql Pl - New Special Registers
Debugging Native Stored Procedures
LOB Data Manipulation - Recap
LOB Definition And Manipulation
Base Table Definition
LOB Tablespace And Auxiliary Table Requirements
LOB Tablespace / Table Definition
LOB Data Manipulation
Using LOB Locators
Lob - Host Variables
Examples using LOB Variables
Examples of Lob Manipulation using Locator Variables
File Reference Variables
Defining File Reference Variables
File Reference Variables - Example
Loading Data uning File Reference Variables
File Reference Variables - New Dsnzparm
Fetch Continue
Fetch Continue Example
Fetch Continue Considerations
DB2 JCC Java Driver
JCC Driver Enhancements
Spatial Support
Change to the SPUFI Defaults Panel

eXtensible Markup Language Introduction
Well Formed Documents
XML Data
Integration of XML Data
XML Serialization Function - XML2Clob
XML Publishing Function - XMLElement
XML Publishing Function - XMLAttributes
XML Publishing Function - XMLForest
XML Publishing Function - XMLConcat
XML Publishing Function - XMLAgg
XML Publishing Function - XMLNamespaces
Overview of pureXML
XML Data Model
XML Parser
XML Schema Repository (XSR)
XPath Axes
XPath Functions
XML Document Storage Infrastructure
Accessing XML Data
SQL/XML Publishing Functions
XML Indexes
Index Considerations
Application development
Database Administration Support
XML Schema
XML Decomposition

Reduction in CPU Processing
Load Enhancements - Sortkeys
Unload Enhancements - Skip Locked Data
Reorg Parallelism Enhancements
Reorg Shrlevel Change - NPSI Enhancement
Reorg Shrlevel Change - Drain Enhancement
Reorg Shrlevel Change - Lob Enhancement
Check Data Enhancements
Copy Enhancements - Checkpage Option
Copy Enhancements - Scope Pending
Recover Enhancements - Restorebefore
Recover Enhancements - PIT Recovery
Recover Enhancements - PIT Recovery Phases
Report Tablespaceset Enhancements
Modify Recovery Enhancements
Rebuild Index Enhancements
Template Switching
Clone Table Support
The Ability to Cancel DB2 Commands

The Existing 3 Tier Security Model
3 Tier Problems
Security Enhancement - Trusted Contexts / Roles
Trusted Contexts
Creating Trusted Contexts
Trusted Context / Role Examples
Authid Switching
Using a Trusted Connection in a JDBC Application
Trusted Connection - Catalog Changes
Other Security Enhancements

Initiating Automatic GRECP Recovery
Postponed Abort Units of Recovery
Restart Processing - Opening Datasets
New Access Database Command
Index Enhancements
Other Enhancements

Course Format

The course is purely lecture based and contains no practical exercises. A comprehensive Student Guide is supplied which contains detailed documentation of the new version together with many syntax examples where relevant. Delegates will also be able to access a free help-line with technical questions relating to topics covered on the course.



Please be aware that due to COVID-19 we are not currently running public courses. However, most of our courses can be delivered over the web (with mainframe access where necessary).

Dates are being arranged daily - please phone or email for further information.

Course Enquiry