G525 DB2 Version 7 to Version 9 Transition

DB2 Version 7 to Version 9 Transition - Topical Outline

Introduction

The Big picture - Version 8

The Big picture - Version 9

The Sample Application

Computer Exercise: Course Setup

SQL changes - 1 (Version 8)

Limit changes

DISTINCT and NOT DISTINCT

Scalar Fullselect

INSERT within SELECT

GROUP BY

FETCH FIRST with ORDER BY

New Built-in Functions

Session Variables

Computer Exercise: SQL Changes - 1 (V8)

Programming Environment Changes (Version 8)

Scrollable cursors

Version 7 Review

SENSITIVE DYNAMIC cursors

ASENSITIVE cursors

FETCH syntax

Update and locking

Multiple Row SQL

Rowsets

FETCH/INSERT/UPDATE/DELETE

Multi-row INSERT within SELECT

GET DIAGNOSTICS

New Special Registers

Computer Exercise: Programming Environment Changes (V8)

SQL Changes, - 2 (Version 8)

Nested Table Expressions Review

Common Table expressions

Recursive SQL

PLAN_TABLE and EXPLAIN enhancements

Computer Exercise: SQL Changes - 2 (V8)

SQL Changes - 3 (Version 9)

EXCEPT and INTERSECT

ORDER BY and FETCH FIRST

TRUNCATE

Computer Exercise: SQL Changes - 3 (V9)

SQL Changes - 4 (Version 9)

MERGE

SELECT from INSERT extensions

SELECT from UPDATE, DELETE, and MERGE

New built-in functions

OLAP-related functions

Computer Exercise: SQL Changes - 4 (V9)

Programming Environment Changes (Version 9)

INSTEAD OF Triggers

FETCH CONTINUE

LOB File Reference Variables

Bind / Rebind enhancements

REOPT AUTO

Access plan stability

New / Improved Special Registers

SKIP LOCKED DATA

Computer Exercise: Programming Environment Changes (V9)

Version 8 Stored Procedure Enhancements

Native SQL Stored Procedures

Version 8 Environment

Version 9 Environment

CREATE PROCEDURE / ALTER PROCEDURE

Versioning

Debugging

Command Changes

Start / Stop / Display Procedure

Bind / Rebind Package

Catalog Changes

Language Extensions

Computer Exercise: Native SQL Procedures

Data Base Administration Enhancements - 1 (Version 8)

Identity column enhancements

Sequence objects

Sequences and cursors

LOBs and transparent ROWIDs

Materialized Query Tables

Security Labels

Computer Exercise: Sequences, LOBs, and MQTs

Data Base Administration Enhancements - 2 (Version 8)

Table-controlled partitioning

Partitioning Indexes

Secondary Indexes

Data-partitioned Secondary Indexes

Clustering Indexes

Computer Exercise: Table-controlled Partitioning

Data Base Administration Enhancements - 3 (Version 8)

Adding Partitions

Rotating Partitions

Online Schema Changes

Versioning

Index enhancements

Computer Exercise: Schema and Partition Changes

Data Base Administration Enhancements - 4 (Version 9)

Universal table spaces

Partition-by-growth Table Spaces

Range-partitioned Table Spaces

Cloning tables

Implicit data bases and object creation

Computer Exercise: Universal Table Spaces and Cloning

Data Base Administration Enhancements - 5 (Version 9)

Data base and storage group new features

Table space new features

NOT LOGGED table spaces

Tables - new features

CREATE TABLE ... APPEND

Reordered row format

Row change timestamps

Column enhancements

New data types: BIGINT, BINARY, VARBINARY, DECFLOAT

Indexes - renaming, page sizes, compression, and indexing on expressions

Computer Exercise: Table Spaces and Column Features

Utility Enhancements (Version 8)

BACKUP / RESTORE SYSTEM

Default changes

REORG, REBUILD, COPY, and REPAIR

Informational referential integrity and utilities

DSN1COPY and DSN1PRNT

Unicode and utilities

LOAD / UNLOAD

RUNSTATS

Utilities for DPSIs

Computer Exercise: Utility Enhancements - 1 (V8)

Utility Enhancements (Version 9)

General

LOAD / UNLOAD

COPY / MODIFY RECOVERY / RECOVER

CHECK DATA / CHECK LOB / CHECK INDEX

RUNSTATS

REORG

Miscellaneous - REPAIR, REPORT TABLESPACESET, REBUILD INDEX,

TEMPLATE, DSN1COPY, DSN1LOGP, DSNUJ004,

BACKUP / RESTORE SYSTEM

Computer Exercise: Utility Enhancements - 2 (V9)

XML

XML overview

XML and DB2

XML table spaces

XML and SQL

Version 8 XML functions

Version 9 XML functions

XML in application programs

XML indexing

XML schema support

XML catalog tables

XML impact on utilities

Computer Exercise: Working with XML Data

G525 / 5 Days

Materials version: V1.x

Course Objectives
Course Description
Home page