Home About Courses Schedule Services Webinars Contact Search

Advanced SQL, Native SQL Procedures & Query Tuning on Db2 for z/OS


Duration: 5 Days

Method: Instructor Led, Hands-on Workshops

Price: $3200.00

Course Code: DB1070


Developers and Database Administrators using Db2 for z/OS


Get ready to tackle a variety of Db2 topics that will take any developer or DBA to the next level. This class starts with SQL programming concentrating on joins, subqueries, and table expressions. Workshops will take place coding complex queries to drive the logic in these areas home. Workshops build in this area where you will eventually see 3,4,5 different ways to code and get the same results. Material is always taught with a slant on performance. Class continues learning the native SQL-PL language and how to code, deploy, and test stored procedures. Best practices in all areas of this language are covered. And finally learn what makes queries, programs, and applications run too long. Every developer and DBA should know how to read and analyze a Db2 explain, and the IBM Data Studio tool does a great job at visualizing the explain output. You will leave this class feeling more confident in many areas of Db2!


Upon successful completion of this course, the student will be able to:

  • Come away stronger in many areas of SQL programming
  • Be able to code queries using complex joins, subqueries and table expressions
  • Will understand different ways to code for the same result set
  • Be able to navigate the IBM Data Studio Environment
  • Learn the SQL PL procedural language
  • Learn how to code, deploy, test and debug Native SQL PL Stored Procedures
  • Understand optimization
  • Understand what makes queries run too long
  • Understand how to read and analyze Db2 explains using IBM Data Studio


A working knowledge of Db2 for z/OS


  • I. Complex Joins
    • Inner Joins
    • LEFT and RIGHT Outer Joins
    • Full Outer Joins
    • “Anti” Joins
    • Self Joins
  • II. Multiple Query Blocks
    • Non-correlated Subqueries
    • Correlated Subqueries
    • Scalar Fullselects
  • III. Table Expressions and Views
    • Views
    • Nested Table Expressions
    • Common Table Expressions
  • IV. IBM Data Studio
    • Introduction to Data Studio
    • Managing Database Connections
    • Coding Stored Procedures
    • Deploying Stored Procedures
    • Importing Existing Stored Procedures
  • V. Introduction to Stored Procedures
    • What are Stored Procedures?
    • Problems Solved Using Stored Procedures
    • Advantages of using Stored Procedures
    • Calling a Stored Procedure
    • Native and External SQL Procedures
    • Stored Procedure Run-time Environments
  • VI. The SQL Procedure Language
    • CREATE PROCEDURE statement
    • Db2 for z/OS BIND options
    • Declaring SQL Variables
    • Procedural Statements in SQ
    • Creating & Retrieving Result Sets
    • Calling another Stored Procedure
  • VII. Error Handling in SQL PL
    • Capturing SQLCODE and SQLSTATE
    • Unhandled Exception Behavior
    • Coding Exception Handlers
    • SIGNAL & RESIGNAL Commands
  • VIII. Global Temporary Tables
    • Created Global Temporary Tables
    • Declared Global Temporary Tables
    • Example of a Stored Procedure Using Declared Global Temporary Table
  • IX. Native SQL Logistical & Performance Considerations
    • Naming Conventions
    • Versioning Procedures
    • Performance and Execution Considerations
    • Using Explain
    • Migrating External to Native
  • X. Unified Debugger in IBM Data Studio
    • Invoking the Debugger
    • The Debug Perspective
    • Using Breakpoints
    • Working with Variables & Values
  • XI. Query Tuning with IBM Data Studio
    • Single Table Access
      • Tablespace Scan
      • Sequential Prefetch
      • Index Scans
      • Index Screening
      • List Prefetch
    • Multiple Table Access
      • Optimizing Inner and Outer Joins
      • Join method selection
      • Sorting
      • Avoiding sorts
      • Join order
    • Predicate Transitive Closure