DB27 - DB2 Version 8 & 9 Transition - 3 Days


Course Description

This course is aimed at Systems Programmers, Programmers and DBA's who are moving from DB2 Version 7 to DB2 Version 9 and therefore need to understand the new features and facilities of DB2 Versions 8 & 9. This lecture based three-day course covers all of the new features in detail.

View Dates

Pre-requisites

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


Objectives

The aim of this course is to provide the delegate, familiar with DB2 V7, with the necessary skills required to use the new Version 8 & 9 features.


Environment

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


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.

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

Course Details

Version 8 Features

SYSTEM PERFORMANCE & CAPACITY IMPROVEMENTS
64-bit Architecture Support
DB2's Exploitation of 64-bit Architecture Support
Bufferpool Storage Enhancements
Bufferpool Configuration Changes
RID Pool Storage Enhancements
Sort Pool Storage Enhancements
Compression Dictionary Storage Enhancements
EDM Pool Storage Enhancements
Lob Data Storage Enhancements
Number of Open Datasets
IRLM Storage Enhancements
Increased Log Datasets

PARTITIONING ENHANCEMENTS
Increased Number Of Partitions
Maximum Number Of Partitions
New Considerations when Partitioning
Create Tablespace - DSSIZE Parameter (Recap)
Altering DSSIZE (Recap)
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
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
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

AVAILABILITY ENHANCEMENTS
Availability enhancements
Online Schema Alterations
DB2 Availability Prior to V8
Online Schema Maintenance
Table Data Type changes
New Syntax for Alter Table
Alter Data Type - Impact Upon Table
Alter Data Type - Impact Upon Indexes
Alter Data Type - Index Availability
Alter Data Type - Impact Upon Views / Check Constraints
Alter Data Type - Considerations
Alter Data Type - Restrictions
New Syntax for Alter Index
Alter Index Add Column - Restrictions
Dropping Partitioning Indexes
Deferred Index Creation
Rebuild Pending - Index Avoidance
Versioning
Version Generating Alter Statements
Version Limits
How Does Versioning Work?
Finding Version Information
Reducing the Number of Active Versions
Versioning and Recovery
Object Schema Change Considerations
Summary of Database State Changes
TableSpace and Index Sizing
Online ZPARM Changes

SQL ENHANCEMENTS
Long Names
SQL Identifier Length Limits
SQL Statements 2MB long
Select from Insert
Select from Insert Example
Result Table Rows from the Insert Statement
Select From Insert in a Cursor
Select From Insert - New Order By Option
Select From Insert - Error Processing
Select From Insert - Using Cursors With Hold
Select From Insert - Using Savepoints
Updates and Deletes against Result Table Rows
Select From Insert - Considerations
Expressions / Functions in Group By
Qualified Column names in Insert and Update
Is Not Distinct From
Hidden Rowid Generation
Multiple Distinct
Scalar Fullselect
Scalar Fullselect Examples
Scalar Fullselect Restrictions
Common Table Expressions
Writing a Common Table Expression
Common Table Expression Example
Common Table Expression Considerations
Recursive SQL
Recursive SQL Example
Recursive SQL - Controlling Depth of Recursion
Read Only Using Update Locks
Session Variables
New Special Registrers

PROGRAMMING ENHANCEMENTS
Dynamic Scrollable Cursors
Insensitive Cursor - Recap
Sensitive Static Cursor with Insensitive Fetch - Recap
Sensitive Static Cursor with Sensitive Fetch - Recap
Fetching from a Scrollable Cursor - Recap
Sensitive Fetches - Update and Delete Holes
Sensitive Fetches - Updated Data
Sensitive Static Cursor with Sensitive Fetch
New Cursor Syntax
Sensitive Dynamic and Asensitive Cursors
Sensitive Cursors - Fetching Rows
Scrollable Cursor Considerations
Multi-row Fetch and Insert
New Syntax for Declare Cursor
Fetching Rowsets
Fetch Examples
Host Variable Arrays
Catering for Update and Delete Holes
Partial Rowsets
Rowsets - SQLCA
Locking Rowsets
Fetch First 'n' Rows
Positioned Update
Positioned Delete
Multi-row Insert
Multi-row Insert Syntax
Static & Dynamic Insert
Get Diagnostics
Get Diagnostics - Statement Information
Get Diagnostics - Condition Information
Get Diagnostics - Connection Information
Get Diagnostics - Examples
Diagnostic Information for Multi-Row Fetch
Get Diagnostics Fetch Example
Diagnostic Information for Multi-Row Insert
Get Diagnostics Insert Example
Stored Procedure / Function Enhancements
Handling Failed Procedures / Functions
Procedure / Function Command Enhancements
Workload Manager Enhancements
Stored Procedure Deprecations
Sql Procedures Language - New Statements
SQLPL - RETURN Statement
SQLPL - GET DIAGNOSTICS Statement
SQLPL - ITERATE Statement
SQLPL - SIGNAL Statement and Message_Text Variable
SQLPL - RESIGNAL Statement
A New Development Center
Debugging Stored Procedures
MQSeries UDFs
Set Current Schema
Set Current Package Path

