Reducing CPU and Maximizing Db2 z/OS Application/SQL Performance

Susan Lawson, YL&A Inc. - Wednesday 15 May - Session 2
 

Coding for optimal performance is an art. It is not only optimal SQL. Many other things like cursor techniques and binding are important. Many techniques to minimize overhead and locking will be explored in this eminar. We will also take a look at techniques coding efficient SQL and minimizing the amount of data we bring back to process in our applications via optimal predicates, less repetition and better use of indexes. All features are current with DB2 11 and 12.

Topics:

  • High performing applications overview
    • What makes an application perform well
    • What kills application performance
  • SQL for call reduction
    • SELECT from INSERT
    • SELECT from UPDATE/DELETE
    • Common table expressions
    • Recursion usage to reduce calls and joins
    • Order by and fetch first in subselect
    • Multi-row fetch
    • Multi-row insert
    • MERGE for replication
    • LISTAGG function
  • Keping the database calls in Db2
    • Using Db2 referential Integrity
    • Caching common code values
    • Using triggers
    • Basic vs advanced triggers
    • Non-deterministic expressions for auditing
    • Trigger performance trade offs
    • User Defined Functions
    • Stored procedures
  • Minimizing application overhead
    • Number of columns retrieved
    • Number of columns/rows sorted
    • Performance trace for sorting
  • SQL filtering, index exploitation and predicate evaluation
    • SQL performance objectives
    • Filtering
    • Predicate comparison
    • Predicate pushdown
    • Residual predicate processing
    • Index on expression
    • Indexable predicate conversion
    • Boolean term predicates
    • Join predicates
    • Function evaluation
    • CASE expressions
    • Row expressions
  • SQL choices for performance
    • Subquery performance issues
    • Non-correlated subquery performance
    • Correlated subquery performance
    • Merge vs materialization
    • Table expressions
    • UNION ALL join distribution
    • Searching on multiple conditions
  • Filter factors and access path influence
    • Filter factors
    • Distribution statistics
    • Histogram statistics
    • Statistics feedback
    • Filter factor influencing
    • Reoptimization
    • OPTIMIZE FOR n ROWS
  • Repeat processing efficiency/elimination
    • Random I/O causes and resolution
    • Excessive sequential processing
    • Excessive index screening
    • Programmatic joins conversion to SQL
    • Cursor in cursor conversion to SQL
    • Putting work in SQL
    • Redundant SQL issue/cost
    • Using MQTs to avoid repetitive processing
  • Explain and the Dynamic Statement Cache
    • What’s missing in Explain
    • Explain tables
    • PLAN_TABLE
    • DSNFILTERTABLE
    • DSNPREDICATETABLE
    • DSNDETCOSTTABLE
    • Advanced Explain queries
    • Dynamic statement cache
    • Coding to use statement cache
    • DSNSTATEMENTCACHE_TABLE
    • Analyzing the dynamic statement cache
    • Dynamic cache and literal replacement
  • Binds and Rebinds
    • DEGREE and parallelism usage
    • ISOLATION options for performance
    • REOPT impact and usage
    • RELEASE option for performance
    • DEFER distributed impact
    • Plan stability
    • APCOMPARE usage and interpretation
  • Locking and Concurrency
    • Cost of locking
    • Locking statistics to monitor
    • Locking reduction/avoidance
    • Row level locking
    • Deadlocks and timeouts
    • Lock wait time/escalation
    • Optimistic locking
    • Skip locked data
    • Currently committed data
    • Coding for best concurrency
  • Miscellaneous application performance topics
    • Next-key generation
    • Using sequence objects
    • Sequence objects for key propagation
    • Fetch first usage
    • Batch restart cost
    • Commit issues/detection
    • Using Savepoints

Platform: z/OS

logo

← Back to all sessions

About: Susan Lawson

photo

Susan Lawson is an internationally recognized consultant and lecturer with a background in system and database administration. She currently works with several large clients to help develop, implement and tune some of the worlds’ largest and most complex DB2 databases and applications. She also performs Performance and Availability Audits for many clients to help reduce costs through proper performance tuning and to help ensure availability. Her other activities include authoring articles and white papers, presenting at user group meetings, and teaching a variety of DB2 courses. She is an IBM GOLD Consultant for DB2 and z/Series, and an IBM Information Champion and has authored the IBM ‘DB2 for z/OS V8 DBA Certification Guide’ and the IBM ‘DB2 for z/OS V7 Application Programming Certification Guide’. She is also the co-author of several DB2 books including the IBM ‘DB2 10 for z/OS DBA Certification Guide’, IBM ‘DB2 9 for z/OS DBA Certification Guide’, ‘DB2 High Performance Design and Tuning’ and ‘DB2 Answers’. She is also a SME(Subject Matter Expert) for the IBM Certification exams for DB2 z/OS Database and System Administration assisting with writing and verifying questions for the exams.

logo