on 08-27-2014 11:40 AM
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
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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?
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
88 | |
23 | |
11 | |
9 | |
8 | |
5 | |
5 | |
5 | |
5 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.