IDENTITY COLUMNS AND SEQUENCES
Identity Column Review
Identity Column Enhancements
Altering Identity Columns
Identity Columns - New Parameters
Identity Columns - Data Sharing Implications
Using Identity Columns with the Load Utility
Catalog Table Changes
Sequences
Create Sequence Syntax
Sequence Ordering
Altering Sequences
Dropping Sequences
Sequence Authorities
Using Sequences in Applications
Sequences - Considerations and Restrictions
Sequence Application Examples
Consumed Values / Gaps in a Sequence
Duplicate Sequence Values
Sequence Cycle Considerations
Defining a Constant Sequence
Cache Considerations
Sequences and Identity Columns Comparison

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
AQR Examples
Determining if Query Rewrite Occurred
MQTs and Referential Integrity
MQTs and RI - Informational Constraints

APPLICATION PERFORMANCE
Variable Length Index Keys
Variable Length Index Keys - Performance
Altering Index Padding
Index Key Length
Backward Index Scan
Indexable and Stage 1 Predicates
Comparing Unlike Data Types
Mismatched Numeric Comparisons
Mismatched Character Comparisons
Table UDF Cardinality
Table UDF Materialized Fetch
Trigger Enhancements
Cost Based Parallel Sort
Volatile Tables
Star Join Recap
Star Join Example
Star Join Enhancements
Run Time Reoptimization Enhancement
Explaining the Statement Cache
Declared Temporary Table Enhancement
Lock Avoidance Enhancement
SQL Non-Correlated Exists - Enhancement
SQL In List Processing - Enhancement
SQL In List Processing - Dynamic Prefetch
New Plan Table Columns
Plan Table Column Definitions

VISUAL EXPLAIN
Visual Explain Overview
Visual Explain Pre-Requisites
Connecting to a Subsystem
Enabling Visual Explain
Tuning Plans and Packages
Tuning Ad-Hoc SQL
Tuning the Dynamic Statement Cache

E-BUSINESS ENHANCEMENTS
E-Business Enhancements
Java JCBC Driver Recap
Database Environments
A New DB2 Universal Driver
DB2 Universal Driver - Additional Functionality
XML Enhancements
XML Publishing Functions
XML Publishing 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
Unicode
Unicode History
Character Conversion Terminology
Code Page Differences
Character Conversion Implementation
How Unicode Works
UTF-8 Characters
UTF-16 Characters
DB2 Version 8 and Unicode
Specifying The Unicode CCSID
Unicode Support
Unicode - Impact on Applications
Collating Sequence - Order By
The Unicode Precompiler and SQL Parser
Cobol and PL/I Conversion Issues
Multilple CCSIDs in a Single Statement
Unicode - Further Information

UTILITY / COMMAND ENHANCEMENTS
System level point-in-time recovery
Delimited Load and Unload
Runstats Enhancements
New defaults Sortdata and Sortkeys
Automatic Utility Restart
Reorg Rebalance
On-line Reorg for Catalog
Copy with Systempages
Repair and Areo
Repair Versions
Dsn1copy and System Pages
Dpsi Support
Other Utiltiy Changes
ALTER BUFFERPOOL enhancement - PGFIX

DATA SHARING ENHANCEMENTS
Data Sharing Review
Coupling Facility Review
Locking Review
Hierarchical Locking V7 Review
Version 8 Global L-Lock Enhancement
Child L-Lock Propagation Enhancement
Group Bufferpool Review
Group Bufferpool Castout Review
Version 8 Castout Enhancement
Improved LPL Recovery
Batched Updates For Index Page Splits
Resolution Of Indoubt URs in Restart Light

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
MLS Considerations and Restrictions
Using Session Variables for Security
Special Registers for User Identification
Encrypting Data

Version 9 Features

AVAILABILITY AND CAPACITY ENHANCEMENTS
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

SQL ENHANCEMENTS
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 PERFORMANCE ENHANCEMENTS
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
Not Logged - SYSLGRNX and SYSCOPY
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

APPLICATION PERFORMANCE ENHANCEMENTS
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

PROGRAMMING ENHANCEMENTS
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

XML SUPPORT / PUREXML
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
XPath Axes
XPath Functions
XML Document Storage Infrastructure
Accessing XML Data
SQL/XML Publishing Functions
XMLCOMMENT
XMLDOCUMENT
XMLPI
XMLTEXT
XMLSERIALIZE
XMLPARSE
XMLQUERY
XMLEXISTS
XMLCAST
XMLTABLE
XML Indexes
Index Considerations
Application development
Database Administration Support
XML Schema
XML Decomposition
Utilities

UTILITY / COMMAND ENHANCEMENTS
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

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

DATA SHARING 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.


Availability

Course Enquiry