Diagnosing the WHERE clause

BMC provides a diagnostic trace function in the WHERE clause that you can use to resolve problems related to WHERE processing.

Warning

Only use the trace function when directed to so by BMC Customer Support.

If you use the TRACE keyword, you must code it immediately after the WHERE keyword and before any predicate.

TRACE.png

Related topics

BMC-AMI-Utilities-SQL-language

IN-predicate

LIKE-predicate

The arguments are defined as follows:

arg1

The first argument (arg1) is required and specifies the level of tracing requested. You can specify one of the following values for arg1:


Value

Description

I

Generates detailed tracing showing the value of each operand in each executed predicate. This level generates one line of output per operand and could produce many lines of output. Use this trace level within a specified range of rows. See examples below.

R

Generates one line showing the row number and the result of the WHERE clause, (TRUE or FALSE).

T

Generates one line showing the number of the row when the result is TRUE.

F

Generates one line showing the number of the row when the result is FALSE.

arg2 and arg3

The second and third arguments (arg2 and arg3), specify the range of rows subject to tracing. These arguments must be numeric values separated by a colon (:).

arg2 is the starting row number and arg3 the ending row number. If neither value is specified, all rows are traced. If only arg2 is specified, then the default for arg3 is 2**31-1.

Example

The following expression traces and prints row numbers between 1 and 1000 whose result is FALSE:

WHERE TRACE(F,1:1000) COL1=COL2 AND COL3=COL4

The following expression prints the entire WHERE logic for row number 17:

WHERE TRACE(I,17:17) COL1=COL2 AND COL3=COL4

 

  • 13.1
Tip: For faster searching, add an asterisk to the end of your partial query. Example: cert*