Advanced SQL Features

Explain Plan

AQT can display the Explain Plan for an SQL statement. The Explain Plan shows you information about the way the database will process your SQL statement. This information is useful for analysing and improving the performance of your queries.

To show the Explain Plan, in the Run SQL window, select Run > Explain SQL or hit F8. If you wish to see your existing Explain Plans, without Explaining the SQL, select Window >Explain Plan.

This feature is available for Oracle, DB2 z/OS, DB2/UDB, SQL Server, Sybase, MySQL, IDMS and Teradata.

For MySQL and SQL Server, the Explain-plan information is shown as a normal query display.

For Sybase, the Explain-plan information is shown in a pop-up box. The Sybase ODBC Driver returns the explain data to AQT in a strange order. This makes the display difficult to use, especially if you have a complex query. There is little we can do to correct this issue.

Explain Plan for Oracle and DB2

For Oracle and DB2 a special Explain Plan window is displayed.

Plan / Explain Tables needed

For some databases, in order to use the Explain Plan feature, you will need to have Plan or Explain tables set up for you. If you do not have these you will get the message PLAN_TABLE not found (or similar) when you try to Explain your SQL.

To create the Plan / Explain tables, contact your DBA.

• For DB2/UDB, the DDL for the Explain tables are in %sqllib%\misc\explain.ddl.

• If you are unable to locate the Explain table DDL, email support and we can send you DDL for the plan/explain tables. Please tell us what database type and database version you are using - otherwise we'll just have to email you back and ask you. ;)

DB2/UDB and SYSTOOLS

For DB2/UDB, explain tables can be written to the SYSTOOLS schema rather than your own schema. DB2 will automatically use the SYSTOOLS explain tables if they do not exist under your userid.

Similarly, if AQT finds the explain tables under your userid, it will use them in the explain displays. Otherwise it will show information from the SYSTOOLS explain tables.

Explain Plan window

The Explain Plan window contains the following:

Plan

The combo-box and buttons allow you to select the plan you wish to display. By default the most recent plan is shown, however you may select another plan.

Only plans generated by AQT will be seen here.

Plan Identifier

AQT will compose a unique identifier for the Plan when it does an Explain Plan.

• For Oracle this is aqt plus a timestamp – example aqt20020526155411.

• For DB2 z/OS this will be a timestamp – example 20020526155411. To distinguish AQT plans from other entries in your plan table, AQT plans use a Queryno of 678.

AQT will not automatically delete plans, but will retain them in your PLAN_TABLE until you choose to delete them.

Delete this Plan

This button will delete the existing plan.

Delete all Plans except this

This button allows you to purge all plans except the one currently shown.

Only plans generated by AQT will be deleted – other plans will be left untouched.

Cost

This will show you the Cost estimate of your SQL statement. This will only be shown for Oracle and DB2/UDB.

For Oracle, this will be blank if Oracle has decided to do rules-based optimization instead of cost-based optimization. Oracle will use rules-based optimization in two circumstances:

• your database has been configured to always use rules-based optimization

• the tables in your query do not have any statistics (statistics are generated when you run an ANALYZE on your tables).

Run DB2 Index Advisor and Run DB2 Explain Tool

These buttons appear for DB2/UDB.

• These functions will only work if the db2advis and db2exfmt programs are installed on your PC and they are in your PATH. These programs are only installed with some types of the DB2 Client and not in others.

• Run Index Advisor and Run Explain Formatter can not be used if you specify Options > User Rights > Disable Save Password. If you try to do so you will get message For security reasons, this function can not be used. See Disable Save Password for more on this.

• You can customize the commands that AQT uses for invoking these functions. You do this by changing the db2advis and db2expl entries at the bottom of the db2udb.cfg file (this file is in your AQT directory).

Plan display grid

The grids in this pane show the information about the Plan. Your database documentation explains the contents of this pane.

AQT has a number of different plan queries that can be shown here – see the section Query below.

The Plan display is shown in two grids:

• the Main grid shows the multiple lines of the explain plan

• the Detail grid which shows the detail of an individual row in the Main grid

As you select entries in the Main grid, the data shown in the Detail grid will change.

