DSPC (Vendor: Database Solutions, Germany)
URL: http://www.data-base-solutions.com
Dynamic SQL - a Permanent Performance Challenge
With the rapid adoption of distributed and "web-enabled" applications accessing data stored in DB2 databases on the mainframe, many IT organizations find themselves managing rapidly increasing Dynamic SQL workloads that drain already scarce mainframe resources.
True and detailed visibility into Dynamic SQL processing is key for an efficient analysis. DSPC is the primary solution specialized on Dynamic SQL tuning, providing a practical approach.
The Emergence of Dynamic SQL
The widespread deployment of popular ERP and CRM systems, the unpredictability of query technologies such as QMF and SPUFI, and script-based database technologies such as Java JDBC have led to an explosion in the volume of Dynamic SQL processing at most data centres.
Dynamic SQL satisfies the flexibility requirements of today's 24x7 business users but for many companies performance management procedures focused on Dynamic SQL processing are still largely undefined and supporting tools inadequate.
There are several general purpose performance tools that provide effective real-time DB2 monitoring, but most prove to be difficult or cumbersome to use for conducting in-depth problem analysis on Dynamic SQL processing. They either fail to provide sufficient granularity or the overhead cast is too significant to be practical for production use or the data is just not readable for non DB2 experts.
Automated vs. Manual Performance Analysis
- Reduces administrative burden on Technical Analysts and Applications Developers
- Delivers increased CPU savings and delays expensive hardware and software
- Upgrades Improves service levels and increases end-user productivity
- Frees processing resources for business growth
- Aligns resource consumption with business-oriented service priorities
Dynamic SQL Performance Control
DSPC enables DNA's, performance specialists, and application developers to optimize large, high-volume Dynamic SQL workloads. DSPC allows analysts to track D-SQL performance for targeted intervals or historically over extended periods of time to isolate inefficient D-SQL calls that waste DB2 system resources. By combining a system-wide view with statement-level granularity, DSPC provides an easy-to-use mechanism for getting to the roots of D-SQL performance problems quickly, within single DB2 systems, but also from a single point of control for complete DB2 groups.
DSPC Report Highlights (extract)
- Review high impact statements by CPU, response, call frequency, getpages, index scans, etc.
- Aggregate similar statements with different ones due to dynamic specifications
- Analyze/Compare: Avg/Tot/Elapsed/CPU time, per user, client program, transaction, table
- All reports per time period
- All reports per user-defined DB2 groups
Cache Extracts Statistics
- Original statement text
- Normalized statement text (Explain format)
- Number of statement executions during interval
- Get Page Count (total and average)
- Used CPU and Elapsed time (total and average)
- Number of sorts (total and average)
- TS-/IX-Scans (total and average)
- Rows Examined, Rows Processed (total and average)
- Package Name
- Performing client, program
- Calculation of the average consumption per statement
- Data histories