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 3: 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.
Hi Ajmal
thanks for sharing the knowledge with us..
Please upload some BE tutorials as well. Keep the good work.
Hai sir,
can you explain process subset without using Jms queue?
Hi Ajmal,
Your tutorials are absolutely fantastic and you provide the fundamentals needed to kickstart learning on a particular component in TIBCO.
If you know BusinessConnect, I would really appreciate you to teach something about it in your website.
Thanks,
Dheeraj
last subset property where is it available??
Can we create table through JDBC palatte….please if it is there possibility explain me….
Yes we can create the table using SQLDirect activity and also we can perform all DDL commands using this activity which are not supported by any other JDBC activities.
When we are processing in subsets, will the JDBC connection be kept hold till all records were fetched? Can you explain how the connection pooling works in this case?
Is there a way to use Oracle cursors (store procedures) to process data in subsets ?