JDBC-JDBC scenario

[Former Member]

Hello All,

My scenario - Select / Update from source Oracle DB -> PI -> Insert in target Oracle DB.

My questions -

1. If I am inserting 5000 records at a time, and only 4900 are inserted (100 fails for some reason), how I can do the error handling? I would like to know against which key field the error happened. If I am able to know against which key field the error happened, I will update the FLAG field of the source DB accordingly.

2. I believe PI can handle the cursors at the Receiver JDBC CC coming as a response from Oracle DB. If yes, how that is done?

Any help / suggestions will be highly appreciated.

Thanks,

Abhi

[Former Member · Accepted solution]

Hi Abhi,

I would select and insert single records. This makes the error handling much easier.

Regards Mario

[Former Member · Accepted solution]

Mario,

So you mean to say that the Select and Insert will be done for single records each time?

The source table will have records up to 500,000 so single fetch and insert doesn't sound good to me. I am selecting 5000 records in every Select and then updating the flag fields as "read" in those selected records.

What you say?

Thanks!

Abhi

[Former Member · Accepted solution]

Any one? Please help.

Thanks,

Abhi

[Former Member · Accepted solution]

Hi,

If you select 5000 record and sent it to Xi. XI will send all the records together. If there is errror to insert the data, everything will be rolled back.

1) You can select 5000 record and split it in Xi and send 1 by one. N:1 mapping. The only record which has wrong data will fail.

2) You can try to write a stored procedure which take all the data as input and depending upong the error should insert the status also.

3)Errorin case of JDBC is always a wrong data, nothing else.. You can do strong validation in mapping and make sure wrong data never reaches the receiver channel.

What kind of other errors you want to handle. ?? Do you want to do it in mapping also ?

Inder

[Former Member · Accepted solution]

Inder,

Thanks for your reply.

I have few queries -

1) You can select 5000 record and split it in Xi and send 1 by one. N:1 mapping. The only record which has wrong data will fail.

How feasible is this approach as the polling interval is every 1 minute, which will be fetching 5000 records?

2) You can try to write a stored procedure which take all the data as input and depending upong the error should insert the status also.  

How this can be achieved through SP? Means, how we can return the "key field" which encountered error while inserting? This approach looks better to me than rest.

Please advice.

Thanks,

Abhi

[Former Member · Accepted solution]

1) if you are gng to pull 5000 evertime.. forget about splitting the message.

How this can be achieved through SP? Means, how we can return the "key field" which encountered error while inserting? This approach looks better to me than rest.

I am not sure about the exact procedure but there are ways to achieve it. Check with some database programmer.

Are you going to send 5000 records after every 1 minute ? is it always going to be this much load?

If the load is this much try validating data in mapping itself... if there is some problem you can route the data to mail adapter to raise the alert or just raise alert from mapping. what u say ? I have followed this approach once and it worked fine.

How many fields do you want to sent from source to target ? if they are less go with validation in mapping....

Do you want to make it synchronous also ?

Inder

[Former Member · Accepted solution]

As load is very high:

I will recommend :

Design mapping in such a way that only 100% correct data reaches ur target. so it will never fail at adapter level.

Do all validations in mapping... as it is database to database transfer, chances of wrong data are minimal. raise alert in mapping if data is wrong.

Dont go for synchronous. Make one more interfaces to send the data back with updated status.

or

You should have strict constraints at source database level so that wrong data is never entered in source database.

Inder

[Former Member · Accepted solution]

Thanks Inder.

Are you going to send 5000 records after every 1 minute ? is it always going to be this much load? 

Well ... since the polling is every 1 min, we don't expect 5000 records every time. Once in a month, we will have 500,000 records to fetch, when the source DB will be updated.

If the load is this much try validating data in mapping itself... if there is some problem you can route the data to mail adapter to raise the alert or just raise alert from mapping.  what u say ? I have followed this approach once and it worked fine.
How many fields do you want to sent from source to target ? if they are less go with validation in mapping....

There are only 5 fields, so data volume is not going to be too much. It's just the number of records. I will have to think and discuss whether I want to validate at mapping level.

Do you want to make it synchronous also ? 

Sync will be helpful to return the "key field", where error is happening. Else the business doesn't require anything to return to the source DB.

Once again, thanks!

Abhi

[Former Member · Accepted solution]

Design mapping in such a way that only 100% correct data reaches ur target. so it will never fail at adapter level.
Do all validations in mapping...  as it is database to database transfer, chances of wrong data are minimal. raise alert in mapping if data is wrong.
Dont go for synchronous. Make one more interfaces to send the data back with updated status.
> 
> or
> 
> You should have strict constraints at source database level so that wrong data is never entered in source database.

I will work on this approach, but will keep the thread open in case anyone else has more ideas for this scenario.

Thanks a lot Inder!

Abhi

[Former Member · Accepted solution]

See you have 5 fields: Validate each of them in mapping.

One more idea, write a sender stored procedure which will pick the data after validating the record as there are only 5 fields. If some field has wrong data update the status record with error.

it will make sure that you always have a correct data to send to XI. It will never fail in XI and hopefully in adapter tooo..

Inder

[Former Member · Accepted solution]

Hi all,

I have couple of more questions.

1. I have to take care of the sequencing of the records.

So, if I make Sender JDBC CC as EOIO, and there is an error while inserting in the target, what will happen to the successive records? Will the Sender CC stop polling or it will do the polling and the messages will pile up in PI?

I guess it will still fetch the records and pile up those in the PI, but PI won't send it to the target as the earlier message failed to get inserted. Am I right?

2. In the Receiver JDBC CC, there is an option of "Batch mode". What is the use of this? When I am inserting in the target, the commit will be done on the complete message and not individual records. Please correct me if I am wrong.

Thanks,

Abhi

[Former Member · Accepted solution]

1. I have to take care of the sequencing of the records.
So, if I make Sender JDBC CC as EOIO, and there is an error while inserting in the target, what will happen to the successive records? Will the Sender CC stop polling or it will do the polling and the messages will pile up in PI?
I guess it will still fetch the records and pile up those in the PI, but PI won't send it to the target as the earlier message failed to get inserted. Am I right?

I beleive it will keep on polling. (never tried) and messages will pile up (You can do a smal test to check it). If one record fails others will wait till the message is corrected or canceled.

2. In the Receiver JDBC CC, there is an option of "Batch mode". What is the use of this? When I am inserting in the target, the commit will be done on the complete message and not individual records. Please correct me if I am wrong.

You can say so.. in actual the XI will collect all your data and try ro insert all the the records in a single call to database in a Batch.

Inder

[Former Member · Accepted solution]

Thanks Inder for all the help!

I will work on this and will get back to you / community if I need more help / suggestions.

I am closing this thread.

Thanks,

Abhi