Chat now with support
Chat with Support

SQL Optimizer for DB2 LUW 4.4.1 - Release Notes

SQL Classification Settings

ClosedView SQL Classification tab options

The SQL Classification tab on the Options window allows users to specify the criteria to analyze a SQL statement. If the SQL statement analyzed satisfies any of the settings below, it will either be classified as Complex or Problematic SQL. Problematic SQL statement indicates potential problematic SQL statement and should be optimized, while complex SQL statements are complicated SQL statements where there is room for improvement.  

The following settings are used to set the criteria for Simple, Complex and Problematic SQL statements.

Simple SQL

Simple SQL Criteria

Description

Number of table scan operations less than

(Default = 2)

Read-only field indicating the number of table scan operations references in the access plan. If the total number of table scan operations is less than this value, then this SQL statement will be classified as Simple. This value is the same as the lower limit of the complex table scan operations range.

Complex SQL

Complex SQL Criteria

Description

Number of table scan operations (Default = 2 /3, Range 2 to 99)

Specify the range of the number of table scan operations referenced in the access plan for Complex SQL statements.

Including OLD_TABLE simulation temp table

Specify to count the OLD_TABLE simulation temp table created in Trigger Conversion.  

Including NEW_TABLE simulation temp table

Specify to count the NEW_TABLE simulation temp table created in Trigger Conversion.  

Problematic SQL

Problematic SQL Criteria

Description

Number of table scan operations greater than (Default = 3)

Read-only field indicating the number of table scan operations references in the access plan. If the total number of table scan operation is greater than this value, then this SQL statement will be classified as Problematic. This value is the same as the upper limit of the complex table scan operations range.

With full table scan

Specify whether SQL statements with a single table full scan with table size greater than or equal to the defined page size (in Ketose) will be classified as Problematic SQL statements.

With full table scan iterated by nested loop

Specify whether SQL statements with a full table scan inside a nested loop are classified as Problematic SQL statements. This classification depends upon the number of pages in a table.

Number of pages (available only if statistics are gathered) (Default = 1, Range= 1 to 9999996)

Specify the number of pages in the table.

Including OLD_TABLE simulation temp table

Specify to count the OLD_TABLE simulation temp table created in Trigger Conversion.  

Including NEW_TABLE simulation temp table

Specify to count the NEW_TABLE simulation temp table created in Trigger Conversion.  

 

Related Topic

Related Documents