Splitting JDBC records.

[Former Member]

Hi Experts,

We have a interface in which Sender channel is picking the files from database Receiver channel is posting the files into database, Both sender and Receiver are JDBC communication channels only here, the problem is at receiver side while inserting the files into one of the database plant its getting Timeout error. So we decided to Split the records and pick n post i mean for example if the data volume if 30000 files , then we can do that it will pick and post 10000 thousand for poll.is there any setting in JDBC channel to define this....like RecordSetsper in FIle communication channle or is there any feasbility to write a select query that ..*Select * records from database..till count =10000.. Please Suggest possible solutions*.

B'rgrds,

Srinivas

[Former Member]

Hi Sri,

You can write a procedure/ ask db person to write, to pick the records from stagging table and use variable to send no.of records from channel.

Use query sql statement as execute "U r procedure name" @count=10000.

Cheers

Veera

[Shabarish_Nair]

an ideal design is to have a staging table. This will mean that PI will not access the original table but access a staging table where a copy of the original data is present. Thus it gives you a flexibility to have a select statement which defines to extract the first 10000 or so records. Then in the following update statement delete the first 10000 records. just a suggestion that you can use.

[Former Member]

Hi all,

Thanks for your prompt answer,

@Shabharish is there any extra communication channel requiired for do this i mean insreting the data into stagging tables first and then insert them into the Production tables.Can you Explain with some more details.

B'rgrds,

Sri

[Former Member]

Sri

what he is saying is not related in pi. This is at the database level.

U can ask ur database guys to create a database staging table and write a query to put the n no. Of records.

Then from pi you need to access this staging table.

Regards

---satish

[Shabarish_Nair]

this is usually done by the DB team themselves. They can load the data from the original table to the staging table and XI will only access this table.

[Former Member]

Hi Sathish And Shabarish,

Here records need to be insert into data base flow is like XI inserts data into Stagging tables and from there production tables, correct me if i am wrong so for this from stagging table to production setup also configured by database team only..?

B'rgrds,

Sri

[Shabarish_Nair]

yes. usually the DB team also has their own Dev, Qa, Prd environment.

So ideally the answer is yes.

[santhosh_kumarv]

Hi,

You can't ctrl the number of records using any parameters in JDBC channel. Only thing you can do is to midify your query to pick n records at a time.

For DB2 you can use fetch-first-clause.

~SaNv...

[Former Member]

Hello,

I think, no standard parameters in JDBC sender adapter for your requirements.

For you purpose i suggest next solution:

1. Define flag "processed" in sender data base table (for example '1' for processed rercord and '0' for not )

2. Define in your sender JDBC adapter Query SQL Statement to choose 10000 records with flag meaning '0' and not more than 10000

3. Define Update SQL Statement to change meaning of flag to '1' for processed records.

BR,

Dzmitry

[Former Member]

Hi Sri,

You can use fetch command in your sql query to take the first 10000 records every time. Please google it for its syntax and you can see some examples also.

Regards,

---Satish