Please kindly share your solutions on the below one;
Let’s say I have a xml file which have a couple of records. My need is to push these records to target DB.
For this, I have created a JDBC connection with local transaction Type to control transactions explicitly. Created a JDBC insert adapter which is used to push records to target DB.
Case1:
In the try block, under the loop parsed the 1st record, pushed to Db. Repeated the same with other records,after the loop step, kept commitTransaction step which takes care of commiting the records in DB table.
Case2:
In the try block, under the loop parsed the 1st record, pushed to DB, when performing the same to 2nd record,DB went down, so control goes to catch block. As part of catch I have kept rollBackTransaction which takes of rolling back the transaction.
Now my requirement is I have to push the records in DB after some time when the DB comes up. I don’t want to use publish mechanism here as I don’t want to use Broker. I can’t use filePolling as if it sees any issues it pushes the file to errored directory which must different from monitoring directory.
Please suggest what all different ways to get my work done and advantages of grabbing the way.
1> Use batch insert adapter service to insert the records in one go and play around this a little bit. (you must see number of records you get at run-time)
2> If it is a large xml file, you must consider of handling large xml file (node iterator logic in wM)/enhanced xml parser from wM 9.0 and above.
3> Handle transient error for DB going down due to network issue or db unavailable (throwExceptionForRetry logic)
4> You can have resource monitoring service but this requires broker/UM where you configure this in your trigger.
3> Handle transient error for DB going down due to network issue or db unavailable (throwExceptionForRetry logic)
Here I can even use, repeat step, by setting properties, repeat on Failure, interval, count which avoids make use of above step.
4> You can have resource monitoring service but this requires broker/UM where you configure this in your trigger.
I don’t want to use Broker, as I don’t have broker in my environment.
Yes, repeat on failure will help having correct properties set. Since you do not have broker try using the local publish to IS and see if you are able to achieve the same functionality without broker. Just a thought!
As you are writing the same data in 2 tables, try the below approach.
Write the data in 1st table say “A” set flag as ‘PENDING’
Write a service which wraps an jdbc batchInsert adapter which picks all records from the “A” table, flushes everything with batchInsert with localTransaction type in “B” table and update back “A” table with flag ‘Completed’ or take each record, insert into “B” table, update flag in “A” table.
With above there is no miss of records in B table which are exist in A table