Transcript Slide 1

Managing Distributed Workloads
MAINVIEW for DB2 9.1
Bill Arledge
7/7/2015
DB2 V9 Support
› DB2 V9 impact on performance products
–
–
–
–
64-bit – DIST address space (many DDF control block changes)
Two new pools separated from EDM for SKCT & SKPT, one pool ATB
Lots of ZPARMs, as usual
New instrumentation – statistics, accounting, storage, detail events
• New SQL types, XML lock, new counts, etc.
–
–
–
–
–
2
New thread identifiers – role and trusted context
Converging of temp space usage into sort work files
Clone tables
Many new Start Trace qualifiers
IP V6 support – location can now actually be a long name
© Copyright 7/7/2015 BMC Software, Inc.
DB2 V9 Exploitation
› Statistics views – new statistics data
– STDB2SYS / STDBSYSD show new Preemptible SRB and ZIIP fields
– STEDMP/D, STCACHE/D, WZCACHE
• Support new skeleton pools (SKCT, SKPT)
• STEDMP/D support new “package not found” counts
–
–
–
–
–
STCMDS/D – 4 new commands
STSQL/D – 9 new SQL counts
STWKTMP/D – new views for converged temp table / work files
STBFRPL/D, BFRPL/D – 2 new counts, new AUTOSIZE attribute
DB2STOR/D – 6 new values for ATB shared storage / stack storage
› ZPARMs
– All new ZPARMs (20-25) added to appropriate detail views and index
3
© Copyright 7/7/2015 BMC Software, Inc.
STDBSYSD – Both V9 and V8
 DDF management overhead, not thread CPU
4
© Copyright 7/7/2015 BMC Software, Inc.
STEDMP/D – EDM Changes
Section for new pools – n/a if not V9)
New package search data
5
© Copyright 7/7/2015 BMC Software, Inc.
MVDB2 9.1.00 Enhancements – Views **
› The ZPARM index views ZPNAMEx revised for usability
› The Status application was revised to allow field additions
– We had reached our limit in MVDB2 8.2.00 (too many DB2 statistics!)
– Can now support the latch counters - Views STLATCH and STLATCHD
• ** Use MV ALARM to set alerts on rate/second fields
› THDACTV revised to enhance usability
–
–
–
–
–
–
–
–
–
–
6
Make CORRID the first key column (more likely unique than Authid)
Move Plan forward, Connect Name back
Add Page Updates, I/U/D Stmts, Rollbacks and Updates/Commit near Commits
 Add hyperlink on Enclave Token to MVMVS to view WMENCLVZ
