DB28 - DB2 Version 9 & 10 Transition
Duration3 Days (customisation)
Availability

Course Description

This course is aimed at Systems Programmers, Programmers and DBA’s who are moving from DB2 Version 8 to DB2 Version 10 and therefore need to understand the new features and facilities of DB2 Versions 9 & 10. This lecture based three-day course covers all of the new features in detail.

Pre-requisites

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

Objectives

The aim of this course is to provide the delegate, familiar with DB2 V8, with the necessary skills required to use the new Version 9 & 10 features.

Environment

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

Customisation

For on-site courses (i.e. at your premises), we are more than happy to tailor the course agenda to suit your exact requirements. In many cases, we are able to build your in-house standards and naming conventions into the delivered course.

Course Details

Version 9 Features
AVAILABILITY AND CAPACITY ENHANCEMENTS
Version 8 Recap - Types Of Tablespace
Version 8 Recap - Tablespace Definition
Version 9 - Universal Tablespaces
Universal Tablespace Benefits
Maximum Number Of Partitions
Partition-By-Growth Tablespace
Creating A Partition-By-Growth Tablespace
Partition-By-Growth Tablespaces - Behaviour
Partition-By-Growth - Partition Allocation
Partition-By-Growth Tablespaces - Insert
Partition-By-Growth Tablespaces - Reorg
Partition-By-Growth Tablespaces - Other Utilities
Partition-By-Range Tablespace
Partition-By-Range Tablespaces - Considerations
Universal Tablespaces - Catalog Changes
Implicit Object Creation
Automatic Database Creation
Implicit Database Creation - Considerations
Automatic Tablespace Creation
Default Bufferpool Usage
Other Implicit System Required Objects
SMS Storage Classes with Db2 Defined Datasets
Clone Tables - Fast Data Replacement
Why Use Clone Tables?
Creating A Clone Table
Dropping A Clone Table
Clone Table Considerations
Creating a Clone - Catalog Activity
Creating a Clone - Index and Lob Catalog Activity
Clone Tables - The Exchange Command
Clone Tables - Exchange Considerations
Clone Tables - Locking Considerations
Clone Tables - DB2 Commands
Clone Tables - Authority
Clone Tables - Utilities
Column Renaming
Column Renaming Restrictions
Alter Long Varchar to Varchar
Refresh EARLY Code Without an IPL
Conditional Restart Log Truncation by Timestamp
Improved Insert Performance - The Append Parameter
Index Renaming
Catmaint Enhancement - Update Schema
Update Schema Restrictions
SQL ENHANCEMENTS
Trigger Recap
Trigger Recap - Before and After Triggers
Instead Of Triggers
Instead Of Triggers - Restrictions
Instead Of Triggers - Authorisation
Instead Of Triggers - Catalog Changes
The Merge Statements
Merge Statement Restrictions
Merge Statement Example
Get Diagnostics Recap
Get Diagnostics - Statement Information
Get Diagnostics - Condition Information
Get Diagnostics - Merge Example
Explaining Merge Statements
Merge Statement Authorisation
Select From Merge / Update / Delete Statements
Select from Insert - Version 8 Recap
Select from Merge
Select from Update
Select from Delete
Order By and Fetch First in Subselect
The Order By Order Of Clause
The Truncate Statement
Truncate Examples
Using Truncate With Multi-Level Security
Truncate Restrictions
Union, Intersect and Except
Version 8 Recap - Union
Intersect and Except
Intersect and Except Examples
Set Current Schema Enhancement
Cultural Sort and Case Insensitive Comparison
Collation Key Examples
Implicitly Hidden Columns
Bigint - Data Type
Bigint - Scalar Function
Binary / Varbinary - Data Types
Binary - Data Type and Function
Decfloat - Data Type
Decfloat - Scalar Function
New Scalar Functions
New Scalar Functions
SYSTEM PERFORMANCE ENHANCEMENTS
System Z and zIIP Exploitation
Virtual Storage Management
Index Page Size
Index Page Size Specification
Index Compression
Index Compression Considerations
Index Compression - Estimation
Index Compression - Dsn1 Sample Output
Table vs. Index Compression Comparisons
Resource Limit Facility Enhancement
Buffer Manager Enhancements
Logging Recap
Not Logged Tablespaces
Switching On and Off Not Logged
Logging Considerations
Not Logged with XML Tablespaces
Not Logged with LOB Tablespaces
Not Logged - SYSLGRNX and SYSCOPY
Recovering Not Logged Tablespaces
Load / Reorg with Not Logged Tablespaces
Rebuild Index with Not Logged Tablespaces
Check Data with Not Logged Tablespaces
Queisce with Not Logged Tablespaces
Internet Protocol Version 6 Support
APPLICATION PERFORMANCE ENHANCEMENTS
Run Time Reoptimization Enhancement
Locking Recap - Page / Row Lock Modes
Locking Recap - Isolation Levels
Locking Recap - Sensitive Static Cursors
Locking Recap - Sensitive Cursor Update / Delete Holes
Locking Recap - Sensitive Cursor Positioned Updates
Optimistic Locking
Optimistic Locking Example
Row Change Timestamp Selection
Row Change Timestamp Insertion
Skip Locked Data
Index On Expression
Index On Expression Considerations
Relief for Sequential Key Insert
Randomized Index Ordering
Reordered Row Format
Moving to Reordered Row Format
Histogram Statistics
Page-Range Screening Optimization
Global Query Optimization
PROGRAMMING ENHANCEMENTS
Stored Procedures Recap
An SQL PL Procedure Example
Procedure Preparation
Native (Internal) SQL Procedures
How Native SQL Procedures Work
Creating Native and External SQL Procedures
Creating Native SQL Procedures
Altering Native SQL Procedures
Native SQL Procedures - Versioning
Native SQL Procedures - Binding Across Servers
Native SQL Procedures - Command Enhancements
Other Sql-Pl Enhancements
Sql Pl - Get Diagnostics Enhancement
Sql Pl - FOR Statement
Sql Pl - Nested Compound Statements
SQL Procedure Example - Nested Compound Statement
Sql Pl - New Special Registers
Debugging Native Stored Procedures
LOB Data Manipulation - Recap
LOB Definition And Manipulation
Base Table Definition
LOB Tablespace And Auxiliary Table Requirements
LOB Tablespace / Table Definition
LOB Data Manipulation
Using LOB Locators
Lob - Host Variables
Examples using LOB Variables
Examples of Lob Manipulation using Locator Variables
File Reference Variables
Defining File Reference Variables
File Reference Variables - Example
Loading Data uning File Reference Variables
File Reference Variables - New Dsnzparm
Fetch Continue
Fetch Continue Example
Fetch Continue Considerations
DB2 JCC Java Driver
JCC Driver Enhancements
Spatial Support
Change to the SPUFI Defaults Panel
XML SUPPORT / PUREXML
eXtensible Markup Language Introduction
Well Formed Documents
XML Data
Integration of XML Data
XML Serialization Function - XML2Clob
XML Publishing Function - XMLElement
XML Publishing Function - XMLAttributes
XML Publishing Function - XMLForest
XML Publishing Function - XMLConcat
XML Publishing Function - XMLAgg
XML Publishing Function - XMLNamespaces
Overview of pureXML
XML Data Model
XML Parser
XML Schema Repository (XSR)
XPATH
XPath Axes
XPath Functions
XML Document Storage Infrastructure
Accessing XML Data
SQL/XML Publishing Functions
XMLCOMMENT
XMLDOCUMENT
XMLPI
XMLTEXT
XMLSERIALIZE
XMLPARSE
XMLQUERY
XMLEXISTS
XMLCAST
XMLTABLE
XML Indexes
Index Considerations
Application development
Database Administration Support
XML Schema
XML Decomposition
Utilities
UTILITY / COMMAND ENHANCEMENTS
Reduction in CPU Processing
Load Enhancements - Sortkeys
Unload Enhancements - Skip Locked Data
Reorg Parallelism Enhancements
Reorg Shrlevel Change - NPSI Enhancement
Reorg Shrlevel Change - Drain Enhancement
Reorg Shrlevel Change - Lob Enhancement
Check Data Enhancements
Copy Enhancements - Checkpage Option
Copy Enhancements - Scope Pending
Recover Enhancements - Restorebefore
Recover Enhancements - PIT Recovery
Recover Enhancements - PIT Recovery Phases
Report Tablespaceset Enhancements
Modify Recovery Enhancements
Rebuild Index Enhancements
Template Switching
Clone Table Support
The Ability to Cancel DB2 Commands
SECURITY ENHANCEMENTS
The Existing 3 Tier Security Model
3 Tier Problems
Security Enhancement - Trusted Contexts / Roles
Trusted Contexts
Creating Trusted Contexts
Roles
Trusted Context / Role Examples
Authid Switching
Using a Trusted Connection in a JDBC Application
Trusted Connection - Catalog Changes
Other Security Enhancements
DATA SHARING ENHANCEMENTS
Initiating Automatic GRECP Recovery
Postponed Abort Units of Recovery
Restart Processing - Opening Datasets
New Access Database Command
Index Enhancements
Other Enhancements
Version 10 Features
SYSTEM ENHANCEMENT
System z Suppor
IBM System z and z/OS Support
IBM zEnterprise System Support
z/OS Security Server Support
TCP/IP Support
WLM Enhancements
Using RMF for zIIP Reporting and Monitoring
Warehousing on System z
Data encryption
IBM WebSphere DataPower
Additional zIIP and zAAP Eligibility
SCALABILITY ENHANCEMENTS
Virtual Storage Relief
Latch Contention Enhancement
Catalog Contention Enhancement
Increased Number of Packages
The WORKFILE database enhancements
Elimination of UTSERIAL for DB2 Utilities
Support for Extended Address Volumes
Shutdown and Restart Times, and DSMAX
Compression of SMF Records
AVAILABILITY ENHANCEMENTS
Version 9 Recap - Universal Tablespaces
Version 9 Recap - Partition-By-Growth Tablespace
Creating A Partition-By-Growth Tablespace
Version 9 Recap - Partition-By-Range Tablespace
Universal Tablespace Enhancements
Alter Tablespace Enhancements
Alter Tablespace Enhancements - Considerations
Pending Changes
Pending Changes – Areor State
Pending Changes – Reorg Materialisation
Pending Changes – Reorg Partition Resizing
Alter Tablespace Examples
Alter Tablespace - Member Cluster
Online Schema Changes – Recovery
Default Tablespace
Autonomic Checkpoint
Active Log Data Set Enhancement
Backout Enhancement
Partition Rotation Enhancement
Compress on Insert
Additional Control for Long Running Readers
Online REORG Enhancements
Check Utility - Increased Availability
SQL ENHANCEMENTS
Recap – SQL/PL Scalar Functions
SQL/PL Scalar Function Enhancements
Inline and Non-Inline SQL/PL Scalar Functions
SQL/PL Scalar Functions – New Control Statements
Non Inline Functions – Binding Considerations
Non Inline Function Examples
Altering Non Inline SQL Functions
Native SQL Functions - Versioning
Enhanced SQL Table Function Support
Enhanced Native SQL Procedure Support
Greater Support for Implicit Casting
Datetime Constants
Variable Timestamp Precision
Variable Timestamp Precision – Current Timestamp
Variable Timestamp Precision – Row Change Timestamps
Variable Timestamp Precision – Catalog Changes
New ‘Timestamp with Time Zone’ Data Type
Timestamp with Time Zone – String Representation
Implicit Time Zone
Timestamp with Time Zone – Assignment and Comparison
Current Timestamp with Time Zone – Special Register
Session Time Zone – Special Register
Time Zone Expressions and Arithmetic
New Timestamp Functions
Application Programming Considerations
PROGRAMMING ENHANCEMENTS
Temporal Tables
Temporal Tables and Versioning
Temporal Tables – Versioning Example
System Temporal Tables – Data Access
Temporal Tables – Considerations
Temporal Tables – Application Controlled
Business Temporal Tables – Data Selection
Business Temporal Tables – Update and Delete
Business Temporal Tables – Update Example
Business Temporal Tables – Delete Example
Temporal Tables – Catalog Information
A New Access Path Framework
Access Path Framework – New Repository
Access Path Framework – New Catalog Tables
Subsystem Level Access Path Hints
The DSN_USERQUERY_TABLE Table
Inserting Rows Into DSN_USERQUERY_TABLE
Access Path Framework – The Bind Query Command
Access Path Framework – The Free Query Command
Access Path Framework – Access Plan Stability
Version 9 Recap - Package Management
Version 9 Recap - Package Switching
Deleting Old Packages
Access Plan Stability – V10 Enhancements
Extended Indicator Variables
Extended Indicator Variables - Example
New Universal Language Interface Program (Dsnuli)
Locking Recap – V9 - Skip Locked Data
V10 Locking Enhancement - Access To Committed Data
Explain – New Special Register
Lastused Information for Packages
Connectivity Enhancements
DDF Availability
Monitoring and Controlling Enhancements
JDBC Type 2 Driver Performance Enhancements
High Performance DBAT
Use of RELEASE(DEALLOCATE) in Java Applications
Support for 64-bit ODBC Driver
DRDA Unicode Enhancements
DB2 Supplied Stored Procedures
XML ENHANCEMENTS
Recap - XML in Version9
Recap - XML Data Type
Recap - Summary Of XML Objects Created
Recap - XML Document Processing in Version 9
XML Document Processing Enhancements
Using XmlModify to Insert Nodes
Using XmlModify to Update / Delete Nodes
XML Indexing Enhancement
XML Schema Validation Enhancement
XML Type Modifier
Support for Binary XML
Support for Multiple Versions of XML Documents
PERFORMANCE ENHANCEMENTS
Improved Optimization Techniques
Dynamic Prefetch Enhancements
DDF Enhancements
Dynamic Statement Cache Enhancements
Insert Performance Improvement
Referential Integrity Checking Enhancement
BufferPool Enhancements
Work File Enhancements
Support For z/OS Enqueue Management
LOB Enhancements
Java & ODBC Performance Enhancements
Logging Enhancements
Hash Access
Non-Key Columns in a Unique Index
Support for Solid State Drive
The SQL PL Enhancements
Preemptable Backout
Mass Delete Locking Enhancement
Parallelism Enhancements
Online Performance Enhancements
Monitoring Enhancements
UTILITY ENHANCEMENTS
Support FlashCopy Enhancements
Autonomic Statistics
Recover with BACKOUT YES
Online Reorg Enhancements
SECURITY ENHANCEMENTS
Policy Based Audit Capability
System Authorities and Privilege Enhancements
System Defined Routines
The REVOKE Dependent Privilege Clause
Row and Column Access
Support for z/OS Security Features
DATA SHARING ENHANCEMENTS
Subgroup Attach Name
Bufferpool Scan Avoidance
Universal Table Space Support for Member Cluster
Restart Light Enhancement
Auto Rebuild Coupling Facility Enhancement
LRSN Enhancement
Cross Invalidation Enhancement

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.