mainview for db2 ddf session

04/12/23

Managing Distributed WorkloadsMAINVIEW for DB2 9.1

Bill Arledge

© Copyright 04/12/23 BMC Software, Inc.2

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.– 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


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


STDBSYSD – Both V9 and V8

DDF management overhead, not thread CPU


STEDMP/D – EDM Changes

Section for new pools – n/a if not V9)

New package search data


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– 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


THDACTV

** Demo system now at MVDB2 9.1.00 / CDC 5.4.00


ZPNAMEx

ZPARM name in first column – more intuitive tabbing / also improved colors


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


STDB2D

. . .


Dynamic Statement Cache Reporting EnhancementWZCACHE / SCPGMZ

**

**


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.


MAINVIEW for DB2 9.1New 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


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 / receiveThe last activity time stamp


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


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


EZDDF – Primary DDF Menu

** DDFPRODZDDFLOCZEZDDF2THDDBAT** SQL / JCC** DDFINACT

STDISTDSTDDFDZPDDFD

D@ELTD DBATDRDA/DDFDDB2DP DDFDSERV MONAREA=DDF

STDISTSTDDF

<< Hyperlinks >>

New DDF views


EZDDF2 – Filtered Access to New DDF* Views

DDFTHDZ ** DDFPRODZ DDFLOCZ

DDFLIST<< Hyperlinks >>


** 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


** 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)


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


DDFLOCZ

Summarizes connections and conversations – requester, server, total, inactiveNetwork Idle Time is calculated from the most recent time stamp

First entry is a DB2 Connect gateway with 4 DBATs (server connections)

Scroll


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


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


DDFTHDZ

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

** Also has PING line command


DDFTHDZ – For a DB2 Connect Gateway

See idle time and program per connection (link to THDACTV or DUSER)


DDFLIST

Hyperlink to DDFDETL

Scroll for more IDs, including DDF workstation IDs


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


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


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


Updated STDISTD View


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!


MAINVIEW for DB2 9.1Summary

› 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

mainview for db2 ddf session

Documents

db2 statistics

new counts

new values

new commandsstsqld

db2 requester

db2 pm

db2 zosdb2 location

db2 cpu time