Add In-DB2 CPU time and In-DB2 Elapsed Time
*** Add SQL stmt# after Current Activity (pending) ** Done
** Add stored procedure CALL statement count
** Two hyperlinks to APPTUNE – plan (moved from %CPU) or package report
** Add SQL cache token – hyperlink to SCSQLD cache entry
** Add DDF Product ID – allow filtering by DB2 Connect / JDBC
© Copyright 7/7/2015 BMC Software, Inc.
THDACTV
** Demo system now at MVDB2 9.1.00 / CDC 5.4.00
7
© Copyright 7/7/2015 BMC Software, Inc.
ZPNAMEx
ZPARM name in first column – more intuitive tabbing / also improved colors
8
© Copyright 7/7/2015 BMC Software, Inc.
MVDB2 9.1.00 Enhancements - Views
› Improved STDB2D and STDB2DS
– DDF and RLF status
– CPU% measures DB2 address space CPU, not z/OS
– A hyperlink to issue the DIS TRACE command in the Journal
• Some users may not have the proper authorization for the command
• V9 trace display data too complex to add
–
–
–
–
–
And more . . .
Buffer pool fixed threshold flag (count if any exceeded)
EDM failures
DBM1 storage usage fields, including available storage
MONSIZE and IFIBUFF added to simplify IFI diagnostics
• DB2 buffer size and MVDB2 buffer size
9
© Copyright 7/7/2015 BMC Software, Inc.
STDB2D
...
10
© Copyright 7/7/2015 BMC Software, Inc.
Dynamic Statement Cache Reporting Enhancement
WZCACHE / SCPGMZ
**
**
11
© Copyright 7/7/2015 BMC Software, Inc.
Enhancements – Performance Reporter
› New statistics summary table support
– Provides two levels of summarization, similar to accounting
• Helpful for long-term trending
– DB2 changed default STATIME statistics interval to 5 minutes
• Increases number of records 6x
› Optimize PR purge process – improve concurrency
– Option to do commits after “n” records
› AutoCust step builds CDC archive job that includes PR load
– Optional
– Careful about elapsed archive time with high volume workloads!
› Improve field documentation in the PR User Guide
– More like DB2 PM, organized by functional area like SQL, EDM, etc.
12
© Copyright 7/7/2015 BMC Software, Inc.
MAINVIEW for DB2 9.1
New DDF Windows Mode Application
› Primary focus is to monitor client connections
› DB2 z/OS as the server
– SQL requests coming in through DDF to run in DBAT threads
– From DB2 Connect, Java JDBC or SQLJ, WebSphere AS . . .
– Or from another DB2
› Also captures DB2 z/OS requester activity
– A request from this DB2 for data from another DB2
– From an allied thread or a DBAT
• So a client DBAT could “hop” to another DB2 for the data
› Connections are between a DB2 and remote locations
› Remote location is identified by
– The TCP/IP address (or VTAM LU) of a requester to DB2 z/OS
– DB2 location name of another DB2 (requester or server)
• Link Name is the LU
13
© Copyright 7/7/2015 BMC Software, Inc.
New DDF Windows Mode Application
› Three connection statuses:
– 1 - Active connections - currently active with a thread
– Also see them in THDACTV / THDDBAT
– 2 - Inactive connections related to a thread, but marked as “DISCONN”
• These are actually pooled threads that can be reused by any client
– ** THDACTV “Current Activity” now shows “POOLED”
– 3 - Inactive connections with no thread
• Client information is kept (in db2xDIST) to speed up response to next SQL
– Often referred to as “inactive threads” (THDINACT) (are really only connections)
› Conversation data is kept
– With the thread if there is one, otherwise with the inactive connection
– Includes important information:
• Remote location (IP address) and “Sessid” – local & partner ports
• Status of the connection – active / suspended
• Last activity send / receive
• The last activity time stamp
14
© Copyright 7/7/2015 BMC Software, Inc.
DDF View Data
› DB2 has two display commands
– DIS LOCATION shows all connected remote locations with a few counts
• Requester threads , server threads, number of conversations
– DISPLAY THREAD(*) LOCATION(*) [DETAIL]
•
•
•
•
Active and pooled threads only (no Product-ID)
Add DETAIL to get to conversation data - but cryptic to read
 Needed to detect hung connections
 Needed to see thread “hops” – DBAT requesting data from another DB2
