| DB27 - DB2 Version 8 & 9 Transition |
|---|
| Duration | 3 Days (customisation) |
| Availability |
|
Course Description
This course is aimed at Systems Programmers, Programmers and DBA’s who are moving from DB2 Version 7 to DB2 Version 9 and therefore need to understand the new features and facilities of DB2 Versions 8 & 9. This lecture based three-day course covers all of the new features in detail.
Pre-requisites
A strong working knowledge of DB2 Version 7 is required to attend this course.
Objectives
The aim of this course is to provide the delegate, familiar with DB2 V7, with the necessary skills required to use the new Version 8 & 9 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 8 Features
- SYSTEM PERFORMANCE & CAPACITY IMPROVEMENTS
- 64-bit Architecture Support
- DB2's Exploitation of 64-bit Architecture Support
- Bufferpool Storage Enhancements
- Bufferpool Configuration Changes
- RID Pool Storage Enhancements
- Sort Pool Storage Enhancements
- Compression Dictionary Storage Enhancements
- EDM Pool Storage Enhancements
- Lob Data Storage Enhancements
- Number of Open Datasets
- IRLM Storage Enhancements
- Increased Log Datasets
- PARTITIONING ENHANCEMENTS
- Increased Number Of Partitions
- Maximum Number Of Partitions
- New Considerations when Partitioning
- Create Tablespace - DSSIZE Parameter (Recap)
- Altering DSSIZE (Recap)
- Data Set Names
- Table-Controlled Partitioning
- Table-Controlled Partitioning Example
- Altering a Table to Add Table Partitioning
- Converting to Table-controlled Partitioning
- Table-controlled Partitioning Catalog Changes
- Index-controlled Partitioning Terminology
- Table-controlled Partitioning Terminology
- Index classification
- Partitioned / Non-Partitioned Example
- Partitioned / Partitioning Example
- Clustering
- Clustering Within Partition
- Changing the Clustering Sequence
- Data Partitioned Secondary Indexes
- Creating a DPSI
- Design Considerations - Why Partition At All?
- Design Considerations - Non-Partitioned Index Problems
- Design Considerations - DPSI Benefits
- Design Considerations - DPSI Problems
- DPSIs and Utilities
- DPSIs and Planning
- Partition Management
- Adding Partitions
- Adding Partitions - Considerations
- Index-controlled to Table-controlled partitioning
- Rotating Partitions
- Rotate Partition Syntax
- Rotating Partitions - Considerations
- Altering Partition Boundaries
- Rebalancing Partitions using Reorg
- Rebalancing Partitions - Considerations
- Considerations for User Applications
- Display Database Command - Increased Partition Support
- Display Database Examples
- AVAILABILITY ENHANCEMENTS
- Availability enhancements
- Online Schema Alterations
- DB2 Availability Prior to V8
- Online Schema Maintenance
- Table Data Type changes
- New Syntax for Alter Table
- Alter Data Type - Impact Upon Table
- Alter Data Type - Impact Upon Indexes
- Alter Data Type - Index Availability
- Alter Data Type - Impact Upon Views / Check Constraints
- Alter Data Type - Considerations
- Alter Data Type - Restrictions
- New Syntax for Alter Index
- Alter Index Add Column - Restrictions
- Dropping Partitioning Indexes
- Deferred Index Creation
- Rebuild Pending - Index Avoidance
- Versioning
- Version Generating Alter Statements
- Version Limits
- How Does Versioning Work?
- Finding Version Information
- Reducing the Number of Active Versions
- Versioning and Recovery
- Object Schema Change Considerations
- Summary of Database State Changes
- TableSpace and Index Sizing
- Online ZPARM Changes
- SQL ENHANCEMENTS
- Long Names
- SQL Identifier Length Limits
- SQL Statements 2MB long
- Select from Insert
- Select from Insert Example
- Result Table Rows from the Insert Statement
- Select From Insert in a Cursor
- Select From Insert - New Order By Option
- Select From Insert - Error Processing
- Select From Insert - Using Cursors With Hold
- Select From Insert - Using Savepoints
- Updates and Deletes against Result Table Rows
- Select From Insert - Considerations
- Expressions / Functions in Group By
- Qualified Column names in Insert and Update
- Is Not Distinct From
- Hidden Rowid Generation
- Multiple Distinct
- Scalar Fullselect
- Scalar Fullselect Examples
- Scalar Fullselect Restrictions
- Common Table Expressions
- Writing a Common Table Expression
- Common Table Expression Example
- Common Table Expression Considerations
- Recursive SQL
- Recursive SQL Example
- Recursive SQL - Controlling Depth of Recursion
- Read Only Using Update Locks
- Session Variables
- New Special Registrers
- PROGRAMMING ENHANCEMENTS
- Dynamic Scrollable Cursors
- Insensitive Cursor - Recap
- Sensitive Static Cursor with Insensitive Fetch - Recap
- Sensitive Static Cursor with Sensitive Fetch - Recap
- Fetching from a Scrollable Cursor - Recap
- Sensitive Fetches - Update and Delete Holes
- Sensitive Fetches - Updated Data
- Sensitive Static Cursor with Sensitive Fetch
- New Cursor Syntax
- Sensitive Dynamic and Asensitive Cursors
- Sensitive Cursors - Fetching Rows
- Scrollable Cursor Considerations
- Multi-row Fetch and Insert
- New Syntax for Declare Cursor
- Fetching Rowsets
- Fetch Examples
- Host Variable Arrays
- Catering for Update and Delete Holes
- Partial Rowsets
- Rowsets - SQLCA
- Locking Rowsets
- Fetch First ‘n’ Rows
- Positioned Update
- Positioned Delete
- Multi-row Insert
- Multi-row Insert Syntax
- Static & Dynamic Insert
- Get Diagnostics
- Get Diagnostics - Statement Information
- Get Diagnostics - Condition Information
- Get Diagnostics - Connection Information
- Get Diagnostics - Examples
- Diagnostic Information for Multi-Row Fetch
- Get Diagnostics Fetch Example
- Diagnostic Information for Multi-Row Insert
- Get Diagnostics Insert Example
- Stored Procedure / Function Enhancements
- Handling Failed Procedures / Functions
- Procedure / Function Command Enhancements
- Workload Manager Enhancements
- Stored Procedure Deprecations
- Sql Procedures Language - New Statements
- SQLPL - RETURN Statement
- SQLPL - GET DIAGNOSTICS Statement
- SQLPL - ITERATE Statement
- SQLPL - SIGNAL Statement and Message_Text Variable
- SQLPL - RESIGNAL Statement
- A New Development Center
- Debugging Stored Procedures
- MQSeries UDFs
- Set Current Schema
- Set Current Package Path
- IDENTITY COLUMNS AND SEQUENCES
- Identity Column Review
- Identity Column Enhancements
- Altering Identity Columns
- Identity Columns - New Parameters
- Identity Columns - Data Sharing Implications
- Using Identity Columns with the Load Utility
- Catalog Table Changes
- Sequences
- Create Sequence Syntax
- Sequence Ordering
- Altering Sequences
- Dropping Sequences
- Sequence Authorities
- Using Sequences in Applications
- Sequences - Considerations and Restrictions
- Sequence Application Examples
- Consumed Values / Gaps in a Sequence
- Duplicate Sequence Values
- Sequence Cycle Considerations
- Defining a Constant Sequence
- Cache Considerations
- Sequences and Identity Columns Comparison
- MATERIALIZED QUERY TABLES
- What Are Materialized Query Tables?
- MQT Features
- Creating an MQT
- Create MQT Example
- Altering an MQT
- Alter MQT Example
- MQT Fullselect Features / Restrictions
- Refresh Table
- Populating User Maintained MQTs
- Automatic Query Rewrite using MQT
- Enabling Automatic Query Rewrite
- Enabling Automatic Query Rewrite - DDL Options
- Enabling Automatic Query Rewrite - Special Registers
- AQR - Using Both Registers
- Enabling Automatic Query Rewrite - System Properties
- Enabling Automatic Query Rewrite - Query Properties
- AQR Examples
- Determining if Query Rewrite Occurred
- MQTs and Referential Integrity
- MQTs and RI - Informational Constraints
- APPLICATION PERFORMANCE
- Variable Length Index Keys
- Variable Length Index Keys - Performance
- Altering Index Padding
- Index Key Length
- Backward Index Scan
- Indexable and Stage 1 Predicates
- Comparing Unlike Data Types
- Mismatched Numeric Comparisons
- Mismatched Character Comparisons
- Table UDF Cardinality
- Table UDF Materialized Fetch
- Trigger Enhancements
- Cost Based Parallel Sort
- Volatile Tables
- Star Join Recap
- Star Join Example
- Star Join Enhancements
- Run Time Reoptimization Enhancement
- Explaining the Statement Cache
- Declared Temporary Table Enhancement
- Lock Avoidance Enhancement
- SQL Non-Correlated Exists - Enhancement
- SQL In List Processing - Enhancement
- SQL In List Processing - Dynamic Prefetch
- New Plan Table Columns
- Plan Table Column Definitions
- VISUAL EXPLAIN
- Visual Explain Overview
- Visual Explain Pre-Requisites
- Connecting to a Subsystem
- Enabling Visual Explain
- Tuning Plans and Packages
- Tuning Ad-Hoc SQL
- Tuning the Dynamic Statement Cache
- E-BUSINESS ENHANCEMENTS
- E-Business Enhancements
- Java JCBC Driver Recap
- Database Environments
- A New DB2 Universal Driver
- DB2 Universal Driver - Additional Functionality
- XML Enhancements
- XML Publishing Functions
- XML Publishing 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
- Unicode
- Unicode History
- Character Conversion Terminology
- Code Page Differences
- Character Conversion Implementation
- How Unicode Works
- UTF-8 Characters
- UTF-16 Characters
- DB2 Version 8 and Unicode
- Specifying The Unicode CCSID
- Unicode Support
- Unicode - Impact on Applications
- Collating Sequence - Order By
- The Unicode Precompiler and SQL Parser
- Cobol and PL/I Conversion Issues
- Multilple CCSIDs in a Single Statement
- Unicode - Further Information
- UTILITY / COMMAND ENHANCEMENTS
- System level point-in-time recovery
- Delimited Load and Unload
- Runstats Enhancements
- New defaults Sortdata and Sortkeys
- Automatic Utility Restart
- Reorg Rebalance
- On-line Reorg for Catalog
- Copy with Systempages
- Repair and Areo
- Repair Versions
- Dsn1copy and System Pages
- Dpsi Support
- Other Utiltiy Changes
- ALTER BUFFERPOOL enhancement - PGFIX
| - DATA SHARING ENHANCEMENTS
- Data Sharing Review
- Coupling Facility Review
- Locking Review
- Hierarchical Locking V7 Review
- Version 8 Global L-Lock Enhancement
- Child L-Lock Propagation Enhancement
- Group Bufferpool Review
- Group Bufferpool Castout Review
- Version 8 Castout Enhancement
- Improved LPL Recovery
- Batched Updates For Index Page Splits
- Resolution Of Indoubt URs in Restart Light
- SECURITY ENHANCEMENTS
- Multilevel Security - Background
- Defining RACF Security Labels
- DB2 Multilevel Security - Seclabel Definition
- Seclabel Behaviour
- Seclabel Behaviour with Sql
- Seclabel Behaviour with Utilities
- DB2 Multilevel Security at Object Level
- MLS Considerations and Restrictions
- Using Session Variables for Security
- Special Registers for User Identification
- Encrypting Data
- 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
|
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.
| 
