The sample exit routine is named RAARXQA and it performs several operations on the raw analysis data as received from the analysis engine. Refer to the comments within these exits for details on the processes and for a list of the incoming parameters, the names of the variables, and their meanings.
To cancel a query, set the CANFLG variable to the character string CANCEL. Any other value allows the query to continue running. For example, if a table is being accessed at a time of day when it is restricted, the exit routine can set this flag, as described in Step 5, "Call Exit Routine," to cancel the query.
To pass the variable value back to Query Analyzer, the exit routine must VPUT the variable into the shared variable pool.
ISPEXEC VPUT (CANFLG) SHARED
You can add threshold logic, such as, logic that will cancel a query if more than one table is accessed and no indexes are used.
The exit routine can pass back the number of rows to fetch before canceling the query. This allows each user the ability to minimize resource consumption by limiting the number of rows fetched for each query. To pass the variable value back to Query Analyzer, the exit routine must also VPUT the variable into the shared variable pool.
ISPEXEC VPUT (AAQARM) SHARED
The exit routine must VPUT all variables that will be displayed on the output panel. In the sample exit, the variables QAP1 through QAP29 are VPUT to the shared pool.
ISPEXEC VPUT (QAP1 , QAP2 , QAP3 , QAP4 , QAP5 , QAP6 , QAP7 , QAP8 , QAP9 , QAP10, QAP11, QAP12, QAP13, QAP14, QAP15, QAP16, QAP17, QAP18, QAP19) SHARED ISPEXEC VPUT (QAP20, QAP21, QAP22, QAP23, QAP24, QAP25, QAP26, QAP27, QAP28, QAP29) SHARED
The analysis output variables and their descriptions are presented in the table below.
Variable | Description |
QAP1 | Number of Query blocks |
QAP2 | Number of Tables Referenced |
QAP3 | Join Methods |
QAP4 | Access Types |
QAP5 | Number of Index Only scans |
QAP6 | DB2 Sorting |
QAP7 | Parallelism Modes |
QAP8 | Highest Access Degree |
QAP9 | Number of Page Range Screens |
QAP10 | Join Types |
QAP11 | Prefetch Types |
QAP12 | Minimum row count |
QAP13 | Maximum row count |
QAP14 | Average row count |
QAP15 | Minimum CPU time |
QAP16 | Maximum CPU time |
QAP17 | Average CPU time |
QAP18 | Number of Matching Index scans |
QAP19 | Column Functions Evaluation Types |
QAP20 | Is access through an index? |
QAP21 | Is access through more than one index? |
Variable | Description |
QAP22 | How many indexes use matching columns? |
QAP23 | Is the query satisfied using only an index? |
QAP24 | Are scans limited to certain partitions? |
QAP25 | What kind of prefetching is done? |
QAP26 | Is data accessed/processed in parallel? |
QAP27 | Are sorts performed? |
QAP28 | Are joins present in query? |
QAP29 | When are column functions evaluated? |