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

SEE SCHEDULE

Duration: 5 Days

Method: Instructor Led, Hands-on Workshops

Price: $3200.00

Course Code: DB1070

Audience

Developers and Database Administrators using Db2 for z/OS

Description

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!

Objectives

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

Prerequisites

A working knowledge of Db2 for z/OS

Topics

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
- GET DIAGNOSTICS
- 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