Customizing the Explain statement used

The Explain Plan feature of AQT uses a particular command for performing the Explain. For instance:

explain plan set queryno=678 for <sql statement>

The explain statement that AQT uses is hard-coded within AQT. However you can override this with the explain specification in the cfg file.

This is useful for DB2 for z/OS, for which you can specify that Explains are to be done using a stored procedure. In this case, you would code:

explain;{call DSN8.DSN8EXP (?,?,?,?,?,?,?,?)};in,char(8),<schema>;in,integer(4),<queryno>;in,varchar(32700),<sql>;in,char(1),Y;in,char(8),<schema>;out,integer(4),,<rc>;out,char(5);out,varchar(960),,<errtext>;

When this is coded, clicking on Run > Explain from the Run SQL window will invoke DSN8.DSN8EXP to perform the explain.

Note that in this example:

• the semicolon delimited options after the call statement give the parms to be passed to/from the DSN8EXP stored procedure.
• where <queryno> is specified, the unique query number will be substituted
• where <sql> is specified, the sql text will be substituted
• where <schema> is specified, the explain schema will be substituted. The explain schema is given in Options > Explain.
• where <user> is specified, your signed-on user-id will be substituted.
• <errtext> specifies the error text that will be returned to AQT.