Delete before insert

[former_member207622]

Hi folks

I have a Proxy to JDBC scenario  .I have a requirement such that  Everytime records flow from ECC to PI via proxy .

At the target JDBC , first I need to delete the existings and nisert the fresh records .

Please let me know how should I proceed

Thanks

Ninad Sane

[Former Member]

Could you update records instead? There is a nice feature of the JDBC adapter that lets you insert a record if it does not exist, or update the non-key fields if it does exist.

You set the action = "UPDATE_INSERT"

Sorry if this doesn't meet your purposes, I know it is a little different than what you are asking.

[MichalKrawczyk]

Hi,

>>>At the target JDBC , first I need to delete the existings and nisert the fresh records .

alternatively you can call an stored procedure with the values which you want to insert

and inside this stored procedure delete the records first and then to the insert

Regards,

Michal Krawczyk

[former_member207622]

Can you guide me for the stored procedure thing , I am clueless

Thanks

Ninad Sane

[Former Member]

Hi,

you can have your DB admin to help you with the stored procedure.

Regards

[rajasekhar_reddy14]

Implementing stored procedure design is right idea because at data base level we can perform multiple operation easily using stored procedure.

Check with your Oracle team they will guide you and check in scn many documents and discusions available on Stored procedure concept.

[bhavesh_kantilal]

Multiple options exist depending on the exact data flow, volume etc.

The easiest I can think of is, you can use Multiple Inbound Interfaces with separate Interface Mappings in your Interface Determination and ensure option "Maintain Interface Order at Runtime" is selected. This will ensure that the Messages take a EOIO flow and hence until the Deletion is not done, the Insertion will not be done.

You need to ensure that the Deletion Message Interface is the 1st Inbound Interface.

Regards,

Bhavesh

[former_member207622]

Bhavesh , Thanks for your reply , Just a clarification I will create a message mapping  between source and target I will create target structure according to JDBC format and will put delete as action ,

I will not map anything  in this mapping as thi should delete the existing data in the tables

In my second mapping i will put insert and map everything

IN the integrated configuration How will I put two operation mapping ? will it ask for condition ?

thnks

Ninad

[baskar_gopalakrishnan2]

What Bhavesh says here is that you can specify both delete and insert interfaces as target interfaces in the operation mapping (7.1) or interface mapping (7.0).itself. Specify first interface is delete and second as insert. You just need to call the operation mapping in the interface determination. That's it.  You don' t need any condition in mapping here. This is enhanced interface determination.

http://www.sdn.sap.com/irj/scn/go/portal/prtroot/docs/library/uuid/90dcc6f4-0829-2d10-b0b2-c892473f1...

If you get help from DB team, I would go for Stored Procedure. That would be neat.

[Senthilprakash1]

Hi Ninad,

>>I will not map anything  in this mapping as the should delete the existing data in the tables.

I assume from above that every time your are planning to do Full refresh of the data into the table from ECC.

That is every time ECC is sending full load via Proxy and for inserting into the table after deleting the existing entries from the table.

well this approach is wrong.

1. If you follow this approach then you will need to ask ECC:

i. To Send all the data to PI in one single message which will be very high volume, this will be very cumbersome in SAP PI. -- Not recommended at all.

ii.If for some reason the deletion operation is not completed in the table. then the channel will error out in PI and it will be very difficult for you to reprocess the message as the single message will be of high volume and may cause inconsistency in the DB/table.

Right Solution would be:

1. Ask ECC to send the data as Multiple messages to PI with max size limit of 5MB each or something.

2. Have a additional field in the table as "inertTimeStamp" field (mapped to CurrentDate Node)-- which will tell the time of creation/update of the row in teh table.

3. Do only "Update_Insert" operation into the table.

4. Now this is important, talk with DB team and have then Schedule a TRIGGER or STORE Procedure in the DB which will systematically delete old records from the table based on the InsertTimeStamp field. (Say records older than 2 days..etc depending on the frequency) -- use your imagination over here.

In this way, there will not be any need of doing Deletion from SAP PI only we have to Insert into the table. and the Scheduled SP or TRIGGER will take care of deleting the old data from the DB.

Note:Deletion of rows from with in the DB using SP or TRIGGER will be much faster and efficient than trying to delte the entire table using external application queries.

Regards,

Senthilprakash.