IBM Db2 12: SQL Workshop for Experienced Users (TDS_CLA32G)

Unit 1: SQL Introduction

• • Describe the structure and purpose of Db2 v12, including its system catalog, database organization, and key SQL components
• • Explain how Db2 catalogs and system tables store metadata, and how they can be queried for database object information
• • Demonstrate the use of OLAP ranking functions (RANK, DENSE_RANK, ROW_NUMBER) to analyze and order query results

Unit 2: Create Objects

• • Create and modify tables, columns, and constraints
• • Use generated columns to automate calculations and reduce dependency on triggers
• • Optimize queries with expression-based indexes for computed values
• • Implement referential integrity, check constraints, and triggers for enforcing business rules
• • Leverage views and materialized query tables to simplify data access and reporting

Unit 3: JOIN

• • Use inner and outer joins to retrieve related data across tables
• • Optimize query performance with hash joins for large datasets
• • Leverage lateral joins for more flexible row-by-row processing
• • Perform self-joins to analyze hierarchical or recursive relationships
• • Utilize set operators like UNION, EXCEPT, and INTERSECT for advanced data merging

Unit 4: CASE, CAST, Summary (Materialized Query) Tables, and Temporary Tables

• • Use CASE expressions to apply conditional logic within queries
• • Perform data type conversions using CAST
• • Leverage summary tables and MQTs to precompute and store aggregated data
• • Implement temporary tables and SGTTs to handle intermediate results

Unit 5: Using Subqueries

• • Use basic subqueries to filter and retrieve data dynamically
• • Leverage correlated subqueries for row-dependent queries
• • Apply EXISTS, NOT EXISTS, IN,  and NOT IN predicates for conditional data retrieval
• • Utilize LATERAL subqueries to execute row-by-row subqueries for sequential logic processing
• • Optimize query performance by structuring subqueries efficiently

Unit 6: Scalar Functions

• • Use string functions to extract, modify, and format text data
• • Perform numeric and mathematical operations, including logarithmic, exponential, and trigonometric calculations
• • Apply LISTAGG to concatenate multiple row values into a single string
• • Work with data and time functions to extract, manipulate, and format timestamps

Unit 7: Table Expressions and Recursive SQL

• • Use nested table expressions to create temporary result sets for complex queries
• • Leverage common table expressions (CTEs) for modular and reusable SQL structures
• • Implement recursive SQL to process hierarchical relationships
• • Apply performance optimization techniques to improve efficiency in recursive queries

Unit 8: An Introduction to UDTs / UDFs and Stored Procedures

• • Define User-Defined Distinct Types (UDTs) to create custom data types for specialized business needs
• • Implement User-Defined Functions (UDFs)
• • Utilize stored procedures to encapsulate complex database logic for performance efficiency
• • Leverage ARRAY data types to pass structured lists of values within SQL queries and procedures

Unit 9: SQL and Db2 Performance

• • Use indexes, clustering, and predicate pushdown
• • Analyze and optimize SQL execution plans with EXPLAIN and VISUAL EXPLAIN
• • Improve filtering and sorting performance using predicate processing techniques
• • Leverage RUNSTATS enhancements to ensure up-to-date statistics for query optimization