› DDF Views are based on complete DDF data
– All connections, both active and inactive
– All conversations, both active and inactive
– Complete picture of remote users of DB2 resources
• See status, time stamps, last activity type – easy to find and read
› DDF views provide powerful analysis capabilities
– Summarization, sorting, filtering, direct hyperlinks to THDDETL / DUSER
15
© Copyright 7/7/2015 BMC Software, Inc.
DDF Easy Menus
› ** Needed to simplify access to all distributed data
› DDF information was scattered all over
–
–
–
–
–
DBAT threads , inactive threads
DDF global statistics and activity counts
DDF exception and workload monitors
DDF-related ZPARMS
Plus new DDF* views on connections and conversations
› Collect all options on one easy menu – EZDDF
– “ > DDF Menu “ hyperlinks from EZDSSI, EZDB2, EZDBA
– Designed for use with an SSI context or one DB2
– Menu options and client product views
• Client product IDs: SQL = DB2 Connect, JCC = JDBC Universal Driver (Java)
– DB2 Connect can direct work to multiple DB2 members
• SSI access simplifies DDF workload analysis with data sharing
16
© Copyright 7/7/2015 BMC Software, Inc.
EZDDF – Primary DDF Menu
New DDF views
17
** DDFPRODZ
DDFLOCZ
EZDDF2
THDDBAT
** SQL / JCC
** DDFINACT
<< Hyperlinks >>
STDISTD
STDDFD
ZPDDFD
© Copyright 7/7/2015 BMC Software, Inc.
D@ELTD DBATDRDA/DDF
DDB2DP DDF
DSERV MONAREA=DDF
STDIST
STDDF
EZDDF2 – Filtered Access to New DDF* Views
<< Hyperlinks >>
DDFTHDZ
18
** DDFPRODZ
DDFLOCZ
© Copyright 7/7/2015 BMC Software, Inc.
DDFLIST
** Client Product View
› ** DDFPRODZ summarizes DDF connections by client product
– Two main products, and a few others
• SQL – DB2 Connect (actually DB2 for LUW – Linux, Unix, Windows)
– Gateway and individual connections
• JCC – DB2 Universal SQLJ and JDBC Driver (Java applications)
– Usually dynamic SQL, also supports SQLJ for static SQL
• DSN – DB2 for z/OS
• ARI – DB2 for VM / VSE
• QSQ – DB2 for iSeries
› Important DDF status overview information
– Active and total client connections
– Network idle time / time since last network activity
• Hung connection / server down?
› View context can be one DB2 or SSI
– Use data sharing group to see sysplex-wide DDF activity
19
© Copyright 7/7/2015 BMC Software, Inc.
** DDFPRODZ – Client Product Summary View
** Hyperlink on Client Product to see connection summary by DB2 and remote location
(DDFLOCZ)
** Hyperlink on connection / conversation counts to see detail list of threads or
“agents” (DDFTHDZ)
20
© Copyright 7/7/2015 BMC Software, Inc.
Location View **
› DDFLOCZ summarizes DDF connections / conversations by
DB2 subsystem and remote location
› Access from DDFPRODZ on “SQL” to see
– DB2 Connect gateways supporting many clients
– Single clients (end users of DB2 Connect Personal Edition)
› Access from DDFPRODZ on “JCC” to see
– All direct JDBC clients
› EZDDF options to select connections for
– All locations, TCP/IP or SNA
› EZDDF2 options to select connections for
– Server or requester, active or inactive
21
© Copyright 7/7/2015 BMC Software, Inc.
DDFLOCZ
Scroll 
Summarizes connections and conversations – requester, server, total, inactive
Network Idle Time is calculated from the most recent time stamp
First entry is a DB2 Connect gateway with 4 DBATs (server connections)
22
© Copyright 7/7/2015 BMC Software, Inc.
Using Location View DDFLOCZ
› Sort on total connections or server connections (DBATs)
– High-activity connection managers pop up to the top
– Can identify and select DB2 Connect gateway activity
› Sort on remote location in SSI mode to analyze data sharing
– Can see DB2 Connect gateways feeding multiple DB2 members
– Workload distribution working correctly?
› Check network idle time – hung connections?
– Perhaps to a gateway – server down!
› ** PING command available on several views
– Check status of TCP/IP connection, see DNS name and response time
• Also considering TRACEROUTE to see hops
23
© Copyright 7/7/2015 BMC Software, Inc.
New Connection / Conversation Views . . .
› For a location, drill down to the “agent” (thread) level
– By location, or also by connection type– requester, server, inactive
– Usually see one conversation per client
› DDFTHDZ is a summary view to handle application “hops”
– Client application DBAT requests data from other DB2(s)
• More than one conversation for that client
– For connection managers, you see multiple agents (clients)
› DDFLIST is a detail list of the selected conversations
– Each “agent” has Identifiers, network time stamp, agent and network status
– Hyperlinks to DDFDETL, or to THDDETL / DUSER for active threads
› Simplifies the analysis of whether
– That network connection itself is in trouble
– Or just specific clients have been inactive for a long time
24
© Copyright 7/7/2015 BMC Software, Inc.
DDFTHDZ
** Also has PING line command
DDFTHDZ very useful when applications can do “hops” (Cnv Cnt > 1)
- Can drill down to all the conversations
If active, hyperlink on Thread Token to THDDETL, on Auth ID to DUSER
25
© Copyright 7/7/2015 BMC Software, Inc.
DDFTHDZ – For a DB2 Connect Gateway
See idle time and program per connection (link to THDACTV or DUSER)
26
© Copyright 7/7/2015 BMC Software, Inc.
DDFLIST
Hyperlink to DDFDETL
 Scroll for more IDs, including DDF workstation IDs
