- Tel: +44 (0)1275 859666
DB19 - DB2 Advanced Programming - 2 Days
Course Description
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
- LARGE OBJECTS
- Large Objects (LOBs)
- LOB Definition and Manipulation
- Base Table Definition
- LOB Tablespace and Auxiliary Table Requirements
- LOB Tablespace Definition
- Auxiliary Table Definition
- Auxiliary Table - Index Definition
- Loading the LOB Data
- LOB Data Options
- LOB Data Manipulation
- LOB Programming Issues
- Declaring LOB Variables
- Example using LOB Variables
- LOB Manipulation
- Data Spaces for LOB manipulation
- Using LOB Locators
- LOB Locator Considerations
- Example using LOB Locators
- LOB Limitations
- 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
- LOB Definition and Manipulation
- RECENT SQL ENHANCEMENTS
- 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
- The Merge Statement
- Merge Statement Restrictions
- Explaining Merge Statements
- Merge Statement Authorisation
- Select From Merge / Update / Delete Statements
- Select from Insert
- 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
- Using Truncate With Multi-Level Security
- Union, Intersect and Except
- Bigint - Data Type
- Binary / Varbinary - Data Types
- Decfloat - Data Type
- Writing a Common Table Expression
- PROGRAMMING ENHANCEMENTS
- Scrollable Cursors
- Insensitive Cursor
- Sensitive Static Cursor with Insensitive Fetch
- Sensitive Static Cursor with Sensitive Fetch
- Fetching from a Scrollable Cursor
- Sensitive Fetches - Update and Delete Holes
- Sensitive Fetches - Updated Data
- Sensitive Static Cursor with Sensitive Fetch
- 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
- Insensitive Cursor
- APPLICATION PERFORMANCE ENHANCEMENTS
- 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
- Optimistic Locking Example
- 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
- Identity Column Enhancements
- 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
- MQT Features
- TEMPORAL TABLES
- 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 and Versioning