Db2 for z/OS Advanced SQL with Performance and Tuning For Programmers Virtual Classroom Live September 22, 2025

Course Overview

I. DB2 Overview and Storage Concepts

• A. What is DB2?
• B. What is the History Behind DB2?
• C. What are DB2’s Objectives?
• D. What is a Relational DBMS?
• E. What are DB2’s Features?
• F. Operational Environment
• G. SQL
• H. Program Preparation Process
• I. Unit of Recovery
• J. Commit / Rollback
• K. DB2 Terminology
• L. Physical Hierarchy of DB2 Objects
• M. Naming Conventions
• N. Object Naming Conventions
• O. Databases
• P. DB2 and VSAM
• Q. Storage Groups
• R. Page Management
• S. Tablespace
• T. Segmented Tablespaces
• U. Partitioned Tables
• V. Base Tables
• W. View Table
• X. Synonym
• Y. Indexes
• Z. Stored Procedures and Functions
• AA. DB2 String Data Types
• BB. DB2 Numeric Data Types
• CC. Data and Time Data Types
• DD. Display Formats
• EE. ROWID Data Type Versus Identity Column Attribute
• FF. User-Defined Data Type
• GG. B2 Catalogs

II. Creating DB2 Objects using Data Definition Language

• A. Structured Query Language (SQL)
• B. DDL - Create Table Statement
• C. Identity Column
• D. Check Constraints
• E. Alter Table Statement
• F. Not NULL with Default
• G. Referential Integrity
• H. Synonyms
• I. Deleting DB2 Objects
• J. Index
• K. Unique Versus Non-Unique
• L. Cluster Versus Non-Cluster
• M. Cluster Index
• N. Index Create
• O. Partitioned Table
• P. Views
• Q. Creating Common Table Expressions

III. Referential Integrity

• A. Referential Integrity - Overview
• B. Delete Rules
• C. Insert and Update Implications
• D. Referential Integrity Summary
• E. DDL - Referential Integrity
• F. Primary Key Characteristics
• G. Foreign Key Characteristics
• H. Lab 1
• I. Data Model Lab
• J. Lab 1 - Loading Tables

IV. Advanced SQL - SELECT

• A. Join
• B. Inner Join
• C. Full Outer Join
• D. Left - Right Outer Join
• E. Joins of More Than 2 Tables
• F. User Request # 1
• G. Subquery
• H. Single Value Subquery
• I. Multivalued Subqueries
• J. Multivalued Subqueries - ALL
• K. Multivalued Subqueries - ANY or SOME
• L. Multiple Column Subqueries
• M. User request # 2
• N. Correlated Subqueries
• O. Correlated Subqueries - Exists
• P. Using Correlation Variables to Check R.I.
• Q. Nested table Expression
• R. Union
• S. Union All
• T. Rules for Union
• U. Performance Considerations
• V. INTERSECT
• W. EXCEPT
• X. User Request #3

V. Update Data Manipulation

• A. Insert
• B. Update
• C. SELECT FROM UPDATE
• D. Delete
• E. Truncate
• F. SELECT FROM Delete
• G. merge
• H. DB2 Valid SQL Return Codes for Updating

VI. Additional Advanced Topics

• A. Handling XML data
• B. Overview of MQTs (Materialized Query Tables)
• C. Overview of Recursive SQL
• D. Use of Dynamic SQL in Application Programs
• E. Creating Global Temporary Tables
• F. Use of Temporal Tables

VII. DB2 Buffers

• A. How Does It Work?
• B. Defining the Pools
• C. Virtual Buffer Pool Tuning
• D. Buffer Pool Size Terminology
• E. Buffer Pool Thresholds

Vlll. Application Coding Considerations

• A. Application Tuning Performance Tips
• B. Application Tuning – Explain Function and PLAN_TABLE
• C. Definitions and Frequently Asked Questions
• D. Tablespace Scans
• E. Index Access Paths
• F. Index Screening
• G. Nonmatching Index Scan
• H. IN-LIST Index Scan
• I. Backward Index Scan
• J. Multiple Index Access
• K. ONE-FETCH Access
• L. INDEX-ONLY Access
• M. Application Tuning - How Predicates Influence Query Performance
• N. General Rules About Predicate Evaluation
• O. Order of Evaluating Predicates
• P. JOIN Methods
• Q. Tuning Your Subqueries
• R. Application Tuning – Optimization Hints
• S. Application Tuning – Summary

IX. Lab- Tuning

X. Appendix – Overview of New Features for v10

Prerequisites

Before attending this course, students should have experience coding in one of the programming languages listed: COBOL, PL/I or C, TSO/ISPF and Db2 file structures and knowledge of basic SQL.