27
© Copyright 7/7/2015 BMC Software, Inc.
More DDF Enhancements – Threads
› New THDACTV improved for DBATs
– Includes client Product ID to support DB2 Connect hyperlink
– Hyperlink to MVzOS DDF enclave information
› THDDBAT completely revised
– Includes client Product ID to support DB2 Connect hyperlink
– Also client platform and accounting string
– Still includes RRSAF threads (WebSphere, other remote clients may not use DDF)
› Additional active conversation data in THD* views
– DDF status, last activity time stamp and elapsed time,
last activity = send/receive
› THDINACT
– Only showed client IDs, collected connections with every THD* request!
– ** Replaced by new DDFINACT in all hyperlinks
• Includes conversation data besides just the client identifiers
• Only collected when DDF data requested
– THDINACT will be deprecated in MVDB2 9.2
• Still in MVDB2 9.1 for cross-system compatibility during release migrations
28
© Copyright 7/7/2015 BMC Software, Inc.
More DDF Enhancements **
› DUSER / STRAC
– Add requester location to ENV section if DBAT
– Update / expand DDF section
› Monitors
– Update several based on research findings (were out of date)
• Improve help, and move 3 to DDF area instead of Users
– Add two new ones
• NACT2 – number of inactive connections
• RMTUT – remote connection % utilization
– Update BLKDMRW
› ZPARMs
– Include DDF rollup ZPARMs in ZPDDFD
• ACCUMACC, ACCUMUID
29
© Copyright 7/7/2015 BMC Software, Inc.
STDISTD – Global DDF Statistics
› STDIST and STDISTD completely revised
– Based on inaccurate DB2 documentation
– Was not easy to see the most important indicators
› This is critical DB2 subsystem tuning information
– Manage thread usage
• Only 2,000 threads allowed per DB2, including DBATs
– Manage connection utilization
• Increases DDF overhead and DIST storage
– Detect exception conditions
• Client connections queued for a DBAT
• Connections deallocated because maximum was reached
• Type1 connections terminated
– Investigate performance indicators
• Pooled thread reuse for better performance
• High resync activity or failures
30
© Copyright 7/7/2015 BMC Software, Inc.
Updated STDISTD View
31
© Copyright 7/7/2015 BMC Software, Inc.
More DDF Enhancements
› THDACTV hyperlink to MVzOS to show enclave data
–
–
–
–
Originally added for stored procedures, but:
DBAT work runs mostly under an enclave (SUBSYS=DDF)
Multiple workload classification rules may be in use
Important part of DDF workload tuning
› Trace and monitor qualifier DB2RTN (and DB2PKG)
– All DRDA work requires a package
– Stored procedures are often used to reduce network traffic
› Provide summary views of Dynamic SQL cache data
– Summarize by 3 available IDs (shown earlier)
• Help find SQL issued by distributed client or application
• Many DDF applications still use dynamic SQL
• Based on SCSQL – shows time cached, execute count, current users
– Link to see SQL text and detail – and Explain!
32
© Copyright 7/7/2015 BMC Software, Inc.
MAINVIEW for DB2 9.1
Summary
› Extensive Enhancements in DDF monitoring capabilities
– New views, new functionality
› Additional changes across the product
– Extensive DB2 9 exploitation
– Improvements in many base product views
33
© Copyright 7/7/2015 BMC Software, Inc.