Target Audience

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.

Pre-requisites

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

Course Environment

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

Course Details

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

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.