JDBC ,select

[Former Member]

hey guys , New to all this ... but i have to poll a database table.. sender jdbc adapter and since have around 50,000 to be picked from the table, saw a blog on sdn which mentions that using rownum query i could pick a specified number of records and then after a certain polling interval the next batch will be picked . it is like splitting a huge chunk into smaller batches.

had a question : it is like jdbc to abap proxy scenario, how will proxy know that this is the last batch of records? can PI send some control record to indicate that this was the last batch / row in table or does source table need to have this record at end and PI could pick it?

Thnx...

[Former Member · Accepted solution]

Proxy is a Realtime reciever. whenever the messages come to it, it processes the messages.

So, i think there is no need to know for proxy, if it is a first record or last record.

Even then, if you have a requirement to know the number of records , you can maintain a counter in mapping.

santosh.

[Former Member · Accepted solution]

Number of records is not required.... for target.

What I am wondering is from interface perspective : its a jdbc to proxy sorta scenario and from jdbc source side according to the query , will be splitting records to send only bunch of data and then iterate to send all the data. But how will proxy know this is the last set of data being passed over and it could start processing the records received?

[Former Member · Accepted solution]

As i mentioned earlier, Proxy is a Realtime reciever.

It doesn't wait till all the records are recieved.

As soon as it recieves the records, it starts processing them.

Lets consider there are 10 records in JDBC.

They are being sent 2 records at a time.

as soon as proxy recieves 2 records, it processes those records immediately.

when next 2 records are recieved, it further processes those records.

Bottomline....."proxy keeps on processing , as records comes".

another thing is, database entries are never split half, its always minimum 1 full DB entry at a time....

-santosh.

[Former Member · Accepted solution]

Hi,

Proxy will process the data as and when it receives it; it doesn't wait for the entire bunch of data as already mentioned in the above post.

But you have to take care at the JDBC end, that no batch of data is getting fetched repeatedly.

-Supriya

[Former Member · Accepted solution]

Ok thats good that Proxy is real time receiver.. get your point.. Both of you gave good explanation.

For sender jdbc side will be using this blog to split records.

/people/peng.shen2/blog/2009/12/23/pi-how-to-handle-high-volume-data-per-jdbc-adapter

1) Did not get that point that Database entries cannot be split into half.. did not get what you were trying to imply. can u explain as it could help. thnx

2) Duplicate entries should not be picked.. hmm is that also a possibility hmm.. was reading the blog i have in upper part of email. is there a possibility of this ?

Thnx

[baskar_gopalakrishnan2 · Accepted solution]

>>1) Did not get that point that Database entries cannot be split into half.. did not get what you were trying to imply. can u explain as it could help. thnx

The blog will help you to split the entire reading records into multiple calls. That means Jdbc adapter picks first 100 and pass it to backend. Second time a fresh call and pick next 100 and pass it to backend. So, you decide how many records to be split in each message.

2) Duplicate entries should not be picked.. hmm is that also a possibility hmm.. was reading the blog i have in upper part of email. is there a possibility of this ?

You make seperate jdbc call to pick or select records. If the first pickup has a record that repeats again in the third jdbc pickup, we cannot handle this. I would rather make the data clean. If not we cannot handle this.

[Former Member · Accepted solution]

Fforgot to ask another thing.. does someone have any idea about whats the optimum size or number of records JDBC select should pick in each shot? We have PI 7.1 and each database record is just 6 columns to be fetched.

thanks All , will assign as in the accept agreement the points and close the thread after this answer. Thank you all.

[baskar_gopalakrishnan2 · Accepted solution]

Pi recommends Optimum message size is 1 to 5 MB. You cannot decide based on the number of records you retrieve Because you might select 30 fields in the query for each row or you might select just one field for each row. So the number of records is not the deciding factor. Again in our project we do around 800 records each time with 8 fields and runs without any issues. The message size is around 3 mb.

Couple of tIps:

Use Advance tab in jdbc adapter and add logSQLStatement parameter and set value true. This will help log trace.

open jdbc connection for every call and close after finishing it. set that config in jdbc adapter. Dont leave the connection always open.

Hope that helps.

[Former Member · Accepted solution]

what is the polling interval you keep to pick these records? are these 80 records picked instanteously or take time ? Thanks you s

[baskar_gopalakrishnan2 · Accepted solution]

Though each transaction takes place less than 20 seconds, We set 150 seconds as polling interval