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

Order and Termination Options

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

To set order and termination options

  1. Click .
  2. Select Optimize SQL | Test Run | Order and Termination

  3. Review the following for additional information:

    Execution Order Description

    Execution order for SQL

    Select one of the following options:

    • Intelligent order—Executes representative SQL statements with various plan cost according to SQL Optimizer's intelligence engine.
    • Plan cost—Executes SQL statements in order of plan cost.
    SQL Termination Criteria Description

    Terminate execution of SQL alternative if it runs longer than

    Select one of the following options:

    • Run time of fastest SQL—Cancels SQL statements that run longer than the current fastest run time. With this option, the first SQL statement runs and the time from that statement is used as the termination time for the next SQL statement. When a SQL statement runs faster than this time, the faster time is used as the new termination time.
    • This percentage of the original SQL run time—Cancels SQL statements whose total elapsed time is the specified % of the total elapsed time for the original SQL statement. It terminates all SQL statements that run longer than the calculated termination time.
    • User defined time (mins/secs)—Cancels SQL statements that run longer than a time you specify.
    • Run without termination—Runs all SQL statements to completion regardless of run time.

    Cancellation delay (seconds)

    Adds a specified time to the termination time. It is important to factor a delay into the overall termination time to account for the time needed to send the SQL statement to the database server.

   

Related Topics

Execution Method Options

Best Alternative Criteria Options

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

To set the best alternative options

  1. Click .
  2. Select Optimize SQL | Test Run | Best Alternative Criteria

  3. Review the following for additional information:

    Best SQL Alternative Selection Criteria Description
    Best alternative selected based on lowest

    Select one of the following :

    • Total Elapsed Time—Select elapsed run time as the criteria to find the best SQL alternative t.
    • First Row Time—Uses the time to retrieve the first record to find the best SQL alternative.

    Default: Elapsed Time

   

Related Topics

Execution Method Options

Order and Termination Options

Tutorial

Using Optimize SQL consists of two steps. In the first step, SQL Optimizer generates semantically equivalent alternatives with unique execution plans for your original SQL statement. In the second step, SQL Optimizer executes the alternatives to test runs each statement's performance. This provides execution times and run-time statistics that allow you to find the best SQL statement for your database environment.

Step 1: Optimize the SQL Statement

  1. Select the SQL Details tab in the main window.

  2. Enter a SQL statement in the Alternative Details pane.
  3. Click to optimize the SQL statement

  4. Select a connection. Review the following for additional information:

    Select Connection Description

    Connection

    Click to select a previously created connection.

    Tip: Click to open the Connection Manager to create a new connection. Connect to the Database

    Select Schema and SQLID Description

    Schema

    Click to select a schema for the connection.

    SQLID

    Click to select your SQLID.
  5. Click after SQL Optimizer completes the SQL rewrite process to compare your original SQL statement with the alternatives generated.

Step 2: Test Run Alternative SQL Statements

The Test Run function provides an efficient way to benchmark alternatives generated by SQL Optimizer. You can test run selected alternatives to obtain actual execution statistics. This function does not affect network traffic since SQL Optimizer can provide these statistics without having to retrieve result sets from the database server. Additionally, data consistency is maintained when using SELECT, SELECT INTO, INSERT, DELETE, and UPDATE statements because these statements are run in a transaction that is rolled back after execution.

