DB28 - DB2 z/OS Version 9, 10 & 11 New Features - 3 Days

Course Description

This course is aimed at Systems Programmers, Programmers and DBA's who are moving from DB2 Version 8 to DB2 Version 11 and therefore need to understand the new features and facilities of DB2 Versions 9, 10 & 11. 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.

  • 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 & 11 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

SYSTEM ENHANCEMENTS
Virtual Storage Management
Storage Enhancements
z/OS Security Server / RACF Support
WLM Enhancements
Upgrading from V8 to V10
Deprecated and Unsupported Features
Virtual Storage Relief
Log / Latch Contention Enhancement
Catalog Contention Enhancement
Increased Number of Packages
Workfile Database Enhancements
Support for Extended Address Volumes
Extended RBA and LRSN
Log RBA / LRSN Format
Conversion to New Log Format - Steps
Conversion to 10 Byte RBA / LRSN Format
10 Byte RBA / LRSN Format - Considerations
Permitted Number of Open Datasets (DSMAX)

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
V10 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
Partition Rotation Enhancement
Version 10 Partition Rotation Enhancements
Rotating Partitions - Considerations
Default Tablespace
Autonomic Checkpoint
Dynamically Adding Active Log Datasets
Data Compression Enhancements
Version 10 - Compress on Insert / Merge / Load
Compression Considerations
Pending Changes Recap - Version 10
Recovery of Objects after Pending Changes Applied
Pending Changes - Recovery Example
Pending Changes - Running Modify Recovery
Pending Changes - Recovery Considerations
Restrictions between PIT Recovery and Reorg
Partition Limit Key Enhancement
Partition Limit Key Example
Partition Limit Key Alteration Considerations
Workfile Database Enhancements
The Wfstguse_Agent_Threshold Parameter
The Wfstguse_System_Threshold Parameter
Compression Dictionary - Replication Enhancement
Drop Column Support
Drop Column - Reorg Considerations
Drop Column - Recover Considerations
Drop Column - Restrictions
Defer Define Object Enhancements
Breaking Into Release Deallocate Threads

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 Statement
Merge Statement Restrictions
The Merge Statement - Rowset Processing
Merge Statement Example
Multi Row Processing - 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
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
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
Global Variables
Qualifying Global Variables
Global Variables - Scope
Stored Procedures - Array Data Type for SQL/PL
Ordinary Arrays
Associative Arrays
The Array_Agg Function
Arrays - New Built-In Functions
Passing Arrays - Examples
Sequences - Recap
Create Sequence Syntax - Recap
Aliases for Sequences
Temporal Tables - Recap
System Temporal Tables - Recap
System Temporal Tables and Versioning - Recap
System Temporal Tables - Data Access - Recap
Application Controlled Temporal Tables - Recap
Business Temporal Tables - Data Selection - Recap
Temporal Special Registers
Temporal Special Registers - Bind Options
Temporal Support on Views
Declared Temporary Tables - Recap
Declared Temporary Table - Commit Behaviour
Declared Temporary Tables - Logging Enhancement
Not Logged Temporary Tables - Undo Processing
Declared Temporary Tables and Thread Reuse
Group By Clause - Review
New Group By Features
Group By Grouping Sets
Group By Rollup
The Grouping Function
Group By Cube
Like_Blank_Insignificant Dsnzparm

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 using 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
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
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
Connectivity Enhancements
JDBC Type 2 Driver - Block Fetch
Database Access Thread (DBAT) Enhancement
Support for 64-bit ODBC Driver
Ensuring Application Compatibility
Archive Tables
Archive Table Control
Archive Table Considerations
Archive Table / Temporal Table Comparisons
Apache Hadoop Support for Big Data
Distributed Enhancements
Other Enhancements

XML ENHANCEMENTS
eXtensible Markup Language Introduction
XML Terminology
An XML Document Example
XML Data Model
Well Formed Documents
Valid Documents
Version 9 Enhancements - Overview of pureXML
XML Data Type
XML Columns - Summary Of Objects Created
Adding XML Columns to a Table
Node Storage
Relational Data vs XML (Hierarchical) Data
Displaying XML Information
The DB2 XML Parser
Schema Validation - The XML Schema Repository (XSR)
XML Decomposition
Accessing XML Data
Summary Of Xml Functions
Sample Data used in Examples
XML2Clob Function
XMLSerialize Function
XMLElement Function
XMLAttributes Function
XMLForest Function
XMLConcat Function
XMLAgg Function
XMLNamespaces Function
XMLComment Function
XMLDocument Function
XMLPi Function
XMLText Function
XMLParse Function
XPath Specification
XML Document used in Examples
Xpath Function - XMLQuery
Xpath Function - XMLExists
Xpath Function - XMLCast
Xpath Function - XMLTable
XPath Axes
XPath Functions
XML Indexes
Index Considerations
Application development
Utilities
XML Document Processing Enhancements
Using XmlModify to Insert Nodes
Using XmlModify to Update / Delete Nodes
XML Indexing Enhancement
XML Date and Time Support
XML Schema Validation Enhancement
XML Type Modifier
XML Validation Information
XML and Check Data
Support for Multiple Versions of XML Documents
XML Versioning Example
Support for Binary XML
Binary XML Conversion
XML in Native SQL Stored Procedures / UDFs
Support for DEFINE NO for LOBs and XML
Xquery Support
XQuery Introduction
FLWOR Constructs
XML Document used in Examples
FLWOR Constructs - Example
FLWOR Expressions with XMLExists
FLWOR Expressions with Formatted Output
FLWOR Constructs - WHERE Clause
FLWOR Constructs - RETURN Clause
FLWOR Constructs - Built In Functions
Selecting Nodes
Specifying XPath Predicates
Xml Performance Enhancements
Xml Flwor Query Rewrite

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
Quiesce with Not Logged Tablespaces
Internet Protocol Version 6 Support
Run Time Reoptimization Enhancement
Locking Recap - Page / Row Lock Modes
Row Change Timestamps
Row Change Timestamp Selection
Row Change Timestamp Insertion
Optimistic Locking - 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
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
Package Management Enhancement
Package Switching
Deleting Old Packages
RID Pool Enhancements
Range-list Index Scan
IN List Enhancements
Predicate Evaluation Table
Dynamic Prefetch Enhancements
Dynamic Statement Cache Enhancements
Insert Performance Improvement
BufferPool Enhancements
Work File Enhancements
Non-Key Columns in a Unique Index
Mass Delete Locking Enhancement
Other Performance Enhancements
Version 9 Lob Recap
Version 9 LOB Recap - Object Definition
LOB Enhancements - Inline LOBS
Other LOB Enhancements
Hash Tables
Tablespaces for Hash Tables
Creating a Hash Table
Hash Table Considerations
Hash Table Catalog Changes
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
Explain - New Special Register
Lastused Information for Packages
Changes to the Explain Facility
Plan Table Layout
Plan Table Column Definitions
The Dsn_Statemnt_Table
The Dsn_Function_Table
Excluding NULL Keys from an Index
General Performance Enhancements
Pseudo Deleted Rows - Index Entry Cleanup
Row Relocation Enhancement

UTILITY / COMMAND ENHANCEMENTS

SECURITY ENHANCEMENTS


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.


Course Enquiry