The Detail grid can be resized if required, and hidden with View >Detail Grid.

SQL text

AQT will also show you in the bottom pane the SQL statement that the Plan is based on. The SQL text is not present in the Explain_Table; instead, this information comes from the AQT SQL Statement history.

It is possible that the SQL statement cannot be found in the SQL statement history (which contains only the last 200 SQL statements you have run). In this case the SQL text pane will be blank.

For DB2/UDB the Text frame provides three options to determine the SQL display:

• SQL (your SQL query),

• Optimised SQL (the SQL as “rewritten” by the DB2 optimiser)

• Comments (any free-format comments you may have added to your explain). You can add/change any comments by typing them into the text box and clicking on Set.

Query listbox

AQT has a number of different ways to display the Plan Information; select the one you wish to use from this listbox.

These queries are different for different database types; they are specified in the AQT cfg file and so can be added / amended if required. Up to 5 explain-queries can be specified.

DB2/UDB comes with some very good tools for analysing plan information, in particular the Visual Explain tool which is available in the Control Center. AQT’s plan analysis is not designed to replace this tool, but to provide a quick display of basic plan information. If you have a complex query, it is recommended that you use Visual Explain rather than AQT.

DB2/UDB holds plan information in a very complex way that is difficult to display simply. The stream queries can seem very cryptic. A visual representation of this information (such as in Visual Explain, or DB2 Explain Tool) is generally easier to understand.

Explain Plan for DB2/400

This is not possible, as DB2/400 does not have an Explain Plan command that can be issued through SQL.

Instead you will have to use the PRTSQLINF CL command or the iSeries Navigator Visual Explain tool.

More on Explain Plan for DB2 z/OS

By default, AQT runs the Explain with the command explain plan set queryno=<queryno> for <sql> where <sql> is your SQL statement and <queryno> is the your query number (by default 678).

You can customize the command AQT uses for running the explain. This is done with an entry in the cfg file (db2390.cfg) - see the explain entries near the bottom.

Some users prefer to run the explain using the SYSPROC.DSNAEXP stored procedure rather than Explain Plan. SYSPROC.DSNAEXP provides more flexibility, for instance it allows the explain to be written to a generic / shared Plan Table. To implement the use of SYSPROC.DSNAEXP, edit the db2390.cfg file and customize / uncomment one of the explain entries.

If you are using SYSPROC.DSNAEXP, and are using a PLAN_TABLE other than your own, you must also specify to AQT where this PLAN_TABLE resides. This is done with Options > Explain Plan > Plan table Schema. AQT will use this schema in the queries it uses for displaying the Explain information.

More details are provided in Customizing the Explain Statement Used.

Visual Explain

For some databases the Explain Plan information is shown in the Visual Explain window. This provides a method of easily understanding how the database will process your query.

This feature is currently available for DB2/LUW, Oracle, SQL Server and PostreSQL.

It can also be used with DB2 z/OS if the Options > Explain > Use Visual Explain for DB2 z/OS has been selected. Configuration of this feature for DB2 z/OS has not been completed so should be used with caution.

Understanding the Visual Explain Diagram

The elements on the Visual Explain diagram are shown as follows.

K, M and G

Number of rows and costs may be in units of K (1024), M (1024*1024) or G (1024*1024*1024).

Finding out more information on a processing step or table

You can find out more about a particular step (or table/index) by clicking on the item in the diagram.

Navigating around the Visual Explain diagram

The Diagram Controller shows the entire diagram. The box in this allows you to navigate around the diagram.

Pan mode

Another way of navigating around the diagram is to use Pan mode. When this has been activated, you can click on the main diagram and drag it around using the mouse.

Pan mode can be activated with View > Pan mode (Ctrl+M)

Showing the query SQL

For DB2 and Oracle you can see the SQL for the query in the SQL tab.

Extra Features for DB2/LUW

For DB2/LUW you can:

• selecting Optimized SQL will show you the query as the DB2 Optimizer has rewritten it.

• selecting Comments will allow you to add some comments about this plan

Displaying DB2/LUW Explained Packages

Visual Explain can also display the explain information for DB2/LUW packages.

Explaining you Packages

