Technical info and insight on using Oracle Documaker for customer communication and document automation

Kickstarting a Stuck Transaction in ODEE

Andy Little
Technical Director

Over the past few years, I've been asked by several ODEE customers if it is possible to restart a stuck transaction. My first response is always why is the transaction stuck? If there's a reason why your transactions are getting stuck somewhere in the system, you will probably want to figure out why that is so you can solve that particular problem. But, sometimes, a transaction is sitting there because something happened - maybe you're in a test system that is subject to anomalous behavior, or maybe you accidentally killed a service you shouldn't have, or maybe someone got root access who shouldn't have... in any event, sometimes things just happen. Normally my advice is to run the transaction again - you can run DoPublishFromFactory to create a new job or transaction from an existing one, so this is usually the best advice. You can also grab the input XML data from an existing transaction or job if you really want to start over from scratch. This usually satisfies any need to regenerate a transaction, but what about that stuck transaction, sitting there with some status code, just waiting to be cleared? Sure, you could create a Historian rule filter to grab anything that's sitting at an odd status code for a long period of time, but... well I have to admit, at this point curiosity got the better of me and I decided that I wanted to explore if it was even possible to get a transaction moving again, and the answer is yes you can.

I wrote a bit of Java code that you can find over on my Github repository that will takes some parameters for input, including:

  • one or more Transaction IDs
  • database class name, connection string, and credentials
  • queue connection factory and queue JNDI names
  • removal option 
  • WebLogic JMS URL

The utility will connect to the database to validate that the transaction IDs exist, and will optionally remove existing form set data, and/or related BCHS/RCPS/PUBS records, will set a new TRNSTATUS code, and finally post a message to a JMS queue for each updated transaction ID. When I put this together I had two use cases in mind:

  • Use Case 1 - Move a stuck transaction - A transaction stuck in DocFactory an arbitrary status can be moved to a previous status. E.g. if a transaction is stuck at 311 status, you can change it back to 221 to run it through Assembler again, in which case the TRNSTATUS will be updated and a message posted into the Assembler's work queue. The default configuration for this behavior target status = 221 and target queue = Assembly Line 1's Assembler queue, so if you have a different use case you will need to know your desired target status and queue.
  • Use Case 2 - Reprocess a transaction - A transaction in complete status 999 can be processed again, but not as a new transaction. This means that the transaction will keep its TRN_ID, and any existing BCHS, RCPS, and PUBS will remain associated with the transaction. If you need a wholly new transaction, use doPublishFromFactory instead. You also have the ability to specify the --remove option to remove any existing BCHS, RCPS, and PUBS associated with the transaction.

Both of these cases work well with this utility, but be advised it is provided without any warranty of any kind. To use the tool, download the code from GitHub, and then make sure you have the prerequisite JAR files available. Compile the tool:

javac -cp ./commons-cli-1.4.jar:./ojdbc7.jar:./wljmsclient.jar:. TransUpdate.java

Run the tool with settings for your environment:

java -cp ./commons-cli-1.4.jar:./ojdbc7.jar:./wljmsclient.jar:. TransUpdate -r 3 -i 444,490,433 -s 221 -c jdbc:oracle:thin:@localhost:1521:orcl -p MyPassWord -u MyUser -w t3://localhost:11001

And have a look at the output:

Database connection opened.
Transactions to process: 3
============ 1. TRNID 444 ============
TRANS 444 located.
TRNS NAPOL data deleted.
Performing sweep for TRNID 444
3 PUBS releated to TRNID 444 deleted.
1 BCHS releated to TRNID 444 deleted.
3 RCPS releated to TRNID 444 deleted.
3 BCHS_RCPS releated to TRNID 444 deleted.
TRANS 444 updated to TRNSTATUS = 221
Message posted to jms.al1.qcf/jms.al1.assemblerreq on t3://localhost:11001 for TRNID 444
============ 2. TRNID 222 ============
TRANS 490 was not located.
============ 3. TRNID 433 ============
TRANS 433 located.
TRNS NAPOL data deleted.
Performing sweep for TRNID 433
3 PUBS releated to TRNID 433 deleted.
1 BCHS releated to TRNID 433 deleted.
3 RCPS releated to TRNID 433 deleted.
3 BCHS_RCPS releated to TRNID 433 deleted.
TRANS 433 updated to TRNSTATUS = 221
Message posted to jms.al1.qcf/jms.al1.assemblerreq on t3://localhost:11001 for TRNID 444
Database connection closed.

More details on how to use the tool are provided over at Github. If you need to set up an ODEE environment, I invite you to check out any of my previous blog posts. If you have any issues you are welcome to log the issue on Github, comment below, or over in the Documaker community although please be aware that this is unsupported code.

Be well and take care.

Be the first to comment

Comments ( 0 )
Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.