How to Insert only valid records in DB using JDBC Receiver adapter

[baskar_ramasamy]

Hi experts,

How to insert only the valid records in to DB using JDBC receiver adapter.

we are doing a transaction which contains 10 records ,that should be inserted in DB using JDBC receiver adapter.

Ex: 10 records sent at a single transaction, in which 1 record has invalid value, so records are not inserted in to DB.

Is there any option in PI to eliminate the 1 failed record and process remaining 9 records to insert in to DB.

Regards,

Baskar

[baskar_gopalakrishnan2]

>Is there any option in PI to eliminate the 1 failed record and process remaining 9 records to insert in to DB.

If all goes in one transaction then the answer is NO.

Use validation in mapping or schema validation in the pipeline steps either at adapter engine or integration engine before processing. I would also recommend stored procedure in your case since message volume is going to be on the higher side.

[baskar_ramasamy]

Thanks raja & baskar. i will try and let you know the results.

[Former Member]

Hi,

Check this thread:

http://scn.sap.com/thread/3190726

Thanks

Amit Srivastava

[rajasekhar_reddy14]

1)First consider writing a validation at ECC level, if you are dealing with IDocs then i dont see you issues with data like(length),if you think more validation required then better to check with ECC team handle it.

2)As a PI consulatant even you can write mapping logic to perform validtaion and even you can supress those records at PI level.

3)Use stored procedure at data base level and ask DB team to write validation logic , before inserting data in to table they have to write a logic in stored procedure, this is very common practice writing validtaions in stored procedure.

What exactly your validation logic?how you idetify good record or bad record.

[udo_martens]

Hi Baskar,

you can build 10 statements in mapping time - instead of 1 statement with 10 records. Of course the performance will be worth, but if you do not have like 1000 records by minute: it does not matter.

Regards,

Udo

[baskar_ramasamy]

Hi udo,

Thanks for your information,

we do 10,000 records per transaction 😞

[udo_martens]

Hi Baskar,

and how often do you execute a transaction?

Regards,

Udo

[baskar_ramasamy]

job scheduled from ecc for every 30 mins, number of records depends on the data available in ecc in 30 min time stamp.record count will be more than 4,000 per transaction average.

[udo_martens]

Ok,

then you have to consider a stored procedure.

If you can identify the not valid records in mapping time (without a lookup), you can exclude them there. But i guess, you cannot.

Regards,

Udo

[Former Member]

Below options

1) use stored procedure at receiver DB side

2)use udf and play with payload