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:
- In the Data Project explorer, open your project, right-click
the Test folder, and select New. Edit Configurations opens.
- 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.
- 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.