Know Db2

Db2 11 for z/OS -
SQL and Application Enhancements

Db2 11 for z/OS SQL and Application enhancements are discussed in detail for migration planning.

Topics Covered:

Duration: 2 Days, Lecture, Hands-on remote labs

Labs: Using TSO, ISPF, SPUFI

Audience: Anyone who will be coding SQL queries or using Db2 11 enhancements that are concerned with optimum performance; including end-users, programmers, application designers and database administrators.

Prerequisites: Working knowledge of basic SQL, TSO, and SPUFI.

Db2 11 for z/OS - SQL and Application Enhancements

Class Outline:

Plan Management

  • RELEASE(DEALLOCATE)
  • PKGREL_COMMIT
  • Predicate Indexability
  • APREUSE(ERROR) in Db2 10
  • APREUSE(WARN) in Db2 11
  • APPLCOMPAT
  • Predicate Rewrite – Stage 2
  • OR/IN and OR COL IS NULL
  • CASE Predicates
  • Stage 2 Predicates/Push Down Method
  • Value BETWEEN
  • Merging of predicates with views
  • Invariant Expressions
  • Correlated to Non-Correlated/Rewrite
  • Query rewrite for predicate Indexability
  • Select List Non-Column Expressions
  • Sort Performance
  • LIKE – Insignificant blanks
  • DGTT avoiding incremental binds/No Log
  • Workshop
  •  

Optimizer - Enhancements

  • Missing statistics/Collection
  • Statistics collection and feedback
  • SYSIBM.SYSSTATFEEDBACK
  • Virtual Indexes
  • RTS Statistics Externalization
  • Filter factor hints
  • Override predicate selectivity
  • Archive Transparency
  • Temporal Auditing
  • Temporal Support on Views
  • Temporal Special Registers
  • Workshop
  •  

Performance Enhancements

  • Empty PBG Partitions
  • DROP column
  • DDF Threads New Options
  • CANCEL THREAD FORCE
  • No Log Declared Temps
  • Partition Limit Key
  • Improvements Large Partitions
  • Sort work files
  • RIDs and Workfile Usage
  •  

Performance Enhancements (cont.)

  • RID overflow to work file usage for set functions
  • Table space scans
  • Number of Open Datasets
  • XML Improvements
  • ACCESS DATABASE command performance
  • ROLLBACK TO SAVEPOINT performance
  • Workshop
  •  

Index Entry Cleanup

  • Automatic Pseudo Delete Cleanup
  • DSNZPARM support
  • CANCEL THREAD FORCE
  • zIIP eligibility
  • Suppress-null indexes
  • Workshop
  •  

NULL & Indexes

  • Reduction of Indirect References
  • EXCLUDE NULL KEYS
  • PCTFREE FOR UPDATE
  • PCTFREE_UPD
  • Duplicate Removal
  • Index Skipping
  • DISTINCT – Early Out
  • Using CACHE
  • Data decompression
  • DPSIs Enhancements/Merge
  • DPSI page range screening join/correlation predicates and parallelism
  • Partition Elimination on Joins
  • Workshop
  •  

In-Memory Enhancements

  • Sparse Index enhancements
  • DSNZPARM control
  • Memory management
  • In-memory usage/caching
  • Get Page Classification
  • Random vs. Sequential
  • Sort Limit Support
  • IFCIDs to trace
  • Workshop
  •  

Class Summary

  • ©2017 Computer Business International, Inc.
  •  

Would you like to schedule or customize this class?
Please call our toll free number (866-224-4968) or email us at info@cbi4you.com.