cancel
Showing results for 
Search instead for 
Did you mean: 

How to split the number of rows of data pulled from DB via JBDC sender channel?

gopalkrishna_baliga
Participant
0 Kudos

Hi Experts,

    I have below requirement.

    Pull all products (ID and NAME) where category = "ATM". There is already a stored procedure get_products.

CREATE PROCEDURE [dbo].[get_products]

@category varchar(10)

AS

BEGIN

    select product.id, product.name from Product where TYPE = @category

END;

    This pulls about 10,000 rows of data.

    Now it should be split into 2000 rows each.

    Then setup a batch job that picks 2000 rows each every 1 hour from DB.

    I am using JDBC sender channel.

   

    Is it possible to achieve this in PI JDBC sender?

    Will PI JDBC sender keep polling the DB?

    Will this be Synchronous call?

    How using JDBC sender can I call the above stored procedure, pass input category parameter and get output?

   

    Also how can I pull 2000 rows in PI? Does batch job to split and send 2000 rows make sense? Where shall this job reside?

  

    Please help?

Thanks

Gopal

Accepted Solutions (0)

Answers (4)

Answers (4)

former_member257758
Participant
0 Kudos

Hi Gopal,

You have below option.

1. Implement select query in JDBC sender channel to pull 2000 records each time with the update stmt , If you have any flag field in (like status filed) DB to update different status. You can schedule the channel to every 1 hr later in the availability tab in Channel monitor.

                            I know you have mentioned abt SP, but check with DB folks if it is OK to use select query.

2. you can pull all the rows using JDBC sender channel (with EOIO, put the queue name) and then split in MM,  its like (1:N) splitting.  But you need to implement 2 UDF in MM to control the rows.

Follow the below link.

Split Mapping using UDF - Process Integration - SCN Wiki

I hope this helps your requirement.

Thanks

Arun

Former Member
0 Kudos

    Is it possible to achieve this in PI JDBC sender? yes

    Will PI JDBC sender keep polling the DB? you can schedule the channel polling frequency or Avaialblity time planning to poll only during particular timings..


     Will this be Synchronous call? depends on ur scenario - Sync or Async


    How using JDBC sender can I call the above stored procedure, pass input category parameter and get output?

Former Member
0 Kudos

Hi,

To select 2000 records at a time you should just add it in your select query on the sender adapter. For example.

SELECT TOP 2000 *
FROM tblProductInformation p1 INNER JOIN tblProduct1 p
ON p.productname = p1.

productname

Just remember your update statement must be correctly otherwise you will pick up the same records.

Regards,

Jannus Botha

iaki_vila
Active Contributor
0 Kudos

Hi Gopalkrishna,

It depends about your DB vendor.

You can try with the clause rownum in the where (In the Select and Update in your sender jdbc.

For SQLServer you can try with the instruction TOP:

In additional parameter you can set the maximum length of your message: Configuring the Sender JDBC Adapter - Advanced Adapter Engine - SAP Library

Regards.