Target Audience
This course is aimed at DB2 Applications Programmers who need to understand the new programming and SQL features of DB2 Version 9.
Pre-requisites
A strong working programming knowledge of DB2 Version 8 is required to attend this course.
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 features.
Course Environment
The lectures apply to DB2 running in a z/OS environment.
Course Details
- 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
- 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
- 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
- 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
- AVAILABILITY AND CAPACITY ENHANCEMENTS - OVERVIEW
- Universal Tablespaces
- Universal Tablespace Benefits
- Maximum Number Of Partitions
- Partition-By-Growth Tablespace
- Creating A Partition-By-Growth Tablespace
- Partition-By-Range Tablespace
- Clone Tables - Fast Data Replacement
- Why Use Clone Tables?
- Column Renaming
- Column Renaming Restrictions
- Improved Insert Performance - The Append Parameter
- Index Renaming
Course Format
This course runs as a 1 day lecture.
|