Flexible Monitoring of the Quality of SQL Queries

DB2 InfoScope detects SQL queries that do not comply with quality criteria (performance, maintainability, scalability, portability, etc.) supplied with the product or established by the user based on site standards.

These standards can be defined for a given type of processing (batch or TP), an application, a group of programs, etc. as specified by the administrator.

DB2 InfoScope analyzes the DBRMs of the programs already written or the SQL statements of the programs running.

DB2 InfoScope generates a quality analysis report containing the queries analyzed. It can be displayed for interactive use or printed out for more generalized use in batch mode.

The query is then accepted or rejected. There can be multiple causes:

Analysis and Optimization of the Access Strategy

The access paths selected by DB2 when creating PLANS and/or PACKAGES are a decisive factor in application performance.

DB2 InfoScope analyzes these access paths and checks that the access strategy is consistent with the specified performance targets.This evaluation, made using the PLAN TABLE, is recorded in a detailed report in which each query is identified as accepted or rejected. DB2 InfoScope gives the reason for the evaluation of each query, for instance by reporting a query that consumes excessive resources (Tablespace Scan on more than n pages). DB2 InfoScope supplies optimization recommendations.

Principles of Use of DB2 InfoScope

The main functions include:

• Analysis of the queries entered
• Analysis of a source program under ISPF
• DBRM analysis
• Interception of BINDS and REBINDS
• Plan/Collection/Package analysis
• Table analysis
• Simulation.