Before you can use this feature, you must first Explain your packages. This is described in section Explaining DB2/LUW Packages.

Invoking Visual Explain

This is done from the SQL window - click on View > Explain Plan.

To see the Explained Packages, click on the Explained Packages tab

Explaining DB2/LUW Packages

You can explain DB2/LUW packages using the following methods.

In all cases, you must have created the Explain tables prior to using these methods.

Explain Stored Procedures when they are created

There are two ways of doing this:

1. run the following command.

   call SET_ROUTINE_OPTS('EXPLAIN ALL')

   This will set the Explain option for the duration of your session.

2. Set the Explain option as a system-wide option

   • open a db2 command window in adminstrator mode

   • run the command:

     db2set DB2_SQLROUTINE_PREPOPTS="EXPLAIN ALL"

   • you must then stop and restart your DB2 server

Once this option has been set, any Stored Procedure which are created will be Explained, and the explain info will be written to your explain tables.

• this option only applies to Stored Procedures. It does not apply to database programs which you bind to the server

• this option only applies to new Stored Procedures and not to existing ones (without dropping / recreating them).

Using SYSPROC.EXPLAIN_FROM_CATALOG

DB2 supplies a Stored Procedure that can be used to create Explain information for an existing Package.

To use, run this Stored Procedure specifying your package / statement as input parms. You need to run this for every section in your package - if your package has a large number of statements this can be quite a big job.

Using AQT Explain Package

AQT has a tool to explain all the statements within a package. This can be invoked as shown below.

This tool uses Stored Procedure SYSPROC.EXPLAIN_FROM_CATALOG. This procedure must be present on your system, and you must have the authority to run it.

AQT will run SYSPROC.EXPLAIN_FROM_CATALOG for every statement in your package. This is a great time saver compared to running this procedure manually.

Explain Timestamp

When the DB2_SQLROUTINE_PREPOPTS method is used, all sections in the package will be given the same explain timestamp. It will appear as a single entry in the explain tables.

When the SYSPROC.EXPLAIN_FROM_CATALOG method is used, each package section is explained separately. Each section will have a different timestamp and will appear as a separate entry in the explain tables. This can make it more difficult to manage these entries in the explain tables.

Running a DB2 Command

For DB2/UDB and DB2 z/OS you can also run utility control statements (such as Runstats, Load etc). These are not “normal” statements so cannot be run through a normal SQL interface; instead AQT will create an instance of the DB2 Command-Line Processor (CLP) and pass the utility control statements to it.

You can run statements in two ways:

• Click on Run > Run as DB2 Command

• Prefix the command with (db2). Example:

  (db2)runstats on table db2admin.department and indexes all shrlevel change

Security Issues

In order to connect to a database through the DB2 CLP, AQT will need to specify the user-id and password in the connection script.

This is a minor security risk. Consequently you can not run a DB2 Command if you specify Options > User Rights > Disable Save Password. When this option is specified, AQT adopts a higher security model, which will not allow this risk.

If you try to do so you will get message For security reasons, this function can not be used. See Disable Save Password for more on this.

Running DB2 Commands in Batch

This can be done by prefixing the command with (db2), as discussed earlier.

DB2 z/OS Commands

DB2 for z/OS has a number of commands such as -DISPLAY DATABASE, BIND etc. These are a different category of commands which cannot be run through the DB2 CLP.

Instead, DB2 for z/OS provides a number of Stored Procedures for running these:

• SYSPROC.ADMIN_COMMAND_DSN can be used for running a BIND, REBIND or FREE

• SYSPROC.ADMIN_COMMAND_DB2 can be used for running other commands

For further information on these commands, consult the DB2 z/OS documentation.

Running a Stored Procedure

The normal method of running a stored procedure is to use the Run Procedure window. However you can also run a stored procedure from the Run SQL window. This is useful in the following circumstances:

• you want to save the stored procedure execution as a saved query

• you wish to run a stored procedure in batch mode

• you want to export the results of a stored procedure to a file

• you are running a Stored Procedure contained inside an Oracle Package.

Copy to SQL Window

From the Run Procedure window, you can use Copy to SQL Window to create an SQL script to run the procedure. This is the recommended method when wishing to run a procedure from the SQL window.

