Hidden SQL/MP Features

1) SELECT $ZZNORM ON; When you enter this command on the sqlci prompt sqlci will return an error. But after the errorsqlci will show you how it translates your queries.


Example:

>>select $zznorm on;
*** ERROR from SQL [-2014]: The SQL compiler was called in test mode.
This *** error is used to exit the compiler without further SQL processing.

>>select * from auditlog
>>where user_id BETWEEN "A" AND "ZZZZZZZZ"
>>browse access;
SELECT TIME_STAMP , USER_ID , MENU_ACTION
FROM \TDMDEV.$TST03.SQLSUBV.AUDITLOG
WHERE ( USER_ID >= "A" ) AND ( USER_ID <= "ZZZZZZZZ" ) FOR BROWSE ACCESS --- 0 row(s) selected. >>

2) add define =_sql_cmp_explain_cat, class map, file $wrk0.wietse.k;

After the defines was added the explain plan will display more information. It will return all the catalog information the compiler has used for its descission.
This information includes:-
Primary key information
- All index information-
Uec, high and low values for each column-
And much more