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