Introduction

• The Big picture
  • 64-bit virtual storage
  • Unicode
  • Hardware exploitation – zIIPs
  • XML
• Migration bases

The Course Database

• Computer Exercise:  Course Setup

SQL changes – 1

• Limit changes
• DISTINCT and NOT DISTINCT
• Scalar Fullselect
• GROUP BY
• FETCH FIRST with ORDER BY
• EXCEPT and INTERSECT
• ORDER BY and FETCH FIRST in subselect
• TRUNCATE TABLE
• Common Table expressions
• Recursive SQL
• Computer Exercise:  Exploring New SQL – 1

SQL changes – 2

• New built-in functions
• OLAP-related functions
• MERGE
• SELECT FROM INSERT/UPDATE/DELETE/MERGE
• Computer Exercise:  Exploring New SQL – 2

Scrollable cursors

• INSENSITIVE
• SENSITIVE STATIC
• 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

• FETCH CONTINUE
• LOB file reference variables
• Bind Enhancements: REOPT AUTO and SWITCH
• SKIP LOCKED DATA clause
• Computer Exercise:  Application Programming Environment Enhancements

Native SQL stored procedures

• CREATE / ALTER PROCEDURE
• Versioning
• Computer Exercise:  Native SQL Stored Procedures

Data Base Administration Enhancements

• Sequence objects
• Clone tables
• Row change timestamps
• New data types: BIGINT, BINARY, VARBINARY, DECFLOAT
• Random indexes
• Indexing on expressions
• Computer Exercise:  Data Base Administration Enhancements

XML

• XML overview
• XML and DB2
• XML and SQL
• XML in application programs
• XML Indexing
• Computer Exercise:  Exploring XML in DB2