How to control the Batch Insert SQL using LOCAL_TRANSACTION

, ,
1

Dear All,

I have problem while using Batch Insert SQL using LOCAL Transaction.

I am trying to insert record list (not more than 5) into Oracle database using Batch Insert SQL. The records have internal id as primary key.

There are chances that record with same internal id is repeated alongwith new records (another new 4 records). Now in such cases, the batch insert fails as one of the record violates the unique constraint rolling back the complete transaction.

Is it possible to configure the Adapter service to ignore the repeating record and continue inserting the new records ?

Your reply will be appreciated.
Thanks

2

Batch Insert Adapter Service is expected to fail and roll back all insertions of the batch when any of the batch record fails.

Better to use other approach if you want to control partial batch processing.

HTH!

3

why dont u check for existing records present in database…
I mean perform some logic in webMethods to remove exisiting records…
This will resolve ur issue

4

Thanks Pankaj.

I’d already thought of this approach.
Actually, the database will be very huge in size and am worried about the response time that would take to check the record before every insert.

5

may i know how many records do u need to insert at a time in batch insert …
If u have less no of records than why dont u insert one record at a time instead of doing batch insert so that records which doesnt exits will be inserted perform explicit commit… If its throwing errror than ignore that records and go on inserting next records.

6

Here is an approach that I use to perform “upserts”:

  1. Insert the record.
  2. Catch errors and examine the DB error code/message that indicates “unique key constraint violated”. If that is the error, then update the record.

This is the only approach that will safely update the DB table in the face of multiple DB users writing to the table at the same time.

One may be tempted to do this:

(DO NOT DO THIS)

  1. Read the record.
  2. If not found, insert.
  3. If found, update.
    (DO NOT DO THIS)

This will not work when multiple users or multiple threads are updating the table at the same time–there is the possibility of a race condition wherein 2 or more threads do the read at the same time, all determine that the record does not exist and all then try to insert. Only the first will succeed.

The approach of “insert, catch error, update” works but is a bit noisy in the wM IS server and error logs. You might consider having someone create a stored procedure to do this logic and call that stored procedure from IS.

HTH

P.S. Forgot to mention that this isn’t just a simple configuration task. You’ll need to write some FLOW steps to do this.