DB/IQ – Quality Assurance

DB/IQ – Quality Assurance

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

Ensure the quality and performance of SQL Db2 applications

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.
  • Alerts and possibly rejects 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 “BIND PACKAGE” function alleviate the complexity. The auto-versioning however, caused Db2 Catalogs to grow exponentially to an unmanageable state where very large Explain tables slow 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

Ensure the quality and performance of SQL Db2 applications

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.

  • Diagnoses the cause of performance issues by identifying the most consuming queries
  • Monitors all or just SQL activities of named applications
  • Measures the evolution of quality over a period of time
  • Automatically compares the quality of a SQL query with its previous version
  • Automatically assesses 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.

  • Anticipates 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
  • Manipulates 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.

  • Examines 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
  • Weight the importance of each application either automatically according to its execution frequency or manually according to a Business-Value set by user.
  • Assesses the relevance of existing indexes
  • Identifies potential index key column candidates automatically

Analyzes index impact, showing which index application and SQL profit most and if any deteriorate from the new index

Want to know more? Contact us!