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
You can use the SQL Assurance report to:
- View the SQL statements and the associated violations.
- Determine the corrective action and change the SQL statements.
- Rerun the job, ensure that the violations are removed and then promote the SQL statements.
You can view this report either in the job output on the mainframe or in your workspace directory.
Sample SQL Assurance report (AS$RSLT)
* *
* BMC AMI SQL ASSURANCE FOR DB2 *
* V12.01.00.0001.23.JENKINS *
* SQL ASSURANCE REPORT *
* *
* 08/26/2020 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. | |
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.
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. |
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. |
Related topics