Ensure the Quality and Performance of SQL Db2 Applications
Check that the SQL Db2 applications comply with your quality rules
Assess whether the data access paths are efficient
Determine the best indexing strategies
Control the execution of Db2 queries, even dynamically
DB/IQ is a set of solutions to guarantee the quality and performance of your SQL queries, whether static or dynamic, while reducing the load on your z/OS environment.
The DB/IQ suite includes the following modules: DB/IQ QA, QA+, PackMan, IA+, MA+, WL and the Eclipse plugin.
DB/IQ QA: Quality Assurance
The DB/IQ Quality Assurance solution analyses and checks the entire SQL code, whether it is in source form, DBRM resulting from the Db2 preprocessor, SQL based on the catalogue (plans, packages, views, MQTs, triggers, etc.), or dynamically executed SQL.
DB/IQ QA provides advanced SQL code control through the following features:
- Allows the quality of all SQL code to be verified according to the standards defined by the organisation
- Over 370 quality rules are available ‘off the shelf’ and can be customised to your requirements and service level agreements
- Provides developers with interactive quality control and a comprehensive EXPLAIN (explanation) feature for all SQL statements
- Reports and possibly rejects programs that violate the quality rules
- The checks can be triggered on demand by developers, directly in their mainframe or Eclipse development environments, and automatically in batch mode during delivery procedures.
DB/IQ QA+: Optimisation of dynamic SQL queries
The DB/IQ QA+ solution analyses dynamic SQL queries in depth by evaluating their cost, joins, sorts, access paths, execution time, CPU usage and number of GETPAGEs. It identifies the most resource-intensive queries in order to diagnose performance problems and monitors the SQL activity of critical applications in real time. By measuring the evolution of query quality over a given period, it makes it possible to anticipate the impact of Db2 updates on execution paths. Thanks to an automatic comparison of performance between different versions of a query, DB/IQ QA+ guarantees continuous optimisation and increased application stability.
DB/IQ PM: Application Package Management
The DB/IQ Package Management for Db2 solution addresses the issue of the constant evolution of Db2 and the numerous daily changes. In fact, the management of static links has become increasingly complex. To alleviate this constraint, the BIND PACKAGE function was introduced to automate version management. However, this approach has led to an exponential growth of Db2 catalogues, making their management difficult and slowing down BIND processes due to overloaded Explain tables.
DB/IQ PM simplifies this management by identifying and deleting unused packages, then reorganising the Db2 catalogue. The result is a leaner, more structured and optimised catalogue, free of redundant packages for more efficient applications.
DB/IQ WL: Advanced data volume simulation
The DB/IQ WorkLoad Detector solution anticipates application behaviour by simulating data volumes through the manipulation of RUNSTATS values. It allows you to reproduce production conditions in qualification and development environments, anticipate the impact of an increase in data and generate statistics adapted to new projects to optimise performance from the start.
DB/IQ IA: Advanced Index Optimisation
The DB/IQ Index Administrator solution analyses all SQL activity in depth to assess the relevance of existing indexes on Db2 tables and identify optimisations by suggesting new key columns for more efficient indexes.
- Exhaustive analysis of SQL: takes into account all SQL queries associated with a table, whether static SQL from the Db2 catalogue or dynamic SQL extracted from traces or the Db2 cache.
- Application weighting: automatically prioritises the importance of applications according to their frequency of execution or allows manual weighting based on business criteria defined by the user.
- Evaluation and recommendations: examines the effectiveness of existing indexes and identifies the optimal columns for new indexes, thus guaranteeing increased performance.
Thanks to these analyses, DB/IQ IA allows for a precise evaluation of the impact of the proposed indexes by identifying the applications and SQL queries that benefit the most, while detecting performance regressions related to the new indexes.
DB/IQ QA - Eclipse plug-in: Integrated Quality Assurance for SQL Development under Db2
The DB/IQ QA-E solution is an Eclipse plug-in designed to ensure quality control of SQL developments in a Db2 environment. Fully compatible with RDz®, it enables standardisation of development practices outside the mainframe. It offers interactive quality control thanks to more than 300 validation rules, as well as a detailed EXPLAIN functionality, including support for hidden EXPLAIN tables. Developers can thus analyse Db2 access paths in real time, measure the impact of data volumes and scans, and easily compare SQL performance. By integrating these features directly into Eclipse, DB/IQ QA-E reinforces the mastery of SQL best practices and improves the quality of developments without leaving the IDE environment.
In short!
- Ensures the application of the standards and guidelines in force for a Db2 application park.
- Identifies and reports SQL queries that are problematic in order to anticipate performance and reliability risks.
- Guarantees the compliance of the SQL code delivered in production, whether static or dynamic, by ensuring that quality standards are met.
- Supports Db2 developers in the optimisation of their programmes thanks to customisable help messages and detailed performance indicators.
- Reduces the workload of application managers and DBAs by automating the control of SQL orders throughout the application life cycle.
- Compatible with DevOps practices, it incorporates auditing tools that enable regular assessment of application quality and rigorous monitoring of their development.
- Benefit from the support and expertise of Infotel, a leader in mainframe data management and optimisation, with high-level technical support.
