InfoScope

Flexible Monitoring of the Quality of SQL Queries

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.

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

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.

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. 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). InfoScope supplies optimization recommendations.

Principles of Use of 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.
InfoScope

Anticipate Future Data Volumes

Validation of the Plan or Package access paths is especially meaningful if made with the real volume of data, i.e. the data of the production DB2 environment.

InfoSimulation can be used in conjunction with InfoScope to:

  • Easily import the volumetric data of the production DB2
  • Simulate the future volume
  • Update the DB2 catalog of the development environment to validate the access paths of the Plans or Packages.

InfoSimulation used interactively helps the user by calculating new variants based on imported or created data to simulate different situations.

Automatic Quality Control

  • InfoScope is easily integrated in BIND and REBIND procedures
  • InfoScope checks all the queries contained in the plan or package
  • This automatic check guarantees that no nonconformant plans or packages can be placed in production.