Description

This course focuses on best practices for proper design, coding and maintenance techniques with the primary objective of improving DB2 performance. Students will learn specific factors which impact performance and how they can proactively address performance issues in their SQL and application programs. Students will learn the features of the EXPLAIN function, how it is used for optimization and how optimization hints can change the optimizer's decisions. Participants will understand the purpose and function of the PLAN_TABLE. This course includes features up to and including version 10 of DB2 for z/OS.

Audience

This course is designed for application programmers and programmer/analysts who will be using SQL statements in a high level programming language to manipulate DB2 tables.

Topics

Application Tuning
SQL Statements That Can Affect Performance
EXPLAIN Function
Simple and Complex Access Paths
Index Structure, Design, and Usage
Db2 Buffer Pools and Their Impact on Performance

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

DB2 Advanced SQL with Performance and Tuning For Programmers Virtual Classroom Live April 28, 2025

Price: $2,100

Enroll today to reserve your spot!

Description

Course Overview

Prerequisites

Other Available Dates for this Course

Find a Course

Corporate Services

Contact us