Run Procedure Syntax

The ODBC syntax for running a stored procedure is:

{ call procedure_name () }

When you are running a stored procedure contained inside an Oracle Package, the syntax is:

{ call package_name.procedure_name () }

Procedure Parameters

If the stored procedure has input parameters only, you can run it with either of the following formats

{ call procedure_name ('Smith',23) }

or

{ call procedure_name (?,?) }

(and enter the parameters when prompted)

or

--aqt parms,parmno=1,value=Smith

--aqt parms,parmno=2,value=23

{ call procedure_name (?,?) }

The latter format is the recommended format, as most database prefer having the procedure parameters passed as query parameters. Some databases have trouble processing the first of these formats.

OUT and INOUT Parameters

If the stored procedure has either OUT or INOUT parameters, you must run it using the following format:

--aqt parms,parmno=1,parmname=custname,value=Smith

--aqt parms,parmno=2,parmname=procreturn,ptype=out

{ call procedure_name (?,?) }

Notice that when specifying the parameters, you must code a ptype clause if your parameter is either an OUT or INOUT parameter. The procedure will only run successfully if the parameters are specifed with the correct type.

parmname is an optional parameter which you can use to identify the parameter. It is ignored by AQT.

You can generate the correct SQL (and control statements) for running the stored procedure by clicking on the Copy to SQL Window button from the Run Procedure window.

Return Parameters

If you have either OUT or INOUT parameters, the status message at the bottom of the Run SQL window gives the value of these parameters after the procedure has executed. You can also see these with Window > Procedure Parameters (this menu item will only be visible if you have run a procedure with out/inout parameters).

Specifying the Data Type of Parameters

Some databases (for instance DB2 z/OS) also require you to specify the data type of the parameters. This is done by coding the DTYPE clause in the parms statement. Example:

--aqt parms,parmno=1,value=Smith,dtype=character(30)

--aqt parms,parmno=2,ptype=out,dtype="decimal(5,2)"

{ call procedure_name (?,?) }

The dtype value must be enclosed in double-quotes if it contains a comma (as in the above example).

Technical discussion

A technical explanation of ptype and dtype parameters is given below.

The way in which stored procedures are processed (by your database) is quite different to normal queries. For a normal query:

• AQT does a prepare of the SQL

• AQT then does a query parameters. The database returns the number of substitutional parameters which are in the SQL, and the data type of each parameter.

• AQT then "binds" the parameters to the query, and executes the query.

This doesn't work for a stored procedure since:

• the information returned by query parameters does not include the parameter type (IN, OUT, INOUT). However this must be specified correctly when you bind the parameter to the query.

• some databases (eg. DB2 z/OS) do not allow you to do a query parameters when your SQL runs a stored procedure.

• some databases (eg. Oracle) do not return accurate information about the query parameters.

Instead, for stored procedures, AQT needs to obtain information on the parameters by querying the procedure information in your database system tables. This is done when procedures are run from the Run Procedure window. However, this is not done(*) when the procedure is run from the SQL Window, or when the procedure is run in batch mode. In these cases, you need to supply this information by use of the parms statement, and the ptype and dtype clauses.

These clauses will be generated correctly if you use the Copy to SQL Window function of the Run Procedure window.

(*) Why doesn't AQT query the system tables when procedures are run from the SQL window? There are two reasons for this:

• this would add extra system overhead and complexity to the running of stored procedures

• the queries that get the information on stored procedures from the system tables can be unreliable for some databases (in particular Sybase). By coding ptype and dtype parameters, you have control of the values and can circumvent any incorrect information returned by the database.

Running a Teradata Stored Procedure

By default, only a Teradata Macro can be run by AQT. This is due to the way that Teradata treats Macros and Procedures.

If you wish to run a Teradata Procedure, you will needs to:

• on the configuration of the Teradata Datasource:

  • change the Session Character Set to ASCII

  • change the Session Mode to ANSI

  • disable CALL to EXEC Conversion

• on the AQT Run Procedure window, click on Copy to SQL Window

• on the SQL window, remove the curly brackets from around the Call statement.

Running Oracle PL/SQL or SQL Server/Sybase Transact-SQL

