Sometimes developers are faced with problems where compiled static SQL does not provide the required flexibility for demanding requirements, and have no choice but to resort to dynamic SQL. Dynamic SQL is now accessible through a wide range of programming technologies. Dynamic SQL offers flexibility that can simplify the development of complex applications. New apps being developed on distributed platforms only support dynamic SQL (Java, C++, etc., DB2 Connect). ERP applications (SAP, Peoplesoft, Siebel) are implemented with dynamic SQL. Using dynamic SQL is now quite often becoming the norm and not the exception, and without the depth of experience in developing well tuned SQL, solutions are being delivered badly prepared for the high volume they are expected to operate in.
Static SQL normally passes several quality tests before it is forwarded to production. These traditional QA checks are not available for dynamic SQL. This product fills this gap. It provides “quality assurance for dynamic SQL”.
XM4DB2-DSCA works in both directions. It supports DB2 experts with adaptive configuring and system tuning (make the best out of it) and it helps to identify inappropriate SQL and to improve it. XM4DB2-DSCA saves time and efforts in that it pursues the goal of automated detection, filtering and alerting. It is the ultimate tool for Quality Assurance.
Made for Efficiency and Ease of Use
DSCA provides intelligent filters to separate the wheat from the chaff. Users specify the desired values of the quality criteria that shall be applied and the product, in the background, observes, measures and introduces analysis.
Statements that show unacceptable behavior are immediately recognized and reported via Workstation, ISPF and email. Findings and measurement data are kept in a DB2 database and used to show the chronological sequence of performance degradations to ease the correlation of cause and effect. It is for example helpful to know that PTFs were applied right before an access path toggled, or that maintenance or other major changes occurred, as significant load change, or new applications deployed.
DSCA not only introduces analysis of the DSC, it also recommends improvement and remedy measures: ‘Run Runstats, introduce new indexes, try to use parameter markers and the like.’ Automatic reaction can be defined (Remedy JOB).
The results of the reiterated measurements of every monitored DB2 system of the entire enterprise are stored in a single DB2 database. This central archive allows to keep and to visualize the entire progression (chronological sequence):
• to recognize trends
• to correlate cause and effect
• to determine when exactly an access path toggled
A particular strength of DSCA is its embedding in XM4DB2, hence the entire infrastructure of XM4DB2 is available: one observer on each LPAR, TCP/IP communication between XM Director, ISPF interface, exception list under ISPF, ability to submit remedy jobs.
DSCA comprises of a graphical user interface.
Each recurrence of a statement or statement group is uniquely identified by DSCA’s tagging technique (hash key). It is crucial to be able to recognize statements of the same form. For example, a single statement with modest resource consumption is normally out of focus. We shall judge it different, if we detect that hundreds of these are in the DSC and that all together consume a significant amount of resources.
Confident identification of same or alike statements is a prerequisite. Only then can we recognize trends and access path changes, can we display the chronological sequence (progress) of values like CPU time usage, Lock Wait times/ratios, elapsed time for certain statements or statement groups. DSCA reports adverse SQL statements based on its unique identification and filter technique.
The product filters automatically for statements or statement groups that perform insufficiently, consume excessive resources – CPU time, IO operations – and obstruct throughput. For example
• Inefficient Search due to missing or inappropriate indexes - self explaining, appropriate index(es) would help to save resources and accelerate processing
• DSC Trashing due to missing parameter markers – too many fully specified statements of the same type flood the dynamic statement cache (DSC) because parameter markers where not used to code WHERE clauses
• Obsolete Runstats, there is a significant deviation between Optimizer’s calculation and the products actual own measurement, the user can specify how significant the deviation must be to invoke an email alert (to raise an exception).
• Negative Access Path Change (static and dynamic): Access Path Changes are automatically detected and reported by email (optional)
DSCA presents SQL statements that reached or exceeded maximum values. DSCA can raise exceptions (send email alert) when certain maximum values or combination of values are exceeded. The values can be set and changed by the user. DSCA displays lists of programs and users that are on top in resource consumption. DSCA monitors lock contention. DSCA allows users to define specific reports, for example:
• Display SQL of programs ABC* producing Ridlist Errors and needed more than 10 CPU seconds.
• Display SQL of userid TECABC01 with an access path change within period FROM .. TO and increase in optimizer estimation of over 10%