Sample SQL Assurance report (AS$RSLT)

The BMC AMI SQL Assurance for Db2 report provides information about the SQL statements with violations. When you execute a job with SQL Assurance, it generates job output with the AS$RSLT DD, also known as the SQL Assurance report. The report displays:

Explain parameters
Explain report
SQL statement violation list
Summary Report

Sample SQL Assurance report (AS$RSLT)

**********************************************************************
*                                                                    *
*                  BMC AMI SQL ASSURANCE FOR DB2                     *
*                     V13.01.00.0001.JENKINS                         *
*                      SQL ASSURANCE REPORT                          *
*                                                                    *
*                   02/17/2022 09:33:36.2363                         *
*                                                                    *
**********************************************************************

**********************************************************************

  Explain Parameters:
    Rules Dsn:       AMA.COB1201.BMCSAMP(COBDFLT)
    SSID/Location:   DEJM
    Object:          PACKAGE
    Name:            CROSJOIN
    Collid:          RDAJZL
    Version:         %

**********************************************************************

************************************************************
Explain report for Package:
   Collid:   RDAJZL
   Name:     CROSJOIN
   Version: V1.02
   Contoken: 19B85D8811A36275
************************************************************

Statement:
   Stmtno:     383
   StmtHash:   80E35B19
   SQLText:    DECLARE CRSJOIN1 CURSOR FOR SELECT PART , SUPPLIER , PARTS .

  BMC184022S-This query joins two or more tables and references at least one table without a local predicate.
  BMC184243S-The table has statistics but one of its indexes is missing statistics. Run RUNSTATS on index RDAJZL.PRODIX2.

Statement:
   Stmtno:     389
   StmtHash:   7E7D4085
   SQLText:    DECLARE CRSJOIN2 CURSOR FOR SELECT PART , SUPPLIER , COALESC

  BMC184022S-This query joins two or more tables and references at least one table without a local predicate.
  BMC184243S-The table has statistics but one of its indexes is missing statistics. Run RUNSTATS on index RDAJZL.PRODIX2.

Statement:
   Stmtno:     397
   StmtHash:   7C02605A
   SQLText:    DECLARE CRSJOIN3 CURSOR FOR SELECT PART , SUPPLIER , PRODNUM

  BMC184022S-This query joins two or more tables and references at least one table without a local predicate.
  BMC184243S-The table has statistics but one of its indexes is missing statistics. Run RUNSTATS on index RDAJZL.PRODIX2.

Statement:
   Stmtno:     405
   StmtHash:   D8A80D7E
   SQLText:    DECLARE CRSJOIN4 CURSOR FOR SELECT PARTS . * , PROJ . * , PR

SQL error encountered during the EXPLAIN process:
DSNT408I SQLCODE = -206, ERROR: PROJ.* IS NOT VALID IN THE CONTEXT WHE

************************************************************

                  Summary Report

Packages processed:                     1
Statements processed:                   4
Violations encountered:                 6
Violations ignored:                     0
Common Explain SQL errors:              1

************************************************************

This section of the SQL Assurance report provides the following information:

Explain parameters

The Explain parameters are inputs used to analyze objects or SQL statements for violations in Static and Dynamic SQL:

Parameter name	Analyze process	Description
Collid	Static SQL	The collection ID for the package used in Explain.
Name		The Name of the package or plan used in Explain. Important For Dynamic SQL, NAME= is %.
Version		The version value associated with a package used in Explain.
Object	Static and Dynamic SQL	The object type to be analyzed. This can be PLAN or PACKAGE.
Rules Dsn		The data set and member for the expert rules used by SQL Assurance.
SSID/Location		The Db2 subsystem on which the Extraction (only for Dynamic SQL) and the Explain are performed.

Explain report

The Explain report provides information about the Collection ID, Name, Version, and Contoken.

Important

This report section is displayed for Static SQL only.

SQL statement violation list

SQL statement	Analyze process	Description
Stmtno	Static SQL	The SQL statement number.
StmtHash	Static and Dynamic SQL	The SQL statement hash value. Important SQL Assurance uses the hash qualifier to uniquely identify the SQL statement.
SQLText		The first 50 bytes of the SQL statement text.
Violation		The expert rule text on the violated SQL statement.
SQL Error		The first 50 bytes of the SQL error text for the SQL statement.
Parse Error		The first 50 bytes of the Parse error text for the SQL statement.

Summary report

The Summary report provides information about the counts for the SQL statements.

Type	Description
Packages processed	The total number of packages.
Statements processed	The total number of SQL statements.
Violations encountered	The total number of violations.
Violations ignored	The total number of violations that were previously ignored.
BMC Explain SQL errors	The total number of SQL errors from BMC Explain.
BMC Explain Parse errors	The total number of Parse errors from BMC Explain.

Sample SQL Assurance report (AS$RSLT)