Process JMS Recovery in Maximo

Objective:

Non – persistent data (GL transactions data) that are stuck / lost in JMS queues can be reprocessed using an existing API with Maximo automation script.

Pre-requisites:

1.       Create a folder jmsrecovery inside the Global Integration Directory specified in the System property mxe.int.globaldir

2.       Create a folder for a queue inside the Global Integration Directory. Replace backslash with dots in the folder name.

for example, for outbound sequential queue jms/maximo/int/queues/sqout, the folder name should be jms.maximo.int.queues.sqout

Automation Scripting:

Create an ACTION type script for any MBO object in Maximo.

https://github.com/bysurendar/maximo_processrecoveryapi/blob/master/automationscript_action.py

 

Launch Point of the script:

Input XML:

XMLs are automatically generated when there is an error on the JMS queue.

In case, we plan to resend records manually, then XML need to be created in the below specified format. 

 

XML tag and its values in Maximo application:

i)                     MSGPROP tag attribute values: MEAMessageID -> auto generated value ; destjndiname à same as queue name where the messages are stuck or need to be reprocessed ; interfacetype à External system name; SENDER à MX ; INTERFACE à Publish Channel name

ii)                   MSGDATA tag and its attributes: maximoVersion à Identified from System Help information ;

iii)                  Set and Data tag comprises of Object Structure name

https://github.com/bysurendar/maximo_processrecoveryapi/blob/master/inputjmsmsgdata.xml

The XMLs are placed in the queue folder inside Integration Global Directory à jmsrecovery à folder with queue name.

 

Write XML to JMS Queue:

The action of writing the XMLs to the queue can be done in 2 ways.

1.       Click on the Execute Script button in the Automation Script application for one time transfer.

2.       Create an escalation for a MBO with 1=1 condition and schedule the resend if required.

In this case, I created the ACTION in COMPANIES object. So, an escalation on COMPANIES object linked with the automation script ACTION can be used for automatic transfer of XML from the folder to the queue.

 

Open Cursor Monitoring in Maximo

We have a database parameter to set a maximum cursor limit for database connections.

Due to improper report design or code logic, there are possibilities that it can exceed the maximum cursor limit and cause slow response for users.

We never know when any user action will exceed the maximum cursor limit, so this monitoring can be automated with an escalation.


Escalation:
Object: DUMMY_TABLE
Condition:
exists ((SELECT  a.sql_id FROM V$sqltext A,(SELECT  sid, sql_id , Count (*) Count_Cursors From V$open_Cursor Where sid In (SELECT sid From V$open_Cursor GROUP BY sid HAVING Count (*) > 650)  GROUP BY sid, sql_id ) B WHERE A.Sql_Id = B.Sql_Id ) )
Escalation point:
Just select the Repeats check box.

The blue color highlighted number is the limit for alert. If the open cursors are greater than this value, we can trigger a communication template to System Administrator with below query to monitor the Database.

SELECT  b.cursors_count, b.sid, b.sql_Id, c.sql_text FROM V$SQLTEXT a,(SELECT sid, sql_id, count (*) cursors_count FROM V$OPEN_CURSOR WHERE sid in (SELECT sid From V$OPEN_CURSOR  GROUP BY sid HAVING count (*) > 650)  GROUP BY sql_id, sid) b , V$SQL c WHERE a.sql_id = b.sql_id and a.sql_id = c.sql_id ORDER BY b.SID, b.cursors_count DESC, b.sql_id, a.piece;

Reference: http://goo.gl/DHCbaq