Fetching Subsets of the Result Set
When the result set of a query is very large, you can retrieve subsets of the result set and iterate until the entire result set is processed. To retrieve subsets, you must use a Repeat Until True loop group to iterate until the entire result set is processed. Use the following procedure as a guideline for creating your Repeat Until True loop group.
Procedure
-
Create a JDBC Query activity.
-
Specify the fields on the Configuration tab and create a query that returns multiple rows.
-
Check the Process In Subsets field on the Advanced tab.
-
Set the
subsetSize
input item for the JDBC Query activity to the number of records you want to process for each execution of the loop. You could specify a constant, or you could use a global variable to store the value. Using a global variable has the advantage that you can change the value of the variable at deployment time. For example, create a global variable namedBATCH_SIZE
, then specify thechunkSize
input element as$_globalVariables/pfx:GlobalVariables/BATCH_SIZE
. -
Select the JDBC Query activity and click the group icon on the toolbar to create a group containing the activity. For more information about working with groups, see TIBCO ActiveMatrix BusinessWorks™ Process Design.
-
Select Repeat Until True as the Group action, and specify an index name (for example,
i
). -
The loop should exit when the entire result set has been consumed. The output element
lasSubset
is a boolean that is set to true when the last subset of records is being processes. For example, the condition for the loop could be set to the following:
$JDBCQuery/resultSet/lastSubset = "true"
The procedure above is a general guideline for creating a loop group for processing a large set of records. You may want to modify the procedure to include additional processing of the records, or you may want to change the XPath expressions to suit your business process.
For an example of processing subsets of a larger result set, see the examples included in the ActiveMatrix BusinessWorks installation directory .