BMC DB2 V8 Status

1
(No Transcript)
2
WelcomeAlisa Claypool, DTS

• -Slides will be available on DTS webpage soon
  http//www.dts.ca.gov/news_events/events.asp
• -Introducing new DB2 User Group webpage
• http//www.dts.ca.gov/news_events/user_groups/DB2.
  asp
• -Check the DTS Event Calendar for other Customer
  events
• http//www.dts.ca.gov/calendar/default.asp
• -Please welcome..
• Bill Arledge, BMC Software

3
Understanding Distributed Processing Inside
DB2 for z/OS

• Bill Arledge
• Consulting DB2 Product Manager

4
Overview

• Distributed Processing Fundamentals
• Distributed threads What can I see?
• WLM, enclaves and SRBs
• DDF and DB2 system considerations
• Impact on critical resources

5
Distributed FundamentalsBasic Concepts - Intro

• A distributed database is not entirely stored at
  a single physical location
• Distributed characteristics and value
• Location Transparency
• Autonomy for each location
• Improved Availability
• Scalability
• Technology Agnostic (goal)
• Extension to relational theory and technology
• Initial delivery with DB2 2.2 in late 1980s
• DB2 on mainframe only
• Introduced a load of new requirements for DBAs
  and application developers

6
Distributed FundamentalsBasic Concepts Units
of Work
7
Distributed FundamentalsA Tale of Two Protocols

• Private Protocol (PP)
• First delivered in DB2 2.2
• Uses 3 part names
• Select C1,C2,C3 from DB2G.APPL1.TABLE1
• Dynamic SQL only
• No remote bind
• DB2 on z/OS to DB2 on z/OS only
• No stored procedure support
• Functionally stabilized and on the way out

• DRDA (Distributed Relational Database
  Architecture)
• Introduced in DB2 2.3
• Supports 3-part names and explicit CONNECT
  statements
• Dynamic and Static SQL
• Remote bind capabilities
• Supports stored procedures
• Supports all RDBMS implemented using DRDA
  protocol
• Supports SNA and TCP/IP
• Is the strategic architecture for distributed

• Specifying the protocol
• Default can be set at subsystem level using
  DBPROTCL DSNZPARM value (DRDA or PRIVATE)
• With DB2 9.1 PRIVATE can no longer be specified
• Can also be specified in the DBPROTOCOL parm of
  the BIND statement (D or P)
• Only option in DB2 9

8
Distributed Fundamentals Connectivity Options
DRDA or PP
SAN JOSE
DRDA
DB2 Connect EE
DRDA
DRDA
DB2 Connect PE
IBM DB2 Driver for For JDBC and SQLJ
9
Basic Terminology Definitions

• Application Server (AS) / Application Requester
  (AR)
• DB2 for z/OS? DB2 Connect? Both!
• Location (DB2 for z/OS term)
• Or RDB-Name, VTAM nodes, TCP/IP partners
• Connection between a requester and a server
• TCP/IP ports, or VTAM LUNAMEs
• Either a client or a thread could have more than
  one
• Network protocol TCP/IP or SNA (VTAM)
• Conversation handle traffic on a connection
• Also referred to as a session
• DRDA one per requester to handle SQL open
  cursors
• Private protocol may have more, one per open
  cursor

10
Distributed FundamentalsSQL Access

• System Directed Access
• Three Part Names
• Alias
• Supported by DRDA and PP

SELECT FROM PRD1.RNDWDA.ORDER WHERE
QUANTITY BETWEEN 1 and 100 or... CREATE ALIAS
RNDWDA.AUSTIN_ORDERS FOR PRD1.RNDWDA.ORDER
SELECT FROM RNDWDA.AUSTIN_ORDERS

• Application Directed Access
• Explicit CONNECT by application
• Supported by DRDA only
• Remote BIND required

EXEC SQL CONNECT TO PRD1 SELECT FROM
RNDWDA.ORDER
EXEC SQL CONNECT TO PRD1 EXEC SQL CALL PROCONE

• Remote Stored Procedure Call
• Explicit CONNECT by application
• DRDA only

