Target Audience

This course is aimed at Systems Programmers and DBA’s who need to understand the new features and facilities of DB2 Version 7. This lecture based one-day course covers all of the new features in detail.

Pre-requisites

A strong working 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

UTILITY LISTS & DYNAMIC ALLOCATION

Larger Tablespaces (EA Enabled)

Dynamic Utility Jobs

Using Listdef / List for Dynamic List Processing

Listdef Syntax

Listdef Parameters

Listdef Specification

Listdef Examples

Recovery Related Lists

Listdef Expansion Steps

Listdef Considerations

How Many Times Does The Utility Execute?

Listdef Restartability

Using Template to Allocate Datasets

Template Syntax

Template Parameters

Substitution Variables for Dataset Names

Template Specification

Template Examples

Space Allocation with Templates

Disposition Allocation with Templates

Considerations for Dataset Allocation

Template and Listdef Combined

Storing Listdefs and Templates in Libraries

Additional Options

DB2I Support for Listdef and Template

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

Faster Online Reorg

The Fastswitch Parameter

Online Reorg Phases

Reorg - Switch Phase for Fastswitch Yes

Reorg - Switch Phase for Fastswitch No

Catalog Information

User-Managed Datasets

Reorg - Faster Build2 Phase

Reorg - Drain and Retry

Online Load Resume

Online Load Considerations

Online Load Restrictions

Online Load Utility Phases

Online Load Compatibility

Copytocopy Utility

Copytocopy Parameters

Copytocopy Examples

Copytocopy Considerations

Recover Enhancements

Changing System Parameters Online

Setting New Parameters

Setparm Load Modules

Consistent Restart Enhancements

Recover Postponed Cancel

Cancel Thread Nobackout

Set Log Command

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

DATA SHARING ENHANCEMENTS

Restart light

Group Attach Behaviour

Persistent structure size changes

Faster Shutdown of Data Sharing Members

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.