Home About Courses Schedule Services Webinars Contact Search

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


Duration: 5.0 days

Method: Instructor Led, Hands-on Workshops

Price: $3200.00

Course Code: DB1070

View Related Video


Developers and Database Administrators using Db2 for z/OS


Get ready to tackle a variety of advanced Db2 topics in this action packed course. You will take your SQL coding skills to the next level and learn how to use native SQL PL stored procedures to effectively deliver SQL results to a variety of platforms. All workshops will be done using IBM's no-charge Data Studio platform. Query tuning with IBM Data Studio will also be explored.


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

  • Upon successful completion of this course, the student will be able to:
  • Code queries using complex joins, subqueries and table expressions
  • Navigate the IBM Data Studio Environment
  • Use the SQL PL procedural language
  • Deploy, test and debug Native SQL PL Stored Procedures
  • Use the query tuning features of IBM Data Studio


A working knowledge of Db2 for z/OS


  1. Complex Joins
    • Inner Joins
    • LEFT and RIGHT Outer Joins
    • Full Outer Joins
    • “Anti” Joins
    • Self Joins
  2. Multiple Query Blocks
    • Non-correlated Subqueries
    • Correlated Subqueries
    • Scalar Fullselects
  3. Table Expressions and Views
    • Views
    • Nested Table Expressions
    • Common Table Expressions
  4. IBM Data Studio
    • Introduction to Data Studio
    • Managing Database Connections
    • Coding Stored Procedures
    • Deploying Stored Procedures
    • Importing Existing Stored Procedures
  5. 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
  6. 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
  7. Error Handling in SQL PL
    • Capturing SQLCODE and SQLSTATE
    • Unhandled Exception Behavior
    • Coding Exception Handlers
    • SIGNAL & RESIGNAL Commands
  8. Global Temporary Tables
    • Created Global Temporary Tables
    • Declared Global Temporary Tables
    • Example of a Stored Procedure Using Declared Global Temporary Table
  9. Native SQL Logistical & Performance Considerations
    • Naming Conventions
    • Versioning Procedures
    • Performance and Execution Considerations
    • Using Explain
    • Migrating External to Native
  10. Unified Debugger in IBM Data Studio
    • Invoking the Debugger
    • The Debug Perspective
    • Using Breakpoints
    • Working with Variables & Values
  11. 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