PROCEDURE PROCONE EXEC SQL SELECT..... EXEC SQL
UPDATE.....
11
Distributed Fundamentals DB2 on z/OS Distributed
Implementation
In the beginning
ALLIED AGENTS
ALLIED AGENTS
ALLIED AGENTS
DBM1
MSTR
IRLM
ALLIED AGENTS
Sign on and such
Locking
Most of the Action
Sign-0n More stuff

• In the beginning - 3 DB2 operational address
  spaces in the beginning
• Plus all the allied agent address spaces
• CICS, IMS, TSO Attach
• TSO Batch, Call Attach Facility
• Distributed Data Facility (DDF) in DB2 V2R2
• Access using 3 part names or aliases
• DIST address space introduced
• DRDA (Distributed Relational Database
  Architecture) first implemented in DB2 V2R3
• Major enhancements delivered in DB2 V4
• DRDA support of stored procedures
• DBAT user priority
• More in DB2 V5
• TCP/IP, ODBC, CLI, JDBC
• Much more . . .
• Web-based access comes of age
• Java, JDBC Universal Driver, Websphere . . .

12
DB2 on z/OS Distributed ImplementationA Word
About Threads
Allied Agents
Database Access Threads
Distributed Connections
DBM1
IMS/TM
Distributed Traffic
DIST
CICS
Batch
Allied Threads

• Database Access Threads (DBATs)
• Service distributed workloads
• Implemented as an MVS WLM enclave running in
  preemptive SRBs originating in the DIST address
  space (more coming)
• DBAT Types
• DBAT (Server)
• DBAT (Dist)

• Allied Threads
• Service local attachment facilities
• Run at the dispatching priority of the requesting
  application
• Can become distributed requesters
• Allied Agent Types
• Allied
• Allied Dist (requester)

13
Where are your DBAT Threads Coming From?

• Other DB2 for z/OS subsystems
• Primarily workstation clients or web users
• Many connection possibilities
• DB2 Connect PE
• IBM DB2 Driver for JDBC and SQLJ
• Recently renames from DB2 Universal Driver for
  JDBC and SQLJ
• Connection managers and concentrators to
  reduce resources required in DB2 for z/OS
• DB2 Connect EE Enterprise Edition
• Websphere Application Server, SAP, others . . .

14
DBAT Processing Modes

• Mode is defined with the ZPARM CMTSTAT
• DDF Threads on panel DSNTIPR
• Two choices
• INACTIVE highly recommended
• Provides DBAT pooling for DRDA access
• More effective WLM classification per UOW
• Reduced Resource usage
• ACTIVE
• DBAT created for each new client application
• DBAT held through commits
• Use this only if the applications require it

15
Processing Diagram
INACTIVE
ACTIVE
New Connection
Yes
gt CONDBAT?
Reject
ResumedConnection
MAXDBAT Reached?
Pooled DBAT Avail?
No
Yes
No
Yes
Queue
Create DBAT
Reuse DBAT
Enclave /Class 1
Reply ready to client
First SQL / UOW?
Process SQL
Enclave / Class 1
Yes
No

Term. DBAT Connection
Commit / Rollback?
Yes
Yes
No
End enclave / Write Acctg
Pool DBAT /Inactv. Conn.
Term Thread?
End enclave / Write Acctg
No
Yes
16
WLM Enclaves

• WLM Enclaves are independent, dispatchable
  units-of-work that span multiple address spaces
  and can include combinations of SRBs and TCBs
• DB2 use enclaves for work coming into the system
  through DDF
• Controlled by WLM
• Thread priority set by WLM workload
  classification
• Providing good DDF classifications is vital
• Enclave completes accounting data is ready
• Defines class 1 elapsed times of a thread
• (Not affected by rollup option)

17
WLM Enclaves

• INACTIVE mode
• No end user think time included
• Enclave is created when the first SQL is received
• Enclave is deleted at commit / rollback (thread
  complete)
• New enclave for each UOW, reclassified by WLM
• Can use multi-period response time or velocity
  goals
