We are a family owned USA based Corporate Training Company determined to help professionals, teams, and organizations improve.

Let us help you find the training program you are looking for.

If you can't find what you are looking for, contact us, we'll help you find it. We have over 800 training programs to choose from.

banner-img
Home » Database » Db2 for z/OS Advanced SQL Coding and Performance Tuning

Course Skill Level:

Advanced

Course Duration:

4 day/s

  • Course Delivery Format:

    Live, instructor-led.

  • Course Category:

    Database

  • Course Code:

    DB2SQLN22J02

Who should attend & recommended skills:

Those with basic relational data model experience seek to code SQL queries

Who should attend & recommended skills

  • Those who will be coding SQL queries and are concerned with optimum performance; including end-users, programmers, application designers and database administrators
  • Relational data model: Basic (1-2 years’ experience)

About this course

This hands-on workshop trains the Db2 Query User, Application Developer and DBA to code complex SQL statements to achieve optimum performance.

Skills acquired & topics covered

  • Db2 Concepts and Facilities Review
  • Tuning Complex Single Table SQL
  • Tuning and Working with Multiple Tables
  • Advanced SQL Processing Topics
  • Advanced Performance Tuning and EXPLAIN

Course breakdown / modules

  • Db2 Environment-How it works
  • Data Structures To Know About
  • SQL Impact on the System

  • SELECT Topics to Know
  • WHERE/ORDER BY/Result Sets
  • Functions, Expressions, Special Registers
  • Review and Workshop – EXPLAIN

  • What is the Optimizer?
  • Predicates-Indexable, Stage 1 and Stage 2
  • Access Paths and How they work
  • Matching Indexes to Predicates
  • Comprehensive Statistics History
  • Db2 Catalog and Distribution Statistics
  • PLAN_TABLE Changes
  • EXPLAIN – Advanced
  • Tuning Workshop

  • Joining Data from multiple Tables
  • Inner / Full Outer Join
  • Left Outer Join / Right Outer Join
  • Using multiple join types in a Query
  • Nested Table Expressions
  • Using Global Temporary Tables
  • Tuning Workshop – EXPLAIN

  • Complex Access Paths
  • Nested Loop Joins
  • Merge Scan Joins
  • Hybrid Joins
  • Intermediate Tables – DSNDB07
  • EXPLAIN – Complex
  • Tuning Workshop

  • Column Functions and Group By Expressions
  • Correlated/Non-Correlated subqueries
  • Scalar Functions and Case Expressions
  • Statement Cost Comparisons
  • Materialized Query Tables (MQT)
  • How the Optimizer uses MQT’s
  • Query Performance Enhancements
  • Avoiding SORTS
  • Predicates for Performance
  • New Stage 1 and Indexable Predicates
  • Mismatched String Types
  • EXPLAIN – Tuning Workshop

  • Influencing Access Paths
  • Partitioned Table spaces
  • Recursive SQL
  • Static verses Dynamic SQL Processing
  • Parallel Operations
  • Enabling and Limiting Parallelism
  • Optimizer Hints – Access Paths
  • Filter Factors
  • Influencing the Access Path Optimizer
  • Dynamic SQL Governor
  • Insert within Select
  • Dynamic Scrollable Cursors
  • Cursor Comparisons
  • Multi – row Fetch with Scrollable Cursors
  • Comparison to Static Scrollable Cursors
  • GET DIAGNOSTICS
  • Tuning Workshop – EXPLAIN

  • Update with Subselect
  • Using Savepoints
  • Using Global Temporary Tables
  • Cursor Processing – Advanced Techniques
  • Scrolling / OPTIMIZE FOR
  • Existence Checking
  • Stored Procedures Changes
  • Coding Techniques
  • Volatile Tables and SQL
  • Transparent Rowid
  • EXPLAIN – Tuning Workshop

  • Aspects of Locking
  • BIND and REBIND Options and Impact
  • Coding Techniques Cheat Sheet
  • New SQL Codes and Warnings
  • New Reserved Words
  • Tuning Workshop – EXPLAIN

  • NOTE:Each attendee should bring one or more complex queries to class for a case study.

Browse our programs to take the next step toward advancing yourself, your team, and organization.

Browse Courses