home 
 
 
 
nlfr
 
Home
About ABIS
Services
Courses
Resources
Contact
MyABIS
All CoursesbalkjeGeneral » Introduction to HW & SW » TracksOperating systems » MVS - z/OS » UNIX - Linux - AIX » Windows » VMwareDatabases, transaction systems and middleware » DB2 for z/OS » DB2 UDB for Linux, UNIX and Windows » Oracle » SQL Server » MySQL » IMS » CICS » WebSphere MQ » WebSphere Application Server » Business intelligence and data warehouseApplication development » Methods and techniques » TOGAF » PRINCE2 » Programming languages » Internet development » Object Oriented systems » Java » IBM development tools » SAS » XML » SOA & web servicesSystems management » ITILbalkjePractical informationRegistration 
Dynamic SQL and the Dynamic Statement Cache

Intended for

DB2 administrators and application designers who want to monitor and/or optimise dynamic SQL queries.

Background

Sufficient knowledge of DB2 performance aspects (see courses DB2 for z/OS SQL performance and DB2 for z/OS database administration).

Course leader

Peter Vanroose.

Duration

1 evening (2 hours).

Schedule

No public sessions are currently scheduled. We will be pleased to set up an on-site course or to schedule an extra public session (in case of a sufficient number of candidates). Interested ? Please contact ABIS.

Objectives

This evening session is one in a series of six where we will bring you up-to-date on some selected topics related to DB2 for z/OS. The individual sessions can be followed independently, or you may sign up for the full series.

In this session you will learn how DB2 v8 for z/OS allows you to obtain information (e.g., EXPLAIN) on dynamic SQL queries, i.e., queries that were not statically bound and hence for which no "classical" bind-time performance information is available. More specifically, you will learn how to optimally configure and inspect the dynamic statement cache.

Main topics

  • The growing importance of dynamic SQL; pros and cons; performance implications
  • Gradations of dynamicity: parameter markers; prepare and execute; BIND options REOPT and KEEPDYNAMIC
  • Runtime control of dynamic SQL
  • Configuring and controlling the dynamic statement cache; size; castout algorithm; relevant zparms
  • EXPLAIN STMTCACHE: a worked-out example
  • monitoring, tuning and improving the dynamic SQL queries on your system