Target Audience

This course is aimed at DB2 Applications Programmers who need to understand the new programming and SQL features of DB2 Version 8. This course can run as a 1 day lecture or as a 2 day 'hands-on' workshop and covers all of the new SQL and programming related features in detail.

Pre-requisites

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

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 Environment

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

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 Format

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