| DB28 - DB2 Version 9 & 10 Transition |
|---|
| Duration | 3 Days (customisation) |
| Availability |
|
Course Description
This course is aimed at Systems Programmers, Programmers and DBAs 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.
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.
| 
