DB2 V8 Transition

Course

In Edinburgh

£ 800 + VAT

Description

  • Type

    Course

  • Location

    Edinburgh (Scotland)

  • Duration

    2 Days

Suitable for: This Course is aimed at Systems Programmers, Programmers and DBA's who need to understand the new features and facilities of DB2 Version 8. This lecture based Course covers all of the new features in detail.

Facilities

Location

Start date

Edinburgh (Midlothian/Edinburghshire)
16 St. Mary'S Street, EH1 1SU

Start date

On request

About this course

A strong working knowledge of DB2 Version 7 is required to attend this Course.

Questions & Answers

Add your question

Our advisors and other users will be able to reply to you

Who would you like to address this question to?

Fill in your details to get a reply

We will only publish your name and question

Reviews

Course programme

Course Objectives
Course Details
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
ALTER BUFFERPOOL - PGFIX
Other Utiltiy Changes
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
Course Environment


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.

DB2 V8 Transition

£ 800 + VAT