DB09 - DB2 SQL Workshop for Experienced Users - 3 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

DATABASE AND TABLESPACE OVERVIEW
The Structure Of DB2 Object
Definition Of DB2 Objects - Ddl
Database Definition
Tablespace / Page Organisation
Page Sizes
Tablespace Layout
Types Of Tablespace
Tablespace Definition

CREATING TABLES
Table Definition
Table Names
DB2 Column Types
Null Values
User Defined Default Values
Copying Table Definitions
Rename Table
Data Partitioning
Hash Tables
Tablespaces For Hash Tables
Implicitly Hidden Columns
Row Change Timestamps
Row Change Timestamp Insertion
Variable Timestamp Precision
Variable Timestamp Precision - Current Timestamp

CREATING VIEWS AND ALIASES
DB2 Views
Read Only Views
Views - With Check Option
Creating A View Of Two Tables
DB2 Alias
The Alter Statements

CREATING INDEXES
DB2 Indexes
Index Organisation - The B Tree Index
Index Clustering
Non-Unique Indexes
Index Definition
Partitioning Indexes
Index Design Considerations
The Drop Statement

ADVANCED SQL FEATURES
Group By Clause
Expressions / Functions In Group By
New Group By Features
Group By Grouping Sets
Group By Rollup
The Grouping Function
Group By Cube
Advanced Scalar Functions
The Case Statement

TABLE JOINING TECHNIQUES
Table Joins
Inner Joins
Outer Joins
Joining More Than 2 Tables
Joining a table to itself

UNION, EXCEPT AND INTERSECT STATEMENTS
Union
Union, Intersect And Except
Intersect And Except
Intersect And Except Examples
Time For An Exercise!

WRITING SUBQUERIES
Subqueries
Subqueries Using In
Exists
The 'All' Subquery
The 'Any' Or 'Some' Subquery

WRITING COMMON TABLE EXPRESSIONS & RECURSIVE SQL
Common Table Expressions
Common Table Expression Example
Recursive Sql
Recursive Sql Example
Recursive Sql - Controlling Depth Of Recursion

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

TRIGGERS
Triggers
Trigger Parts
Before And After Triggers
Trigger Examples
Allowable Combinations
Error Handling
Trigger Cascading
Ordering Of Multiple Triggers
Trigger Authorisation
Catalog Information For Triggers
Instead Of Triggers
Instead Of Triggers - Restrictions
Instead Of Triggers - Authorisation
Instead Of Triggers - Catalog Changes
Removing Triggers

REFERENTIAL INTEGRITY
What Is Referential Integrity?
Parent And Dependent Tables
The Primary Key
The Foreign Key
Referential Constraint Rules
Constraint Names
More Complex Referential Structures
Check Pending Status
Resetting Check Pending Status
Referential Integrity Access
Design Considerations

IDENTITY COLUMNS AND SEQUENCES
Identity Columns
Identity Columns - Examples
Altering Identity Columns
Altering Identity Columns - Parameters
Identity Columns - Retrieving The Generated Number
Identity Columns - Data Sharing Implications
Using Identity Columns With The Load Utility
Sequences
Create Sequence Syntax
Sequence Ordering
Altering Sequences
Dropping Sequences
Using Sequences In Applications
Sequences - Considerations And Restrictions
Sequence Application Examples
Sequences And Identity Columns Comparison

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 - Catalog Information

USER DEFINED FUNCTIONS
User-Defined Functions
Built-In Functions
The Create Function Statement
Function Parameters
Sourced Function Examples
External Scalar Function Examples
External Table Function Example
Implementing An External Function
Writing An External Function
Function Example - Cobol
Function Example Using Scratchpad - Cobol
Function Example - Pl/1
Writing Functions In Java
Inline And Non-Inline Sql/Pl Scalar Functions
Sql/Pl Scalar Functions - New Control Statements
Sql/Pl - Non Inline Function Examples
Native Sql Functions - Versioning
Enhanced Sql Table Function Support

PERFORMANCE AND TUNING
The Explain Tool
The Explain Statement
Plan Table Layout
Plan Table Column Definitions
Predicate Evaluation
Indexable Predicates
Stage 1 And Stage 2 Predicates
Predicate Evaluation Sequence
Indexable Stage1 Predicates


Course Enquiry