• ACTIVE mode
• End user think time is included
• Enclave is created when the DBAT is created
• Enclave is only deleted at thread termination
• Only one enclave, no reclassification
• Can only use a single-period velocity goal

18
DBAT Thread identifiers - Basic

• Connection Type WLM CT
• DRDA or Private Protocol
• Other IDs for DB2 to DB2 work (DRDA or PP)
• All come from the remote requester thread
• Even with a hop, they come from the requester
• Other IDs for non-z/OS DRDA clients
• Two unique identifiers
• Connection Name SERVER
• Plan DISTSERV WLM PN

19
More Identifiers from non-z/OS Clients

• Clients can flow other identifiers to DB2 for
  z/OS
• ODBC/CLI/VB (SQLSetConnectionAttr)
• Non-OBDC (sqleseti)
• JDBC (DB2Connection)
• DRDA (ACCRDB prddta / sqlstt in EXCSQLSET)
• Most important IDs supported in V8 with special
  registers
• Client Accounting (see QMDA below)
• Workstation Userid WLM SPM 1-16
• Workstation Name WLM SPM 17-34
• Workstation Application WLM PC 1-32

20
Other Differences DRDA Clients

• Package / Collection WLM CN/PK
• First package accessed
• Stored procedure name WLM PR
• If First SQL is a CALL
• AUTHID of client WLM UI
• Often not unique for non-z/OS clients
• Original primary AUTHID
• Used to make initial connection to server
• Correlation ID WLM
  CI
• DDM external name (EXTNAME) for client
• Accounting correlation token
• 22-byte token

21
More Accounting Information

• Special section for thread accounting data
• Used for additional client identification
• Only in the accounting record IFCID 03
• Product ID - shows the client source product
• SQL DB2 for LUW / DB2 Connect
• JCC Universal JDBC Driver
• DSN DB2 for z/OS requester
• DSN accounting string (z/OS)
• A repeat of the QWHC identifiers, except
• MVS accounting string (QMDAACCT)

22
Non-z/OS Accounting IDs

• SQL or JCC Accounting
• Client platform
• Client application name
• Client AUTHID of an application process
• Accounting String WLM AI
• Also, IDs from the DB2 for z/OS server
• Subsystem instance WLM SI
• Subsystem collection name (Data sharing group)
  WLM SSC
• Sysplex name WLM PX

23
DBATs and Accounting

• ACTIVE mode
• Only cut at thread termination, not at commit
• INACTIVE mode
• DRDA at clean COMMIT or ROLLBACK
• Type 2 inactive
• DRDA with KEEPDYNAMIC(YES)
• At clean commit (DB2 V8 and above)
• PP DBAT at commit or termination
• At commit, if Type 1 Inactive (MAXTYPE1)
  allowed
• Else only at termination
• Active thread is idle too long and is canceled
• At Idle Thread Timeout (IDTHTOIN), if allowed
• Checked every 2 minutes

24
Accounting and DDF Rollup

• Option in DB2 V8 to reduce accounting volume
• Turned on if ZPARM ACCUMACC gt 1
• Data accumulated for specified of threads
• For matching IDs, based on ACCUMUID
• Combination of the 3 workstation IDs
• Accounting written when
• Too old (staleness threshold)
• Too much (internal storage threshold reached)
• Just enough (limit threshold reached)
• One accounting record reflects one or more
  threads
• Currently no DDF statistics (QLAC) or QMDA
  accounting
• Only one ROLLUP package
• Active thread data only shows the current thread
  counts

25
Connection and Thread Processing - Review of
INACTIVE Mode

• (1) A new connection (in DIST) is established
• (2) DB2 attempts to allocate a DBAT
• Use a pooled DBAT if possible
• Allocate a new DBAT if possible (expensive)
• Queue if MAXDBAT reached (RQ)
• DBAT shows as pooled until SQL is received (DA)
• (3) UOW processes SQL (RA)
• Idle thread timeout can cause it to be canceled
• (4) Clean commit or rollback completes the UOW
  
• Frees the DBAT to be pooled, connection goes
  inactive (R2)
