Database Management Solutions

Ensure the Quality and Performance of SQL Db2 Applications

Check that SQL Db2 Applications Respect Your Quality Rules

Evaluate Whether the Data Access Paths are Efficient

Find the Best Index Strategies

Monitor the Execution of Db2 Queries

DB/IQ is a software solution designed to assess and improve the level of quality and maintainability of Db2 for z/OS applications before they get deployed in your production environment. DB/IQ automates the quality control process according to performance, maintainability, and scalability criteria.

Quality checks

DB/IQ analyzes and performs checks on all SQL code; whether in source form, in a DBRM resulting from the Db2 pre-processor, in all Catalog-based SQL (plans, packages, views, MQTs, triggers etc.), or as dynamically executed SQL.
  • Check the quality of any SQL code according to organization-defined standards
  • More than 350 quality rules are available “out-of-the-box” and can be customized according to your requirements and SLAs
  • Provide developers interactive quality control and full EXPLAIN functionality for all SQL statements
  • Alert and possibly reject programs that violate quality rules
  • Controls can be triggered on demand by developers, directly in their mainframe or Eclipse development environments, and automatically in batch mode during delivery procedures
 

Package Management

DB/IQ Package Management for Db2 (PM) – Early Db2 installations implemented the BIND strategy for plans and associated DBRMs with static linking of all applications. With so many daily Db2 changes, the static linking created great complexity for the Catalog, and auto-versioning was put in place with the “BIND PACKAGE” function to alleviate the complexity. The auto-versioning however, caused Db2 Catalogs to grow exponentially to an unmanageable state where very large Explain tables slowed down BINDs. DB/IQ PM helps reduce this complexity by identifying unlinked packages, removing them, then reorganizing the Db2 Catalog. The result is a cleaner Db2 Catalog, free of redundant packages, and streamlined for better application performance.

  • Cleans up the Db2 Catalog by discarding non-required packages – even in Change Management Systems – delivering streamlined application performance for better EUX
  • Reduces mainframe budget churn
  • Assists DBAs to easily build jobs and keep a cleaner, more efficient Db2 Catalog
  • Minimizes all general commonly used gigantic Explain tables
  • Avoids all -805/-818 (timestamp mismatch) problems by locating inconsistencies between DBRMs packages and corresponding load modules

Audit Db2 applications

DB/IQ benchmarks all SQL statements, whether they are static or dynamic, according to cost factors, joins, sorts, access patch, elapse time, CPU, number of GETPAGEs, and much more.

  • Diagnose the cause of performance issues by identifying the most consuming queries
  • Monitor all or just SQL activities of named applications
  • Measure the evolution of quality over a period of time
  • Automatically compare the quality of an SQL query with its previous version
  • Automatically assess the impact of Db2 release upgrades on your application paths

 

Simulate data volume

DB/IQ simulates data volumes present in your database tables by manipulating RUNSTATS values responsible for Db2’s access path selection.
  • Anticipate the behavior of your applications without having the production data volumes in the tables
  • Copy the RUNSTATS values from your production environments to your qualification and development environment
  • Manipulate RUNSTATS values to anticipate an increase in volumes
For new projects, generates RUNSTATS values from scratch based on your volume estimates.

Index optimization

DB/IQ analyses the entire SQL activity to evaluate if existing indexes on Db2 tables are relevant and if other columns would make more efficient indexes.
  • Examine all SQL related to a table, whether it is static SQL found in the Db2 catalog or dynamic SQL extracted from traces or the Db2 cache
  • Weigh the importance of each application either automatically according to its execution frequency or manually according to a Business-Value set by the user
  • Assess the relevance of existing indexes
  • Identify potential index key column candidates automatically
Analyze index impact, showing which index application and SQL profit most and if any deteriorate from the new index.

Benefits

  • Eradicates poor performing SQL
  • Establishes standards and guidelines for all Db2 applications, regardless of origin
  • Ensures 100% of SQL code delivered in production is compliant with your quality standard
  • Detects reliability and performance problems before they occur in production
  • Increases developers’ awareness by explaining the mechanisms of the Db2 optimizer
  • Reduces the size of your pre-production environments
  • Frees DBA time by automating code control task