Approach needed for processing huge file.using file-jdbc

[Former Member]

Hi,

My Scenario is file-jdbc. I need to update the records in database table. The size of file would be 500mb.

1)Will the recordsets per message in the sender side FCC will help me in processing the file? Any other better solution or any configuration needs to be checked on PI system? as processing should be finished in 3 hours time?

2)i need to update other table with no.of records processed per cycle with time. How to acheive this?

TIA

[rajasekhar_reddy14]

Hi,

When you say 500mb file how many records it contains and how many update operation required on data base?

Suppose 500mb file is having 1 lac records in this case you need to perform 1 lac data base update operations using JDBC receive channel, it is really big challenge to JDBC adapter to process those many records in 3 hours time.

So you have to design your interface to reduce data base operations by using stored procedure and donu2019t put record set per message option value as 1. May be 200 is right number and try to update 200 records at a time ,i.e stored procedure should get 200 records as a one input.

Discuss with Data base team and request them to create stored procedure.

Regards,

Raj

[markangelo_dihiansan]

Hello,

Adding to the approaches mentioned above, there would be performance improvement if you would be using integrated configuration

http://www.sdn.sap.com/irj/scn/go/portal/prtroot/docs/library/uuid/700058f0-b1a1-2a10-39a8-ab2627b87...

Hope this helps,

Mark

[Former Member]

I would suggest you to go for Stored procedures and follow as mentioned below:

1)Use recordsets per message in the sender side FCC (already mentioned by u)

2) Create a SP and and pass source payload as xml input to the SP.

Ur target structure will be something like this:

http://help.sap.com/saphelp_nw04/helpdata/en/2e/96fd3f2d14e869e10000000a155106/content.htm

<StatementName>

<storedProcedureName action=u201D EXECUTEu201D>

<table>realStoredProcedureeName</table>

<param1 type=SQLDatatype>val1</param1>

</storedProcedureName >

</StatementName>

pass ur source data as xml input to param1.

If u r using PI7.1 then chk this:

http://www.sappi.sapag.co.in/flat-file-to-file-senario/convert-the-input-xml-to-string-in-pi-7-1-usi...

on the database side parse the string (xml document) and then insert the data in the table.. Chk with ur DB team regarding the same.

[Former Member]

Hi,

using Record Set Per Message may help you in splitting.

then you need to do message packaging.

please go thorugh this blog it will help you

http://www.sdn.sap.com/irj/scn/index?rid=/library/uuid/2016a0b1-1780-2b10-97bd-be3ac62214c7

regards,

ganesh.