• KEEPDYNAMIC(YES) keeps the DBAT until termination
• (5) New SQL resumes the connection and a new
  UOW
• (6) Disconnect frees the connection

26
Real DBAT Thread Status (1)

• Assigned to a remote client (RA or RX)
• Actively processing executing SQL
• Active but idle waiting for more SQL
• Waiting for more work after clean commit, if
• INACTIVE mode only
• KEEPDYNAMIC(YES) all resources DBAT kept
• Type 1 inactive PP only / some resources freed
• ACTIVE mode even after commit
• All resources DBAT kept until thread
  termination
• Suspended to connect (PP only, temporary) (RN)

27
Real DBAT Thread Status (2)

• Pooled (DA)
• DRDA clients only, with INACTIVE mode
• Freed or newly created DBATs are pooled
• Also referred to as DBAT slots
• Available for reuse by any new / resumed request
• (Still somewhat in standby for previous client)
• Still uses resources (esp. DBM1 storage)!
• Occasionally terminated to free storage
• Still shown and counted as active threads
• But connection name is DISCONN
• Can be terminated if not used (POOLINAC)

28
Where are the Inactive Type 2 DBATs?

• They are referenced often in various manuals
• Pooled DBATs? Not DBATs at all !
• Actually, they are the inactive connections
• Associated with a remote requester
• Waiting for more work
• This speeds up response to additional SQL
• Tracked in DIST, and use less storage (7.5K)
• Shown only with DIS THREAD TYPE(INACTIVE)
• Connection name is now SERVER
• Thread status (R2)

29
And Inactive Type 1 DBATs?

• These are real DBATs
• Idle between UOWs
• Only Private Protocol
• Old style of inactive processing
• The DBAT is still assigned
• But resources are reduced
• This can only occur if MAXTYPE1 gt 0
• And limit is not reached

30
Understanding Thread Status

• Active thread displays
• Show both assigned and pooled DBATs
• Even though pooled DBATs arent really active
• Inactive thread displays
• Show the inactive connections in DIST
• While still associated with a pooled DBAT
• Looks like the same requester is both active and
  inactive
• When pooled DBAT is terminated or reassigned
• The requester disappears from active
• Still shows as inactive until connection
  terminated

31
Conversation Processing

• Conversations are used for actual traffic on a
  connection between two remote partners
• When processing, the conversation is
• Shown under the active thread
• Otherwise, the conversation is
• Shown under the inactive connection
• After the initial connection until the first SQL
• After a successful commit

32
Viewing Active Threads

• Assigned DBATs are identified with SERVER
• Pooled DBATs with DISCONN
• Only the number is interesting (see statistics)
• Extra DDF activity counts
• Data sharing considerations
• Various routing mechanisms across members
• Need a group view of DBATs
• To see complete distributed workload
• In MVDB2, use SSI mode with a group context

33
Active DBATs (Data Sharing Members)

Customize with workstation IDs, other values as
neededScroll right to see more fields
Hyperlink
34
Enclave views (MVzOS)
35
Checking Client Connections

• Many will be inactive connections
• Shown as inactive threads (with client IDs)
• Some have active DBATs
• In-flight accounting data is available
• Conversation is with DBAT or inactive connection
  and shows
• Whether the conversation is active in the network
  or suspended in DB2 waiting for a response
• Last send/receive time stamp
• Whether it is receiving or sending
• The remote location (IP address) and Sessid -
  local and partner ports (for TCP/IP)

36
Inactive Thread (Connection) View
37
Connection / Conversation Views
Hyperlink on Tot Conn to see details
38
Analyzing DDF Thread Data

• The accounting data is the first source
• Still analyze other application considerations
• Elapsed and CPU times, I/O, SQL counts . ..
• But in addition
• Elapsed time inside / outside the DB2 server
• Number of messages and blocks sent / received
• Batch reports summarized by
• The important DDF identifiers for your workloads

39
Thread Accounting
40
Tracing Distributed Workloads

