Manual Trigger for Data Buffer Re-Run

[lawrence_prinsloo]

Hi all,

we're currently using the Data Buffering for a SQL Query Template which is called in a transaction and this is working for us, the scheduled buffer jobs run and the SQL query is executed when the connection is re-established.

The just of what the SQL will do in the transaction, is update the status of an entry in the database to processed if the transaction executes successfully. The status of the entry before this SQL runs will be "unprocessed". When the transaction runs, it'll get the list of all the "unprocessed" entries and go through them one by one. As it completes each entry, it'll update the database to show that entry as "completed" or "processed".

Now the scenario I'm worried about, is what if the connection fails and then is re-established. Before the Buffer Jobs have run, the Transaction runs and queries all "unprocessed" data from the database. Because the Buffered Jobs haven't run, this may mean the Transaction re-processes some of the data that's already been processed, but not updated in the database yet as the buffer jobs haven't run.

Is there anyway I can make the Transaction first initiate the Buffer Jobs before it carries through it's normal process? I'm assuming there must be some web service call to MII, or some means of running the job, just hope someone out there knows what it is.

Thanks.

[salvatore_castro · Accepted solution]

Lawrence,

How's it going...

If you're using v12.1 then you can use my custom actions for JDBC volume commit and rollback to manage your data instead of the standard data buffering since it seems like you are after a "Mass Commit/Rollback". You can download them from the Customization section of the MII Wiki.

If this will not work for you then another option would be to post the failed message into the Asynchronous MII HTTP Listener and process them to a category, then from a scheduled transaction read the entries out of the Category and process. You will have to use Message Services actions to update the status of each message.

Hope this helps,

Sam

[lawrence_prinsloo · Accepted solution]

Hey there Sam,

doing well man, hope you're having fun in the surf down under!

The "transaction" component of our MII Transaction is our RFC call to SAP and after that, we'll update the status in the MES database to processed. If the RFC call executes correctly, but the SQL update doesn't, we wouldn't want to reverse the RFC call, but rather wait till the SQL comes up again and update the status then.

I'll look into your message listener approach - that seems like the one that's going to solve our problem; Thanks!

I was hoping there would be a URL we could execute to trigger the buffered SQL statements to run, or whatever trigger the scheduler runs, that we could call that from the MII Transaction Do you know what the typical "re-query" schedule for the queue buffer to run is (it seems like a minute or so between each attempt)?

Thanks.

[salvatore_castro · Accepted solution]

Lawrence,

Yeah enjoying myself down here, had a good weekend at Moreton Island and am enjoying the weather quite a bit...

Anyway, the re-query schedule is based on the data server configuration of the connection, the Retry Count & Retry Interval are used.

Cheers,

Sam

[lawrence_prinsloo · Accepted solution]

That would be a logical place to have it Thanks!

[Former Member]

You could store completed items in a Queue and check if they are already in there before you process. You would have to set up a job to clean the Queue though.