Creating routine test configurations

For SQL, PL/SQL, and Java routines in a project, you can create test configurations that contain settings that are used when you run the routine. The routine test configurations can help test routines and validate changes you make to a routine.

For example, you can create multiple test cases that pass different input values to test different portions of a routine. When you run a routine with a test configuration, you can compare the result output and output parameter values with a predefined set of values. The results of the comparison appear in the SQL Results view.

Before you begin

Deploy your routine to the database.

About this task

You create test configurations for routines in your project. The configurations are stored in the project Test folder.

Procedure

To create a routine test configuration:

  1. In the Data Project explorer, open your project, right-click the Test folder, and select New. Edit Configurations opens.
  2. Specify the properties for the test configuration
    • In the Routine page, specify the name of the test configuration. Click Browse to select a deployed routine in your project.

      If needed, enter or update input parameter values. For a string parameter value, type the value without string delimiters. If the parameter requires a binary value, enter the hex string without delimiters.

    • On the Run and Performance Options page, select whether you want to commit changes to the database. For DB2® for z/OS®, specify the collection ID that you want to use. If you do not select to commit changes to the database, any changes that are made as a result of running the routine are rolled back.

      Gather database performance information collects and displays routine performance metrics in the SQL Results view.

      Gather actual access plan information from the database runtime statistics for the operations performed to execute the SQL statements are collected from the database. InfoSphere® Optim™ Query Workload Tuner uses the information when you tune the routine SQL statements.
    • On the Before Run page, specify SQL statements that you want to run before you run the routine.
    • On the After Run page, specify the SQL statements that you want to run after you run the routine.
    • On the Baseline Results page, select Enable comparison of test configuration results to compare output generated when you run the routine with the test configuration with result output that you specify. The comparison information is displayed in the SQL Results view.
  3. Click OK.

Results

A test configuration for the routine is created in the Tests folder.

What to do next

Right-click the test configuration and click Run Test to run the test configuration. The results of the test run are displayed in the SQL Results view.

The results of the operations performed as part of the test run are nested under the test run operation. For example, if you enabled Enable comparison of test configuration results, the results of the comparison between the baseline result and the test run output are listed as an operation nested below the test run operation.

When you change a routine in the workbench, you can compare the results of a modified routine with the results of the original routine.

If you gathered performance information for an SQL routine, the information appears in the SQL Results view. For SQL statements that perform poorly, you can right-click the statements and click Start Tuning to open the InfoSphere Optim Query Workload Tuner and tune the statement. To tune the statements from the SQL Results view, InfoSphere Optim Query Workload Tuner must be installed on the database that runs the statement.


Feedback