• Additional focus on one workload
• Summary exception trace (accounting)
• Detail trace with important event IFCIDs
• All the usual qualifiers are available
• For DDF, important to reduce the data
• Filter by requesting location
• Filter by Workstation ID(s)
• In V9, DB2 also allows qualification by these IDs
• Exception Filters can be used to keep only
  threads that may need analysis (high In-DB2
  elapsed, etc.)

41
Detail Traces

• Detail traces can include selected event groups
• Basic thread flow and SQL
• Also can choose to add scans, I/O, locks
• Another group to include specific DDF events
• The volume can be high
• Use it only when needed
• To understand the conversation flow
• Each event has a pop-up view with the IFCID
  details

42
DDF Statistics

• The next place to look are the statistics
• Global statistics
• Critical DB2 subsystem tuning information
• Location statistics
• Application impact on DB2 and network
• DRDA_Remote_Locs (combined)
• Private Protocol locations (separate)
• DDF Address Space CPU usage
• TCB and SRB

43
Global DDF Statistics - STDISTD
44
STDISTD View - Revised
45
Exception Monitoring

• Review your current exceptions
• Are DDF conditions being monitored?
• Statistics
• DBAT high water mark
• Queuing for a DBAT?
• DDF still active?
• DBM1 storage usage
• Accounting
• Focus on DDF service levels
• Filter for DBATs / most important work
• Elapsed time / CPU usage

46
DDF-Related ZPARM Review

• CMTSTAT DDF Threads
• IDTHTOIN Idle Thread Timeout
• TCPKPALV TCP/IP Keepalive
• POOLINAC Pool Thread Timeout
• ACCUMACC and ACCUMUID
• MAXTYPE1 (PP) Max Inactive DBATs
• KEEPDYNAMIC(YES) / MAXKEEPD
• EXTRAREQ / SRV Extra Blocks REQ / SRV
• And of course
• MAXDBAT Max Remote Active
• CONDBAT Max Remote Connected

47
DDF ZPARM View
48
DDF Resource Usage

• CPU TCB and SRB
• In the DIST address space
• Management of the DBATs and connections
• For the threads themselves (enclave SRBs)
• DBM1 storage (MAXDBAT, and CTHREAD)
• Management of thread storage is critical
• DIST address space
• Storage likely not an issue (CONDBAT)
• Dynamic SQL cache
• Most distributed SQL is still dynamic
• The cache is critical for good performance
• Aim for an 80 or better hit ratio for SQL reuse

49
DBM1 Storage DB2STORD View
50
Dynamic Cache STCACHED View
51
Extended Reporting

• Distributed workloads are often volatile
• Less insight and control
• Can be useful to track activity over time
• Store and query summary data in DB2 tables
• When needed, distributed traces and monitoring
• z/OS reporting on WLM can be helpful
• Enclaves SMF 30
• Workloads by service class SMF 72
• MVzOS provides online views as well as reports

52

• Questions?

53
Demystifying the DB2 Dynamic Statement Cache
54
What Will We Talk About?

• Some SQL Tuning Fundamentals
• Dynamic SQL in More Detail
• Introduction to DB2 Statement Caching
• Mining for Gold in the Global Statement Cache

55
SQL Tuning FundamentalsAccess Path Selection

• Static SQL
• Access path determined at bind time better
  performance
• Exceptions to the rule
• REOPT (VARS) or (ALWAYS)
• Access path determined at run time for those
  statements with host variables or parameter
  markers
• PREPARE(DEFER)
• Option useful in distributed environments for
  reducing message traffic
• Authorization for execution at the plan/package
  level
• Qualifiers passed via host variables
• SQLJ provides for bound static SQL in Java
  applications

• For Dynamic SQL
• Access Path Selection determined at execution
• Thats the PREPARE
• Exceptions to the Rule
• KEEPDYNAMIC bind option
• Holds prepared statements across commits to avoid
  cost of re-preparing statement
• Global Dynamic Statement Cache
• Maintains Skeleton of prepared statements
• Build and execute SQL on the fly
• User requires authorization to all accessed
  objects
• Parameter markers for passing variables

56
Trends in the Marketplace Static vs. Dynamic SQL

