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:
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:
--removeoption 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.