MAINVIEW for DB2: What is the Data Collector, and Do I Need It?
And other selectable components
James Wilson Senior Software Consultant March, 2006
Yes
Agenda
› What are these components? › Why would I want them? › What is the cost? › How do I implement and use them? › Features and benefits
What are the MVDB2 Selectable Components?
› A method to provide additional functionality from other BMC
DB2 products / architectures
– In addition to the classic MAINVIEW interface – MAINVIEW for DB2 – Data Collector
• Common Data Collector (CDC) • Shared by other DB2 performance products • Provides new MVDB2 features plus product integration
– Catalog Manager for DB2 Browse
• Browse-only access to DB2 catalog tables • Provided by the Catalog Manager for DB2 product • No license required for access through MVDB2
Why Use The Components?
› Which features could benefit you?
– Optimized thread history data collection
• After-the-fact filtering options • Package level reports
– Hyperlink access to the common Explain feature
• Expanded SQL analysis
– – – –
System level event traces of key performance IFCIDs An alternative to writing DB2 records to SMF Additional batch reporting features Direct access while investigating performance issues:
• To DB2 catalog information • To other DB2 performance products • Additional reports
What is the Cost?
› No additional license / maintenance cost › The primary issue is additional complexity
– Not MAINVIEW-based
• Not yet packaged with SMP/E – but will be • Some new user navigation to learn – simple, but different
– The Data Collector is another address space to set up and manage – The Data Collector and Catalog Browse also access DB2s
How to Use the Components Effectively
› Installation
– You need both the M and C series tapes / files
• Warning - don’t select the “quick” install JCL!
– Components require a standard installation
• Other DB2 products can be selected as well
– A MAINVIEW SMP/E upgrade is possible
• Separate SMP/E install
– See the Installation Road Map
• In the MVDB2 Customization Guide • Walks you through the options and steps
How to Use the Components Effectively
› Customization
– Two paths – Customization Road Map is recommended
• MAINVIEW AutoCustomization • OZI Customization for the Components
• Also in the MVDB2 Customization Guide • Covers the MVDB2 issues to help you make choices • Does NOT cover customization of additional products • Review your plans before starting the install
– Reduces “guessing”, and perhaps guessing wrong
– And/or - call support
How the Components Fit Together
MV CAS
MV UAS
Catalog Browse
DB2
Hyperlink uses DMRACT clist - need MV & CAT HLQs - need CAT release and DOPTS
MVDB2 PAS
DB2
MVDB2/DC
DB2
Hyperlink uses DOMC clist - need CDC HLQ API requests data - need CDC LOAD in PAS Steplib
Authorization in PAS and CDC - need BDS password in BMCPSWD and in CDC LOAD
How Do the Components Interact?
› Hyperlinks to Report Manager (CDC UI)
– Access thread history from MVDB2/DC – Common Explain
• Very detailed Explain • For active threads or cached dynamic SQL • Future – also hyperlink from DUSER and trace
– Access reports with no MVDB2 equivalent
• List EDM pool CTs, PTs and DBDs • Show OPERTUNE ZPARM change log • Show data sets in use by a thread
– MVDB2/DC menu options
• Online administration, Explain options, trace archives
How Do the Components Interact?
› Data Collector API
– Used to retrieve data from the Data Collector trace data sets to display in MVDB2 views – Set up DB2 IFCID collection in the CDC – Online features
• Thread interval history views • System level event traces
– Feed the same data into batch reporting
How Do the Components Interact?
› Hyperlinks to Catalog Manager Browse
– Access local DB2s (where your user session is) – Access remote DB2s on other LPARs
• If DDF defined in DB2 and Catalog Manager
– Browse any DB2 catalog table – If licensed for Catalog Manager
• Also access other product functions
Feature Overview
› Major new function with selectable components
– – – – – – – Thread history options (1) Common Explain (2) System-wide event IFCID traces and views (3) Alternative to writing DB2 IFCIDs to SMF (4) Quick direct reports outside DPRSMF (5) Direct access to DB2 catalog data (6) Access to other DB2 performance products (7)
Thread History Options (1)
› Data Collector and DB2 Accounting Collection
– Allows access to longer thread history
• • • • CDC active trace data sets are compressed Data collection is staged in a data space to handle spikes Can archive to support batch reporting if desired Hyperlinks from MVDB2 view for online access
– What about MVDB2 THRDHIST?
• Modify traces to keep only the important threads • Less volume, quicker access to key data • Consider one or more summary traces
– Write most important workload(s) to THRDHIST » Use workload qualifiers – Discard or separate lesser workloads, wrap logs instead of archiving – Use exception filters to keep only threads that may require review » High CPU or elapsed times, getpages, locks, etc.
MVDB2/DC Online Thread History
› Enhance existing MVDB2 thread history
– Support thread history views
• Access either MVDB2 traces or MVDB2/DC data • Both can hyperlink to thread summary and detail reports
– Added MVDB2DC entries to HTLOGS view
• In addition to TLDSs • Easier to view longer time periods
– MVDB2/DC hyperlinks also allow filtering
• Time, Plan, Auth ID, CorrID, etc. for threads • Also Package, Collection ID for packages
HTLOGS View – Access to Thread History
Hyperlinks
(c)
(d)
(a)
(b)
Can sort the view on any column to find the trace log you want - Most current are at the top
Workload Analysis / Hyperlink on #Intvls (a)
› Summary views by interval and connect type
– The same from a trace or MVDB2/DC – First view, HTDTLZ, summarizes the data
• Key activity indicators • Exception counts by type
– Then drill down to
• Hourly summaries • Interval summaries • Activity by Connect type
Detail Thread Data / Hyperlink on #Records (b)
› Detail data from any MVDB2 TLDS
– Direct access to LTRAC for that trace log – Like the BBI2 History Traces menu option – Then use the buttons to analyze the data
• For example:
– Summarize by plan – Sort by CPU descending – Drill down to LTRAC for worst plan
Detail Thread Data / Hyperlink on #Records (b)
› Detail data from MVDB2/DC
– Choose an entry with MVDB2DC as the traceid – Drill down to an hourly summary
• This hyperlinks to a set of Data Collector reports
– Zooms are hyperlinks
• • • • Put the desired option letter where a plus sign is Selects that hourly entry for drilldown Can see a list of each thread (L), with drilldown to details Or a summary by plan, package, other Ids
– And drilldown further
Hourly summary - THACINTV Report
Zoom to Plan Summary ( THACPLAN)
Zoom to Packages (THACPKGS)
R- new V8 package option
Report & Filter by Thread or Package (c / d)
› Reduce data retrieval and processing time
– Report and Filter by Thread
• Hyperlink on Traceid (c) • Hyperlink on Type (d)
– Report and Filter by Package
› A qualification panel allows specification of:
– Data Source – active data sets or archived – Interval – time selection – Qualifier
• • • • Any number of identifiers (AND) Lists of values for one identifier (OR) Wild cards Include / Exclude
Package Filters / Hyperlink on Type (d)
Qualify by Package
* Tip: specify DB2 too
One Package Returned (THACPKGS)
Common Explain (2)
› Common Explain is shared
– Provided by SQL Explorer – Also used by APPTUNE for DB2 – Many capabilities
› Hyperlinks to Explain from MVDB2
• Object information • Recommendations • What-If scenarios (modify SQL text and explain again)
– Current SQL in an active thread – Dynamic SQL in the cache – Menu options - explain a plan, package, DBRM, ad-hoc SQL
THDACTV/ THDDETL Hyperlink to Explain
MVDB2/DC SQL Text Report
Enter X on the “+” to go to Dynamic Explain
Dynamic Explain Entry Panel
Explain from the Dynamic Statement Cache
SCSQL view lists each SQL statement in the cache
SCSQL View- Scrolled Right
To Statement Detail
SCSQLD – Statement Detail
Hyperlink on Token
Stmt Token => MVDB2/DC Explain Panel
Formatted SQL Text – Zoom to Explain
Explain Results
Many options available
System Event Applications (3)
› Individual IFCID events
– MVDB2 views
• Use the API to the Data Collector to obtain the data
– Four are available
• • • • Authorization failures Open / Close events DB2 commands RID pool failures (processing)
– More planned later
EZDEVENT - System Event Traces Menu
Deleted - only capture failures now (too much data)
AUTHFAIL - Authorization Failures
CMDLIST and CMDTEXT - DB2 Commands
DATASETA / DATASETZ - Open/Close
RIDFLLIM / RIDEVD - RID Failures
Alternative to writing DB2 IFCIDs to SMF (4)
› Batch processing input from MVDB2/DC
– Avoid logging DB2 data to SMF – Support logging (for batch) in Data Collector:
• • • • • Accounting (IFCIDs 003 and 239) Statistics (IFCIDs 001 and 002) Audit (IFCIDs 140-145)* Perf (IFCID 225)* * must be enabled
– Logging / archiving in DC Cust / Admin Guides
• Under DOMPLEX Output Groups and DOMBCOPY
– Optional - can disable DC logging if not needed
• IFCID Discard panel in online Admin
Additional Batch Reporting Features (5)
› Run batch from SMF or from archive files
– => Existing DPRSMF procedure
• “Reports from SMF”
– and / or
– => Data Collector reports
• Load data into DB2 tables • New report program DOMBRPT1 • Also supports quick reports from active logs in Data Collector • Use new ACCTG parm to set accounting classes for MVDB2 traces like THRDHIST
– If no DB2 SMF
Performance Reporter
› Data Collector reports
– Accounting / Statistics / Audit reports
• Now have audit reports without loading DB2 tables • Adding other reports as needed
– DB2 DBM1 Storage report now available
– Use for direct reporting without PR extract step – JCL and program in Data Collector libraries (DOMBRPT1) – Documented in PR User Guide – Supports Include/Exclude qualifiers – Supports a “Top 10” plan report sorted by key values
Catalog Manager Browse (6)
› Provides direct catalog table browse access
– Direct hyperlinks from MVDB2 views
• No Catalog Manager password (ACT) required
• If licensed customer
– Catalog Manager recognizes MVDB2 – Browse-only menu available through hyperlink – ACT password is in their load library – Full function menu available through hyperlink
– Up to date catalog information – Supports all catalog tables – Provides access to remote DB2s
• If DDF connections exist between DB2s
* Enter any 2-char object type for list
** Many more objects than RxD2
1) Enter List line command for object type drilldown like “TS” 2) Enter “S” or “D” for detail display
“TS” line command to see TSs in this DB See Help for descriptions / CMD for list
› S or D for object row data
Access to Other DB2 Performance Products (7)
› Where integration adds value
– Move from one DB2 product to another – Exploit product synergies and unique strengths • MainView monitors and measures • Pool Advisor optimizes and advises • OPERTUNE takes action • APPTUNE provide the SQL statement perspective • System Performance solution provides advisor dialogs • Data Collector provides shared SQL Explain
– Based on SQL Explorer
• Catalog Manager provides complete catalog data
MVDB2 to Other Products
› Hyperlinks
– Pool Advisor
• EZDB2 menu hyperlink to Pool Advisor
– APPTUNE
• Active threads view (THDACTV) => plan analysis and SQL information
– Parameter is plan name of selected thread – Hyperlink on CPU%
– Change ZPARMs from MVDB2 views with OPERTUNE