QuerySurge Technical Whitepaper No. 4
Parameterizing your QueryPairs using a Table-based Approach
Introduction
One of the needs that QuerySurge users sometimes have is to build multiple QuerySurge QueryPairs that differ only in the parameters that the queries use. There are a couple of ways to set this up in QuerySurge; some users employ Reusable Query Snippets as containers for parameters, while others set up a Parameterized Driver Table in either the Source or Target database. This whitepaper focuses on the second approach, using a Parameterized Driver Table, because of the flexibilities it affords.
A Parameterized Driver Table
This approach provides a broad range of options in terms of setup, data and behavior. The basic idea is to create a table with parameterized data in a Source or Target database (whichever database the parameterized queries will run on), along with a flag to mark which rows have been executed. Taking advantage of QuerySurge’s ability to execute multi-statement “queries” (essentially, short SQL scripts on either the Source or Target or both; however note that this is not a full SQL scripting environment), we can pull the parameterized data off one row at-a- time from the driver table, mark the row as executed, and use the data in a query.
The following example,[1] while relatively straightforward, shows how powerful the approach can be.
The Setup: Driver Table and Fetch Function
For the purposes of this example, let’s assume that the parameterized data that we need for a series of related test queries is a list of country names. The driver table[2] must therefore have a column for the country names. In addition, the table will need an ID column as a key and a RUNFLAG column to indicate which rows have been run and which have not. In this example, a 0 in the RUNFLAG column indicates a row that has not been run, and a 1 signifies an executed row. We’ll create a table with these characteristics, called COUNTRYDATADRIVER in schema SALES:
|
COUNTRYNAME |
ID |
RUNFLAG |
|
Australia |
1 |
0 |
|
Austria |
2 |
0 |
|
Belgium |
3 |
0 |
|
Canada |
4 |
0 |
|
Denmark |
5 |
0 |
|
… |
… |
… |
|
United Kingdom |
49 |
0 |
|
United States of America |
50 |
0 |
In order to fetch the parameterized data from each row, we’ll need an executable statement that will return each value sequentially on a row-by-row basis. A clean way to implement this in Oracle is to create a function (we’ll call it f_get_row) that returns the desired row value:[3]
create or replace FUNCTION "f_get_row"
return VARCHAR2
is
paramdata VARCHAR2(45);
BEGIN
SELECT s.COUNTRYNAME INTO paramdata FROM "SALES"."COUNTRYDATADRIVER" s WHERE s.ID =
(select MIN(ID)
from "SALES"."COUNTRYDATADRIVER"
where RUNFLAG = 0);
RETURN paramdata;
END;
This function returns the COUNTRYNAME data from the row with the lowest ID value whose RUNFLAG value is 0.
A Parameterized Query in a QueryPair
With the setup above, it becomes straightforward to build a QueryPair with a parameterized query. The parameterized query simply needs to:
- Call the f_get_row function to get the parameter’s current value, and
- Update the COUNTRYDATADRIVER table RUNFLAG column after each function call
Just two statements are required to achieve this. The first is simply the “parameterized” query. Instead of using a parameter or a variable, we use a call into the f_get_row function:
SELECT c.COUNTRYISO2 FROM COUNTRYISO c
WHERE c.COUNTRYNAME = "SALES"."f_get_row";
The second statement is an update to the COUNTRYDATADRIVER table, which sets the value of the RUNFLAG column to 1 on the row of the last value returned by the previous call to f_get_row:
UPDATE "SALES"."COUNTRYDATADRIVER" SET RUNFLAG = 1 WHERE ID =
(SELECT MIN(ID)
FROM "SALES"."COUNTRYDATADRIVER"
WHERE RUNFLAG = 0);
With these two simple statements, we have the desired parameterized behavior. QuerySurge QueryPairs support multi-statement queries (as noted above) so both of these statements, the SELECT and the UPDATE, can be entered into either the Source query editor or the Target query editor.
Execution
Every time these statements are executed, a sequential parameterized value will be returned by f_get_row from the COUNTRYDATADRIVER table. In a multi-statement query, the SELECT statement acts as it would in a regular, single statement query – returning a ResultSet to QuerySurge for comparison to the ResultSet returned by the partner query in the QueryPair. In the example shown here, 50 sequential parameterized queries are executed.
Cleanup
Once the parameterized QueryPair in the example above has been called 50 times, the parameterized driver table’s RUNFLAG column will be fully set, so subsequent calls to f_get_row will return null, and the query SELECT statement will return 0 rows. The parameterized driver table can be re-set simply by updating the RUNFLAG column values to 0. Note that this cleanup step should not be done in a QueryPair, as QuerySurge makes no sequencing assumptions about QueryPair execution – since a given query’s return time may vary depending on the mix of query jobs running on the database alongside it, QuerySurge does not assume anything about when individual queries will complete relative to other queries in a Suite.[4]
Summary
The example discussed in this paper is relatively simple. However, it would not be difficult to expand on the scheme presented here. For example, multiple driver tables could be used for different kinds of executions, or a single driver table might contain not data but a key which could be used to join to other tables (e.g. fact tables) to return data for the parameterized run.
Another area could be in the control of the system as opposed to the fetching of the data. Cleanup, for example, could be achieved via an UPDATE statement which would run only at the final row execution, re-setting the system automatically after the final parameterized value has been used. A statement that provides this type of conditional cleanup for the system shown here is:
UPDATE "SALES"."COUNTRYDATADRIVER"
SET RUNFLAG = 0
WHERE (SELECT SUM(RUNFLAG) FROM "SALES"."COUNTRYDATADRIVER") = 50;
These additional directions show how easily this approach could be elaborated to accommodate multiple parameterization needs. The limitations are relatively few, and the potential enhancement to testing with this technique is considerable.
[1] Built in Oracle 10g Express
[2] Since we are creating tables and functions in the example shown here, login permissions with appropriate rights will be necessary.
[3] Note that we need not have encapsulated with a function in this example; however for purposes of illustration, we elected to use a function to show a broader range of syntax options.
[4] This is even clearer considering the fact that typically, multiple QuerySurge Agents are executing simultaneously, and Agents usually are run with multiple query threads. Any assumptions about which query will return relative to other queries becomes even more tenuous.