To test run a SQL statement alternative

  1. Click .

  2. Select Optimize SQL | Test Run | Test Run Settings.

  3. Select Custom Settings or Predefined Settings.

  4. Select Optimize SQL | Test Run | Execution Method.

  5. Review the following for additional information:

    Execution Method Description

    Maximum rows to be retrieved (records)

    Select this checkbox and then enter the maximum number of rows to retrieve for a test run of all records.
    Run Time Retrieval Method Description

    Run SQL options

    Select one of the following options:

    • Run all SQL twice if original SQL runs faster than (seconds)—Combines the Original SQL twice and all others once and the All SQL twice options into one option and allows you to determine (by the number of seconds a SQL statement runs) which option to use. The original SQL statement always runs twice. The SQL alternatives run twice if the original SQL statement runs in less time than the value specified. Otherwise, the SQL alternatives all run once.
    • Original SQL twice and all others once—Caches data from a table into memory the first time you access it. The next time you access that data, it is already in memory so the following SQL statements run faster. To provide an accurate comparison, the first SQL statement runs twice but only the time from the second run is compared to the times for the other statements.
    • All SQL twice—Executes all SQL statements twice to eliminate factors that can affect the accuracy of the results. If you recently executed a SQL statement, the information for that statement may be cached and the statement may execute faster. This option eliminates time variation caused by caching since it runs all SQL statements twice but only uses the second run time for comparison.
    • All SQL once—Executes all SQL statements once. For long running SQL, you do not need to run any statement twice since the effect from caching diminishes over time.
  6. Select Optimize SQL | Test Run | Order and Termination.

  7. Select the This percentage of the original SQL run time checkbox.

  8. Click .

    The execution statistics display in the Alternatives pane once the test run completes. Select an alternative to see more information in the Alternative Details and Execution Plan panes.

  

Tutorial: SQL Optimizer

Using Optimize SQL consists of two steps. In the first step, SQL Optimizer generates semantically equivalent alternatives with unique execution plans for your original SQL statement. In the second step, SQL Optimizer executes the alternatives to test runs each statement's performance. This provides execution times and run-time statistics that allow you to find the best SQL statement for your database environment.

Step 1: Optimize the SQL Statement

  1. Select the SQL Details tab in the main window.

  2. Enter a SQL statement in the Alternative Details pane.
  3. Click to optimize the SQL statement

  4. Select a connection. Review the following for additional information:

    Select Connection Description

    Connection

    Click to select a previously created connection.

    Tip: Click to open the Connection Manager to create a new connection. Connect to the Database

    Select Schema and SQLID Description

    Schema

    Click to select a schema for the connection.

    SQLID

    Click to select your SQLID.
  5. Click after SQL Optimizer completes the SQL rewrite process to compare your original SQL statement with the alternatives generated.

Step 2: Test Run Alternative SQL Statements

The Test Run function provides an efficient way to benchmark alternatives generated by SQL Optimizer. You can test run selected alternatives to obtain actual execution statistics. This function does not affect network traffic since SQL Optimizer can provide these statistics without having to retrieve result sets from the database server. Additionally, data consistency is maintained when using SELECT, SELECT INTO, INSERT, DELETE, and UPDATE statements because these statements are run in a transaction that is rolled back after execution.

To test run a SQL statement alternative

  1. Click .

  2. Select Optimize SQL | Test Run | Test Run Settings.

  3. Select Custom Settings or Predefined Settings.

  4. Select Optimize SQL | Test Run | Execution Method.

  5. Review the following for additional information:

    Execution Method Description

    Maximum rows to be retrieved (records)

    Select this checkbox and then enter the maximum number of rows to retrieve for a test run of all records.
    Run Time Retrieval Method Description

    Run SQL options

    Select one of the following options:

    • Run all SQL twice if original SQL runs faster than (seconds)—Combines the Original SQL twice and all others once and the All SQL twice options into one option and allows you to determine (by the number of seconds a SQL statement runs) which option to use. The original SQL statement always runs twice. The SQL alternatives run twice if the original SQL statement runs in less time than the value specified. Otherwise, the SQL alternatives all run once.
    • Original SQL twice and all others once—Caches data from a table into memory the first time you access it. The next time you access that data, it is already in memory so the following SQL statements run faster. To provide an accurate comparison, the first SQL statement runs twice but only the time from the second run is compared to the times for the other statements.
    • All SQL twice—Executes all SQL statements twice to eliminate factors that can affect the accuracy of the results. If you recently executed a SQL statement, the information for that statement may be cached and the statement may execute faster. This option eliminates time variation caused by caching since it runs all SQL statements twice but only uses the second run time for comparison.
    • All SQL once—Executes all SQL statements once. For long running SQL, you do not need to run any statement twice since the effect from caching diminishes over time.
  6. Select Optimize SQL | Test Run | Order and Termination.

  7. Select the This percentage of the original SQL run time checkbox.

  8. Click .

    The execution statistics display in the Alternatives pane once the test run completes. Select an alternative to see more information in the Alternative Details and Execution Plan panes.

  

Related Documents