Chat now with support
Chat with Support

SQL Optimizer for DB2 ZOS 5.6.1 - User Guide

Welcome Optimize SQL Options Tutorial About us Third-party contributions Copyright

Set Bind Variables

You need to define the values of SQL statement variables before you optimize the statement. The Set Bind Variables window displays automatically when you use SQL Optimizer for a statement with variables.

Troubleshooting: If the Set Bind Variables window displays when you optimize a statement without variables, make sure you spelled the column and table names correctly, you selected the correct database or user, and you selected a table or column that exists in the database.

Note: This topic focuses on information that may be unfamiliar to you. It does not include all step and field descriptions.

To set a bind variable

  1. Select the SQL Details tab in the main window.
  2. Enter a SQL statement with a bind variable.

    Note: SQL Optimizer currently supports the following bind variables data type:

    • CHAR

    • NUMERIC
    • DECIMAL
    • INTEGER
    • SMALLINT
    • FLOAT
    • REAL
    • DOUBLE
    • DATETIME
    • VARCHAR
    • TYPE_DATE
    • TYPE_TIME
    • TYPE_TIMESTAMP
    • GRAPHIC
    • VARGRAPHIC
    • LONGVARGRAPHIC
    • BLOB
    • CLOB
    • DBCLOB
    • DATALINK
  3. Click .

  4. Review the following for additional information:

    Bind Variables List Description

    Datatype

    Click and select the variable datatype.

    Null

    Select the checkbox if the value for the variable is null.

    Variable Value

    Enter a value for the variable.

    Browse Data Description

    SELECT

    Click and select column references for the variables.

    Note: SQL Optimizer selects the All Columns checkbox by default.

    FROM

    Click and select table references for the variables.

    Tip: Click to browse to tables in a different database.

    WHERE

    Enter a WHERE clause or click to select a previously entered clause.

    ORDER BY

    Enter a ORDER BY clause or click to select a previously entered clause.

 

Related Topics

Optimize SQL Statements

Test Run SQL Alternatives

Review Alternatives

Use the Alternatives pane to review the SQL text and execution plan for the alternatives SQL Optimizer generates. You can also review the run-time statistics for your original SQL statement and the alternatives after you execute the statements.

Note: This topic focuses on information that may be unfamiliar to you. It does not include all step and field descriptions.

To review details for an alternative

  1. Select the SQL Details tab in the main window.
  2. Select an alternative in the Alternatives pane.

  3. Review details for the alternative in the Alternative Details and Execution Plan panes.

  

Related Topics  

Optimize SQL Statements

Automatically Optimize SQL Statements

Alternative SQL Text

Status Displays client status.
Status Displays client status.
Elapsed Time Displays the execution time to retrieve the entire result set.

Alternative SQL Text

The text editor is used to display the SQL text of the selected alternative. When there’s only original alternative or the selected alternative is user defined, the SQL text can be edited. Otherwise, it’s read-only.

  

View Optimization Details

Note: This topic focuses on information that may be unfamiliar to you. It does not include all step and field descriptions.

To view optimization details

  1. Select the SQL Details tab in the main window.
  2. In the Alternatives pane, click .
  3. Select the SQL Information tab in the Optimize SQL window. Review the following for additional information:

    Optimization Details Description

    Intelligence level

    Intelligence level used for optimization. This option is set on the Optimizer Intelligence options page. Intelligence Options

    Semantically equivalent SQL statements

    Number of semantically equivalent SQL statements generated by SQL Optimizer.

    Alternative execution plans produced

    Number of alternative execution plans produced for the original SQL statement.

    SQL statements eliminated due to identical execution plans

    Number of statement alternatives eliminated with the same execution plan as the original SQL statement.

    Note: SQL statements with the same execution plan produce the same run time because the database executes these statements in the same way. Therefore, SQL Optimizer eliminates alternatives with equivalent execution plans.

    Optimization Time Details Description

    Optimization started at

    Time optimization started.

    Optimization finished at

    Time optimization finished.

    Total optimization time

    Total time to complete optimization.

    Average optimization time

    Average time to generate each statement alternative.

    Notes:

    • This information is only available after you generate SQL alternatives.
    • Optimization details only display for the original SQL statement.

 

Related Documents