The section relates to the running of:

• PL/SQL (Oracle)

• Transact-SQL (SQL Server and Sybase)

In the Run SQL window, you can run a block of SQL code written in these languages. This may include Select / Update statements as well as conditional logic

Example (Oracle):

DECLARE maxsal NUMBER;
BEGIN
  SELECT MAX(SALARY) into maxsal FROM HR.EMPLOYEES;
  IF (maxsal > 0) THEN
     UPDATE HR.EMPLOYEES SET SALARY = maxsal WHERE JOB_TITLE = 'DBA';
  END IF;
END;

However, in order to do run these, you must:

• select Options > Run SQL. Change Statement Delimiter to None

• select Options > Technical Parameters. Check Use SQLExecDirect.

The first of these options is needed if your code includes a semicolon. By default AQT will interpret a semicolon as a statement delimiter. You do not want this to happen when running a block of code.

The second option is only required when running Oracle PL/SQL which includes variables (such as :CUSTCODE). In the normal way of running, the Oracle ODBC Driver will see the :CUSTCODE and think it is a substitutional parameter; this will result in AQT prompting for it. By setting SQLExecDirect, AQT will process the SQL in a slightly different way that prevents this problem from happening.

Display Oracle DBMS OUTPUT

Within a Oracle PL/SQL, it is possible to write debugging and other information messages with a statement such as:

dbms_output.put('message')

AQT provides a tool to display these dbms_output messages. This tool can be invoked from either the Database Explorer window or the Run SQL window with Tools > Oracle DBMS Output.

AQT will open only one DBMS Output window for each Oracle database.

AQT Function

In order to use this feature, AQT needs to create a function on your Oracle database. This function enables the dbms_output messages to be returned as a result-set - this is needed so that AQT can read these messages through the ODBC interface used by AQT.

When you first use the DBMS Output tool, AQT will prompt you to:

• create the function on your Oracle database

• use an existing function (for instance, one created by another user)

This function needs to be created on every Oracle database you use this tool with. Once you have created / selected the function, AQT remembers this name so won't prompt you again for this.

AQT doesn't remember different names for different databases; it is recommended that you use the same name for all Oracle databases.

Other notes:

• the name of the function that AQT creates is AQT_READ_DBMS

• the SQL used for creating the function is given near the bottom of the oracle.cfg configuration file

Enabling DBMS_OUTPUT

Before you can use the dbms_output feature of Oracle, you need to enable this for your Oracle session. This is done with:

call dbms_output.enable()

and disabled with

call dbms_output.disable()

These statements will be done automatically by AQT when you start/stop the DBMS Output window. Eg.

• when the DBMS Output window is started, AQT will do a dbms_output.enable

• when the DBMS Output window is stopped, AQT will do a dbms_output.disable

So, before you run some PL/SQL (or Stored Procedure) which generates some dbms_output messages, you should either open the DBMS Output window, or manually run the dbms_output.enable command.

Using the DBMS Output window

This window show the dbms_output messages generated during your Oracle session.

• click on Refresh to check the database for new messages.

• messages are unformatted. Oracle concatenates the text; separate messages do not appear on separate lines.

  You can obtain a degree of formatting by placing a chr(10) character at the end of each message - eg. dbms_output.put('message' || chr(10)). When displaying the output, select the variable row height option to see the text in multiple lines.

• the column Approx Time gives the time when AQT retrieved the message from Oracle. This is not the time when the message was created. However, when you are retrieving messages over a long period of time, this can be useful.

• you can have AQT auto-refresh the display - check Auto Refresh and set a value for the Refresh period. The minimum refresh period you can specify is 5 seconds.

  Note that AQT can only run one query at once. When the Auto Refresh feature is enabled, and you are running queries and using other windows within AQT, you may periodically get the message Cannot process SQL as a query is already in process.

• the Clear button clears the contents of the grid.

• the Row Height button displays the grid with variable-row-heights. This is useful if you have large or multi-line messages.

• if you have a large value in a cell, you can display the full value by double-clicking the cell, or right-clicking and selecting Display Cell.

• you can copy cells by selecting them then hitting Ctrl+C.