• Dynamic SQL usage is on the increase
• Whats driving it?
• Dynamic SQL offers flexibility that can simplify
  developing complex applications
• New applications being developed on distributed
  platforms using connections that only support
  dynamic SQL
• DB2 CONNECT, etc.
• ERP applications implemented with dynamic SQL
• SAP, PeopleSoft, Siebel
• New applications being developed on distributed
  platforms
• New developers are much more familiar with
  GUI-based programming environments and dont even
  sign on to the mainframe
• More Java and C

57
SQL Fundamentals - Dynamic SQL

• Data access requirements are ad hoc in nature and
  identified on the fly
• SELECT Operations

Parameter marker provides placeholder for later
substitution
Notice the literal

• Other operations
• Cause the INSERT statement to be prepared and
  executed immediately

58
SQL Fundamentals - Dynamic SQL In Practice

• A Statement from a major ERP application
• Built on the fly based on search criteria
  selected
• A complex statement with unpredictable input
• Default statement syntax includes minimal number
  of search criteria
• More search criteria the statement expands to
  include those search arguments
• If using static SQL could require over 100 cursor
  definitions in the program

59
Dynamic SQL Operational Considerations

• Sensitive to DB2 statistics
• Dynamic SQL always uses current catalog
  statistics for access path selection
• Changes in DB2 statistics can cause unpredictable
  changes in access paths
• Some DB2 customers collect catalog statistics to
  drive maintenance processes
• May cause SQL performance to fluctuate
  unexpectedly
• Security is generally more complex with dynamic
  SQL
• Application users generally require authorization
  to the objects being accessed
• Auditing is also affected because statements are
  developed on the fly
• Governor capability may be required
• Performance characteristics can vary widely for
  dynamic
• DB2 Resource Limit Facility may be required
• Access path analysis difficult because access
  path is not available prior to execution

60
Dynamic SQL ConsiderationsPREPARE Yourself

• Repeated PREPAREs drive up the cost of dynamic
  SQL
• Prepared statements by default are not persistent
  across UOWs
• Prepare costs vary widely but are significant
• Key requirement from anyone developing dynamic
  SQL applications to reduce or eliminate the cost
  of preparing dynamic SQL statements
• Driven initially by SAP and other ERP vendors
• More in-house dynamic SQL applications drive this
  requirement
• Enter Dynamic Statement Caching

61
Introduction to Dynamic Statement Caching

• Goal is to reduce or eliminate SQL Prepare
  operations required for dynamic SQL statements
• Implementation
• Four kinds of caching
• No caching
• Local Dynamic Statement Caching
• Global Dynamic Statement Caching
• Full Caching
• Cache prepared SQL statement and statement text
  for dynamic SQL statements in DBM1address space
• Local Statement Cache
• Global Dynamic Statement Cache
• Controlled by various parameters
• Bind options
• DSNZPARMs
• Application constructs

62
Dynamic Statement CachingNo Statement Caching

• Prepared statements do not persist across commits
• Discarded at commit
• Except for statements defined with CURSOR for
  HOLD
• Default mode of operation

Program RRS01
Thread Storage
Prepared Statement STMT2(Version 1)

• No prepare returns -514 or -518

63
Dynamic Statement CachingWith Local Statement
Caching Only

• Eliminates need for application to do multiple
  prepares for same statement
• Implicit prepares done by DB2
• Enabling Local Statement Caching
• KEEPDYNAMIC(YES) Bind Parameter
• MAXKEEPD DSNZPARM controls maximum prepared
  statements
• Does not affect statement text which is always
  kept
• Differentiation between prepared statement and
  statement text
• Minimal benefit if used alone
• Some reduction in message traffic in a
  distributed environment is possible

Statement Text Retained
64
Dynamic Statement CachingGlobal Statement
Caching Only

• Allows reuse of prepared statements across UOWs
• Within and across program executions
• Prepared statement (SKDS) cached in global
  dynamic statement cache
• Copied into local storage when possible
• Short Prepare
• Enabling global statement caching
• CACHEDYNYES DSNZPARM value
• Storage allocation discussed later
• Big benefit for applications with frequent reuse
  of dynamic SQL
