TIBCO Tutorial: How to Process JDBC Query Results in Subsets

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:

table records

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:

jdbc query subsets jdbc connection

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:

jdbc query subset configuration

 

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

jdbc query advanced process 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.

jdbc subsetSize input

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:

jdbc query result schema

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:

jms connection screenshot

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

jms queue sender configuration

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:

jms queue sender input mapping

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.

tibco repeat until true group example

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:

tester jdbc subset query

 

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

ems show queue tibco

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.

 

Ajmal Abbasi

Ajmal Hussain Abbasi is Integration Consultant By Profession with 13+ years experience in Integration domain mainly with TIBCO products. He has extensive practical knowledge of TIBCO Business Works, TIBCO Cloud, TIBCO Flogo, TIBCO Mashery, TIBCO Spotfire, EMS and TIBCO ActiveSpaces. He has worked on a number of highly critical integration projects in various sectors by using his skills in TIBCO Flogo, TIBCO API Management (Mashery), TCI, Tibco Designer, TIBCO Business Studio, Adapters, TIBCO EMS, RV, Administrator, TIBCO BE, TIBCO ActiveSpaces etc. Ajmal Abbasi has experience with MuleSoft ESB as well. Ajmal Abbasi is also experienced in the area of API Management particularly with WSO2 API management platforms. Ajmal Abbasi is also experienced in developing solutions using Core Java and J2EE Technologies. You can contact Ajmal Abbasi for Consultancy, Technical Assistance and Technical Discussions.

More Posts - Website - Facebook - LinkedIn - YouTube

8 thoughts on “TIBCO Tutorial: How to Process JDBC Query Results in Subsets

  1. rana

    Hi Ajmal
    thanks for sharing the knowledge with us..
    Please upload some BE tutorials as well. Keep the good work.

    Reply
  2. Purushotham

    Hai sir,
    can you explain process subset without using Jms queue?

    Reply
  3. Dheeraj

    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

    Reply
  4. Rajeswari Chenna

    Can we create table through JDBC palatte….please if it is there possibility explain me….

    Reply
    1. Khadarvalli

      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.

      Reply
  5. tibcobee

    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?

    Reply
  6. Ian

    Is there a way to use Oracle cursors (store procedures) to process data in subsets ?

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *