TIBCO - How To - TIBCO JDBC Query Result in Subsets

TIBCO JDBC Query Result in Subsets – Version 1.00 DISTRIBUTION DATE: 2 February 2015  STATUS: Draft By: Warren Chen

 

Introduction

When using JDBC Query in TIBCO Processes, there can be cases where you are getting a large number of records as query result but you don’t want to process and use all the records altogether. Rather you want JDBC Query Results to be processed as subsets.
In this Step by Step JDBC Tutorial, I will explain how you can process JDBC Query results in subsets in a Loop in TIBCO processes.

Example Scenario

For this tutorial, we take a scenario where we want to query a database table which has large number of records and we want to send the query results in subsets of 2 to a JMS Queue using JMS Queue Sender activity.
The table that we want to query has a total of 10 Records as shown below:


Now let’s proceed step by step with the tutorial to query and process the records from above table in subsets.

Step 1: Create JDBC Connection and Configure JDBC Query Activity

For this tutorial, I am using Oracle Database. Add JDBC Connection resource in your project from JDBC Palette and configure it based on your own database information. Configuration in my case is as shown below:


Now create a process and add JDBC Query activity in the process. In the configuration tab, select the connection and also specify the query to be executed as shown in screenshot below:

 
Now go to Advanced Tab and check the option for Process in Subsets. This checkbox enables you to process query results in subsets.
 

Now if you go to the input tab, you will observe that a new field subsetSize has been added. Specify some value for it. I have specified 2 in the field as I want to process query results in subset of 2.
 

Now In order to send query results to a JMS Queue, I have crated XML Schema. This schema will be used to map JDBC Query results to body of JMS Queue Sender. Schema is shown in below screenshot:
 

Now we need to add JMS Queue Sender activity in the process. For this we first need to have JMS Connection available. I have configured JMS Connection as shown below:
 

Add JMS Queue Sender and configure it by specifying JMS Connection, Queue name and other fields as shown in the diagram below:
 

After selecting XML Schema in the Input Editor, go to Input tab and map Query results to the body of JMS Queu Sender as you can see below:
 

Now Select JDBC Query and JMS Queue Sender activities and enclose them in a group by choosing Create Group option from toolbar.
In the Group Action, choose Repeat -Until-True and use LastSubset variable value in the Conditions field. LastSubset will be true when last subset of the query result will be in the Loop.
 

This completes our process design and configuration of all activities.
Now let’s proceed towards testing step to see how JDBC Query with Subsets feature works.

Step 2: Test JDBC Subset Query Process

Load JDBC Subset query process in designer tester and run it. As you can see below, It has run successfully:

 
You can go to EMS Server and verify number of Pending Messages in the queue:
 

Please note that number of messages are 6 instead of 5 as Loop has run 6 times as condition of LastSubset was true 6th time.
That’s it for today. Contact me for any further help or guidance needed.