Db2 12 for z/OS SQL Performance and Tuning

• Introduction to SQL performance and tuning
• Analysis tools: VQUBE3, EXPLAIN, accounting trace, bubble chart, performance thresholds
• Index fundamentals and structure; I/O estimation; clustering index
• Access paths: matching vs. screening, hash access, prefetch
• Advanced indexes: include, expression, random, NPSI/DPSI partitioning
• Tuning methodology, index cost and index design
• Prefetch list, multiple index access, adaptive indexing at runtime
• Multiple table access: join methods, table order prediction
• Correlated and non-correlated subqueries; global query optimization
• UNION / EXCEPT / INTERSECT
• Table design, MQTs, temporal tables and archive-enabled tables
• Working with the optimizer: indexable predicates, stage 1 vs 2, filter factors
• Locking: ACID, transaction locking, promotion, escalation and avoidance

• Day 1: introduction to tuning, analysis tools, index fundamentals
• Day 2: access paths, advanced joins, subqueries, set operators
• Day 3: table design, optimizer and locking

• Delivered directly by IBM Champion instructors from the SIXE team.
• Credly badges available on completion: credly.com/organizations/sixe/badges
• Flexible format: online, in-person or on-site.
• Private groups with per-student pricing. Delivery language of your choice (ES / EN / FR).

• Real project examples: OLTP with locking diagnostics, MQT tuning, resolving degradation after v12 upgrade.
• Bridge to Db2 technical support for Power and x86 after the course.

• COBOL or PL/I developers seeing performance degradation after migrating to Db2 12.
• Db2 z/OS DBAs reviewing OLTP workloads with locking issues or suboptimal access paths.
• Teams introducing analytical queries (recursive CTE, MQTs, OLAP functions) on Db2.

Do I need to take CV844G first?
SQL knowledge and Db2 12 z/OS familiarity are assumed. If the team hasn’t worked with Db2 z/OS, start with CV844G or CE131G.

Does it cover LUW too?
No. This course is z/OS-specific. For LUW tuning, see CL443G.

Group size?
Private groups of 4 to 10.

• CV844G — Db2 12 z/OS Basic DBA (base if the role is administration)
• CV853G — Db2 12 for z/OS Introduction to System Administration (when the problem is systemic, not SQL)
• CE131G — Advanced SQL for Db2 (prior SQL foundation)