DB2 V8 Transition for Programmers

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 features.

Course Details
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
Course Environment The lectures apply to DB2 running in a z/OS environment.

Course Format: This course can run as a 1 day lecture or as a 2 day 'hand-on' workshop.