• Benefits with no coding changes required

Global Statement Cache
SKDS
SKDS
65
Dynamic Statement CachingWhere Cached Statements
can be Reused

• Statement text must be 100 the same
• Use parameter markers
• Literals wont work (usually)
• Additional items must be 100 the same or
  compatible
• Bind rules
• Special registers
• Authorizations
• Others
• You may not get any benefit out of the dynamic
  statement cache at all
• Most likely to benefit if you using an ERP or
  some other application that uses dynamic SQL
  extensively

66
Dynamic Statement CachingFull Caching A Final
Flavor

• Combines benefits of local and global statement
  caching
• Ability to completely avoid prepare operations
• Prepared statement kept in local thread storage
  and not invalidated across commits
• Prepare Avoidance
• Enabling global statement caching
• CACHEDYNYES, MAXKEEPDgt0, KEEPDYNAMIC(YES)
• Maximum benefit within an application execution
• Local thread storage is discarded at thread
  termination

Program RRS01
67
Dynamic Statement CachingCost Impacts
Relative Cost

• Full Prepare
• Statement not in cache
• Global statement caching not active
• Short Prepare
• Dynamic statement (SKDS) in the global cache
• Global caching active
• Avoided Prepare
• Local and global caching active

100
1
0
68
Dynamic Statement Caching Impacts on Storage
EDM Pool in DB2 V7
DB2 Database Services DBM1

• Caches access path internal structure
  definitions
• This pool contains
• DBDs database descriptors
• Skeleton Package and Cursor Tables (SKPT SKCT)
• Package and Cursor Tables (PT/CT)
• Authorization cache block for each plan
  (optional)
• SKDS - Skeletons of dynamic SQL for CACHE DYNAMIC
  SQL (optional)
• Optionally stored in a dataspace
• Trigger Packages

69
Dynamic Statement Caching Impacts on Storage
EDM Pool In DB2 V8
DBM1 - DB2 Database Services
DBDPOOL

• EDMPOOL now in 3 separate pools
• EDMDBDC DBDs
• Above the Bar
• EDMSTMTC Dynamic Statements
• Above the Bar
• EDMPOOL Skeleton Package and Cursor Tables
• Still below the bar and a potential source of VSC
• No dataspace option for Dynamic Statement Cache

DBDs
DBDs
GLOBAL STATEMENT CACHE
SKDS
SKDS
SKDS
2GB Bar
SKCT
PT
EDMPOOL
SKPT
CT
70
Dynamic Statement Caching Impacts on Storage
EDM Pool In DB2 V9
DBM1 - DB2 Database Services
DBDPOOL
EDMPOOL

• Portions of runtime Components moved above the
  bar
• Plan and package skeletons above the bar
• Bound/Prepared DML Statements
• Statement Text
• SQLDA DESCRIBE output
• Portion of native SQL PL package
• Portions of static SQL sections (CT/PT) are
  moved as well
• Further reduces VSC in the DBM1 address space

SKPT
SKCT
DBDs
DBDs
CT
PT
GLOBAL STATEMENT CACHE
2GB Bar
SKDS
SKDS
SKDS
EDMPOOL
CT
PT
71
Dynamic SQL Statement CachingDB2 Cache
Statistics
Statement Pool Full Failures Should be 0 Increase
Statement Pool Size if not
Global Cache Hit Ratio Shoot for 80
Local Cache Hit Ratio Specific for Applications
bound with KEEPDYNAMIC(YES)
Statement Discarded Shoot for 0 Increase MAXKEEPD
72
The Global Dynamic Statement CacheWhat Goes In?

• Dynamic Statements
• If the Global Cache is active (CACHEDYNYES) and
  not a REOPT(ALWAYS) application
• Reside in the till they are thrown out
• DROP or ALTER
• Authorization Revoked
• LRU
• RUNSTATS
• DB2 is recycled

73
Retrieving Data From the Global Cache

• As shown previously
• Statement caching performance data in DB2
  statistics records
