DB09 - DB2 SQL Workshop for Experienced Users - 3 Days

Course Description

This training course teaches the delegate how to write advanced SQL statements including many new features introduced in recent DB2 releases.

Pre-requisites

A working knowledge of the DB2 host environment is advantageous but not essential.

The delegate should be able to code basic to intermediate SQL statements - these skills can be acquired by attending our SQL Workshop.

Objectives

The aim of this course is to provide the delegate with the necessary skills to write advanced SQL queries using DB2 for z/OS.

On completion of this course the student will be able to:

  • create Tables, Temporary Tables, Views, Aliases and Indexes
  • use Advanced SQL Grouping Functions
  • use Advanced SQL Scalar Functions and the CASE statement
  • code various types of JOIN
  • code UNION, INTERSECT and EXCEPT statements
  • code complex subqueries
  • use Referential Integrity, Check Constraints and Triggers
  • code Common Table Expressions
  • code recursive SQL statements
  • use Business and System Temporal Tables
  • define User Defined Functions
  • invertigage common SQL performance issues with the EXPLAIN statement

Environment

The course applies to DB2 running in a z/OS or LUW environment.

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