Target Audience

This course is aimed at DB2 Applications Programmers who need to understand the new programming and SQL features of DB2 Version 7. This lecture based one-day course covers all of the new SQL and programming related features in detail.

Pre-requisites

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

Course Objectives

The aim of this course is to provide the delegate, familiar with DB2 V6, with the necessary skills required to use the new Version 7 features. On completion of this course the student will be able to:

•     use Utility Lists when running DB2 Utilities
•     use Dynamic Allocation when running DB2 Utilities
•     utilise many of the new performance features introduced to Utilities
•     use new Utilities such as UNLOAD, COPYTOCOPY and EXEC SQL
•     understand and use the new SQL enhancements
•     understand the new programming enhancements
•     understand and use Scrollable Cursors
•     use many of the new system performance enhancements
•     understand the new Data Sharing enhancements
•     have an awareness of the new E-Business enhancements

Course Environment

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

Course Details

COMMANDS & UTILITIES

New Utilities Packaging

DB2 Operational Utilities

DB2 Diagnostic and Recovery Utilities

DB2 Utilities Suite

Unload Utility

Unload Syntax

Unload Parameters

Unload Options

Unload Examples

Unloading from Copy Datasets

Unload Restrictions

Load Partition Parallelism

Parallel Load Syntax

Partition parallel LOAD without Sortkeys

Partition parallel LOAD with Sortkeys

Online Load Resume

Online Load Considerations

Online Load Restrictions

Online Load Utility Phases

Online Load Compatibility

Runstats - Historical Data

Deleting Historical Statistics

New Catalog Statistics

The EXEC SQL Utility

Cross Loader

SQL ENHANCEMENTS

Consistent Constraint Syntax

Constraint Naming

Dropping Enforcing Indexes

Catalog Changes for Constraints

Greater use of Unions

Fullselects and Subselects

Union Example in a View

Union Example in a Table Specification

Union Example within a Where Clause

Union Example within an Insert or Update

Multiple Column 'In' Predicate

Multiple Column Subselect

Multiple Column Basic Predicate

Self-Referencing Update / Delete

Fetch First 'n' Rows Only Clause

Fetch First vs Optimize For

Using Fetch First for Singleton Selects

PROGRAMMING ENHANCEMENTS

Precompiler Services

Existing Program Preparation

Program Preparation using Precompiler Services

User Defined Function Enhancements

Scrollable Cursors

Declaring a Scrollable Cursor

Scrollable vs Non-scrollable Cursors

Updatable Cursors

Declaring a Scrollable Cursor

Fetching from a Scrollable Cursor

Absolute Fetching Examples

Relative Fetching Examples

Insensitive and Sensitive Cursors

Fetch Sensitivity for Sensitive Cursors

Sensitive Fetches - Update and Delete Holes

Sensitive Fetches - Updated Data

Scrollable Cursor - Locks on the Base Table

Scrollable Cursor Recommendations

REXX Support

Stored Procedure Enhancements

SQLJ Support

JDBC Enhancements

SQL Procedures

APPLICATION & SYSTEM PERFORMANCE

Identity Columns

Identity Columns - Syntax

Identity Columns - Examples

Identity Columns - Considerations

Identity Columns - Restrictions

Identity Columns - Catalog Changes

Identity_Val_Local Function

Savepoints

Savepoint Definition

Savepoints - Considerations and Restrictions

Declared Temporary Tables

Declared Temporary Table Considerations

Declared Temporary Tables - Comparisons

Parallelism for IN Lists

Transform Correlated Subqueries

Fewer Sorts with Order By

Min/Max Performance Improvement

E-BUSINESS ENHANCEMENTS

DB2 Extender for Text Objects (Text Extender)

XML Extender for z/OS

XML Storage and Access Methods

Dtd Repository

Unicode Support

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.