• Metrics show details about cache hit ratios and
  other useful data points that help you evaluate
  overall performance of your statement caches
• For more detail on Global Statement Cache usage
  the following instrumentation is provided
• IFCID 316 Provides details on statements in the
  cache
• First 60 bytes of SQL text
• Includes execution statistics (0 if not being
  collected)
• IFCID 317 can then be used to retrieve the entire
  SQL statement from the cache once you have
  identified the statement of interest
• EXPLAIN STMTCACHE
• V8 feature that exports Dynamic Statement Cache
  information to the DSN_STATEMENT_CACHE_TABLE
• Nearly identical to the detail in IFCID 316 317
• Multiple options including ALL, stmt-id, and
  stmt-token

74
Reviewing Global Statement Cache
InformationIFCID 316 Results

• First 60 Bytes of SQL Text
• IFCID 317 gives full text
• Bind Options
• Statement Statistics (more later)

75
Mining the Dynamic Statement CacheEXPLAIN
STMTCACHE ALL

• Extracts all statements from the global cache
• Inserts one row for each entry in the global DSC
• Populates DSN_STATEMNT_CACHE_TABLE only
• STMT_ID column matches the Unique ID in the
  global statement cache
• Nearly exact match to the DSC with a few
  additional columns
• STMT_TEXT is a 2M CLOB so be careful with that
• COLLID set to DSNDYNAMICSQLCACHE

DSN_STATEMENT_CACHE_TABLE
76
Mining the Dynamic Statement CacheEXPLAIN
STMTCACHE STMT_ID

• Extracts a single statement from the global DSC
• Populates PLAN, DSN_DYNAMIC_STATEMNT,
  DSN_STATEMENT, and DSN_FUNCTION tables if they
  exist
• Access path is current access path for statement
  in the cache
• Numeric literal or host variable from program
• -248 SQL Return Code back to program is STMT_ID
  not found

77
Mining the Dynamic Statement CacheEXPLAIN
STMTCACHE STMTTOKEN

• Extracts a group of statements from the global
  DSC
• Populates PLAN, DSN_DYNAMIC_STATEMNT,
  DSN_STATEMENT, and DSN_FUNCTION tables if they
  exist
• Access path is current access path for statement
  in the cache
• Based on STMT_TOKEN value in the cache
• Alphanumeric literal or host variable in program
• -248 SQL Return Code returned if no qualifying
  entries found in cache

78
Mining the Dynamic Statement CacheMore on the
STMT_TOKEN in the Cache

• Provides a method for grouping similar SQL
  statements
• STMTTOKEN values set using RRSAF or sqleseti
  functions
• Similar to Client special registers implemented
  in DB2 v8
• PL/1 RRSAF Example

79
Reviewing Global Statement Cache
InformationIFCID 318

• Execution statistics for dynamic SQL statements
• Turn on collection with Monitor trace IFCID 318
• Begins collecting statistics and accumulates them
  for the length of time the monitor trace is on
• Stop Monitor trace resets all statistics
• 2-4 overhead per dynamic SQL statement stored in
  the cache
• Recommended approach
• Run the trace only when actively monitoring the
  cache
• Use EXPLAIN STMTCACHE to externalize data for
  evaluation

80
Acknowledgements

• There are numerous documents that discuss SQL in
  general and dynamic SQL in particular, including
• DB2 technical publications
• Technical articles by numerous DB2 Subject Matter
  Experts
• IDUG List Server Archives
• IBM Redbooks on this topic were especially
  helpful in researching this presentation,
  including
• DB2 for z/OS and OS/390 Squeezing the Most Out
  of Dynamic SQL
• DB2 UDB for z/OS V8 Through the Looking Glass
  and What SAP Found There

81
Summary

• Dynamic SQL is growing in usage
• ERP Vendors
• Distributed applications
• DB2 offers multiple options for reducing the
  overhead traditionally associated with dynamic
  SQL
• These options include multiple types of statement
  caching
• Local statement caching
• Global statement caching
• Full statement caching
• DB2 9 will see big changes in the way the SQL
  statement execution statistics discussed in this
  session will be used captured and used