Limited supportBMC provides limited support for this version of the product. As a result, BMC no longer accepts comments in this space. If you encounter problems with the product version or the space, contact BMC Support.BMC recommends upgrading to the latest version of the product. To see documentation for that version, see BMC AMI SQL Explorer for Db2 13.1.

SQL statement matches and match percentages

The Compare Explain History function uses an SQL parser that breaks the SQL statement into lines.

Each major or subordinate clause of the SQL statement begins a new line. Subordinate keywords (like AND or a column name in the first clause) also begin a new line.

Each line of the first SQL statement is checked against a line from the corresponding SQL statement of the second object. For example, the FROM clause of the first object is compared to the FROM clause of the second object. Any differences that are found affect the matching percentage. If the matching percentage is within the threshold in the MATCHPCT parameter, the two statements are compared. The product performs matching by using a top-down method in which all SQL statements matching at 100% are done, then 99%, then 98%, and so forth.

In the following example, the SQL statements matched at 50%:

SELECT EMPNO ,
+      ,FIRSTNME
-      ,MIDINIT
 ,LASTNAME
+      ,MIDINIT
-      ,FIRSTNME
 FROM   IOD.EMP
-WHERE  (EMPNO = ?)
-  AND  (LASTNAME = ?)
 ORDER  BY EMPNO
+OPTIMIZE FOR 5 ROW

The formula for calculating the matching percentage per statement is:

(# matching lines)  * 100 / (# lines from either statement, whichever is greater)

In this example, the number of matching lines is 4. The number of lines from the first object is 7 and from the second object is 8. The maximum number (8) is used as follows: 4 * 100 / 8 = 50%.

Related topic

Comparing-Explain-history




 

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