Using Interface Tables to Insert Records - Continuous Vs Sequential Queue

In our world, we come across different requirements and problem statements for which we need to provide solution. It is not necessary that each solution we design or implement would be agreed upon universally. We had this requirement to generate records automatically for one of the application in MAXIMO. At the same time, record needs to be created in the parent object as well as records need to be entered to its child object. So, definitely we need to think about Object Structure, Enterprise services, External Systems and off course Interface Tables to insert records to our MAXIMO main objects.

For this data import to MAXIMO through interface tables, we need to consider or keep in mind the following:

1. Object Structure - The object structure with the required objects and parent-child relationships should be created. Exclude/Include fields should be used appropriately keeping in mind what columns need to be included in the interface table.
2. Enterprise Service - Enterprise Service should be created with the above created object structure and the intended name of the Interface Table mentioned. You can check to enable message tracking.
3. External System - You can create a new external system or use an existing one but ensure that end point selected is "MXIFACETABLE", sequential queues and continuous queues properly configured. 
• Outbound Sequential Queue = jms/maximo/int/queues/sqout
• Inbound Sequential Queue = jms/maximo/int/queues/sqin
• Inbound Continuous Queue = jms/maximo/int/queues/cqin
• In the Enterprise Services tab for this external system, add the above created enterprise service, check the "Enabled?" check box, check or uncheck the "Use Continuous Queue?" checkbox.
• From action menu, now select the Create Interface Tables action. In the dialog, select the interface table name mentioned with the enterprise service and click on Create button. It will take a few seconds to process your request.
4. Cron Task - In Cron Task Setup application, open "JMSQSEQCONSUMER" cron task and ensure that two cron task instances, one for sequential In and one for sequential Out are active. Next, open the "IFACETABLECONSUMER" cron task. A cron task instance should be present which is active with its scheduled defined. The properties for this cron task instance should have QUEUETABLE=MXIN_INTER_TRANS and END POINT=MXIFACETABLE.
5. Writing data to Interface Table - At this stage, everything on the Maximo side has been set up, and we need to do to create some kind of routine to send data to the interface tables. The cron task IFACETABLECONSUMER, then polls the inbound interface queue table for new records to process as per the schedule set. 
• Automation Script: For every record that needs to be added to the MAXIMO object, we need to add a new row to MXIN_INTER_TRANS object as well as to the Interface Table. For our convenience, we have used an automation script to include insert scripts for MXIN_INTER_TRANS and the interface table created above.
• Cron Task: To trigger or initiate the above automation script, we have set up a new cron task with class=com.ibm.tivoli.maximo.script.ScriptCrontask. A new cron task instance also needs to be created with "Active?" checkbox checked and appropriate schedule defined. The properties for this cron task instance include SCRIPTARG=0 and SCRIPTNAME=<above automation script name>

With above configurations, we were able to create new records for the application, as stated above. Now, moving over to the confusion over using continuous queue or sequential queue for this approach. We had initially used continuous queue for this inbound processing of records to MAXIMO objects. If an incoming message gets stuck in sequential queue, other subsequent incoming messages are not processed until the error message is either reprocessed or deleted. To avoid this, we had gone ahead with continuous queue. It was working fine in our development instance. When this was moved to TEST and Production instance, we started facing an error of Database Error 1 in some of the incoming messages. 

I need to mention here that this database error 1 had come for primary key attribute of the main object in the object structure.We checked the logic in automation script to see if we were trying to insert any duplicate value to this attribute. This may have been due to continuous queue trying to insert two records with same ID generated. If we would reprocess the message with status=ERROR, it eventually gets processed. To handle this, we tried two approaches.

1. We tried with increasing the Max Try Count, this issue got reduced but it was not fully resolved, i.e., the number of failed messages decreased but issue was still prevalent for a few messages. 
2. We changed the inbound processing queue to sequential. We have never come across the database error 1 for our primary key attribute again and it has been seven to eight months since :) 

With the above experience, we can conclude that if the order of processing is important, like in our case above, we should use Sequential Queue rather than Continuous queue. Continuous inbound processing works better for transaction records like invoices, etc. Please do share your experiences and thoughts.