DB/IQ WL+ WorkLoad Detector is an “add on” to the DB/IQ QA product and helps DBAs and developers analyze complete applications and reduce their “workload”. This form of application tuning enhances the QA base product by processing captured trace data, previously extracted by QA’s SQL Monitor and Extract facility. The captured trace data reflects the actual SQL executed – whether dynamic or static, its cost, CPU and elapsed time, execution counts, buffer activity, rows processed and more … Together with the exact sequence in which all SQL was processed, thresholds may be set, in order to nominate statements, packages or plans violating suggested limits.
With an explosion of packaged solutions, e.g. SAP/R3® and PeopleSoft® etc., and their many-thousands of tables exploited by dynamic SQL, the ability of the performance specialist to effectively tune the application or SQL becomes virtually impossible. Packaged vendors will always endeavor to provide a reasonable set of indexes for these products, but each site’s individual implementation and use of these tools can be quite different and thus require alternative indexes and very often far fewer. Such analysis is a time-consuming and daunting task since it requires you to review individual SQL of an application for predicate usage and simultaneously take business priorities into account. Administration and development teams often lack the specific information they require to make such critical decisions.
DB/IQ WL+ WorkLoad Detector provides a fast and easy solution to identify which packages and SQL are responsible for heavy workloads. Once identified all index tuning can be assessed by DB/IQ IA+ Index Administrator and SQL statement tuning established with the DB/IQ base product QA.
Following the SQL Monitor extract facility, the WL+ dialog assists in filtering the data to display or report on critical packages and SQL. All WL+ functions may be run in batch or ISPF foreground.
Filtering may be individually set to process certain plans, packages or single SQL statements with minimum resource limits. These limits include number of times a SQL or package was executed, elapse time, CPU time, rows processed, stage I or II rows processed, pages retrieved and more …
For example, WL+ will locate packages and / or SQL statements with the highest :
- total, average or single DB2 elapse time and CPU time
- total, average or single number of scanned pages, execution counts
Threshold values may be set within QA’s “Rule Maintenance” and include W-rules to limit :
- total, average and max. Getpage activities per plan, package or single statement
- total, average and max. DB2 CPU and / or Elapse times per plan, package or single statement
- total, average and max. Cost factors per plan, package or single statement
Plus additional checks to restrict :
- no. of cursors “opened” simultaneously , no. of times a single cursor may be OPENed or FETCHed
- no. of inserts, updates and deletes executed without a COMMIT as number or percentage and more …
A vital function within the WorkLoad Detector is the List Dynamic SQL feature. This locates all similar dynamic statements found within the selected trace and groups them into “like” SQL. In this case, “like” SQL, means all literals found within the predicates, have been neutralized to build groups of the same SQL. Thus, DBAs and developers now see the client-server or Web based applications with similar predicates, possibly the cause of over-refreshing or contention within the buffer pools.
Now, DB/IQ WL+ facilities offer the means to enhance QA consistently and impartially by focusing on the complete application and workload with all SQL executed.
Furthermore, the SQL Monitor captures all SQL regardless of origin, both static and dynamic SQL, including QMF ® or ERP packages. Using such extracts, DB/IQ WL+ can easily identify costly SQL belonging to ERP packages for purchase quality verification.