Trigger Alerts for Messages in "To be delivered" status: Monitoring Adapter queue lock

[shweta_walaskar2]

We were facing problems with Adapter queue locks when a number of messages used to be stuck in RWB and were held in 'To be delivered' status. This had critical impacts in case of some scenarios like warehouses where even a delay of an hour in message transfer was not acceptable. Hence we tried to find a solution to set up automatic alerting for messages in 'To be delivered' status and would like to share this with XI colleagues. There is no standard way to enable alerts for messages in ‘To be delivered’ status. A possible reason for this status is adapter queue lock. Adapter queues reside in Java stack and we couldn't find this information in any table in ABAP stack. As this information is stored in Oracle database ,we decided to read this data from Oracle database and then it could be used to raise alerts. Table XI_AF_MSG is the table which contains this information. As ‘To be delivered’ status for a short duration (2-3 seconds) is not a problem, this shouldn’t be a criteria to raise alerts. Hence, we have taken criteria to raise alerts considering following two points:  1) In case of processed messages with delay (problem of stuck queue resolved by itself after sometime), this delay is reflected in start and end time of message at RWB.   ABAP stack shows no delay in message processing.  “Alert has to be raised when difference between start time and end time is more than 15 minutes.”  2) In case of messages having status ‘To be delivered’, ‘Waiting’, ‘Holding’, ‘Delivering’ for a long time, end time is not populated. In such cases, the criterion to raise alerts is:  “Raise an alert where difference between start time and current time is more than 15 minutes”  In order to achieve this, we have configured a File-to-File scenario with JDBC lookup. Input and output files are dummy files and just used to trigger the JDBC lookup process. *Query to select such records:* Query = "select MSG_ID, a, b, diff "+         "from (SELECT msg_id,    "+         " SUBSTR(SENT_RECV_TIME,1,30) a,   "   +         "SUBSTR(TRAN_DELV_TIME,1,30) b,systimestamp \"Systimestamp1\","+         "case when TRAN_DELV_TIME is null then       "+         " ((to_date(to_char(systimestamp,'dd-mon-yyyy')) - to_date('1-Jan-2000','dd-mon-yyyy')) *24 *60 +           substr(systimestamp, instr(systimestamp,' ')+1,2) *60 +        substr(systimestamp, instr(systimestamp,' ')+4,2)) "+         " -        ((to_date(to_char(SENT_RECV_TIME,'dd-mon-yyyy')) - to_date('1-Jan-2000','dd-mon-yyyy')) *24 *60 +   substr(SENT_RECV_TIME, instr(SENT_RECV_TIME,' ')+1,2) *60 +        substr(SENT_RECV_TIME, instr(SENT_RECV_TIME,' ')+4,2))"          +" else ((to_date(to_char(TRAN_DELV_TIME,'dd-mon-yyyy')) - to_date('1-Jan-2000','dd-mon-yyyy')) *24 *60 +   substr(tran_delv_time, instr(tran_delv_time,' ')+1,2) *60 +        substr(tran_delv_time, instr(tran_delv_time,' ')+4,2))                -        ((to_date(to_char(SENT_RECV_TIME,'dd-mon-yyyy')) - to_date('1-Jan-2000','dd-mon-yyyy')) *24 *60 +   substr(SENT_RECV_TIME, instr(SENT_RECV_TIME,' ')+1,2) *60 +        substr(SENT_RECV_TIME, instr(SENT_RECV_TIME,' ')+4,2)) "+
        "end diff "+
        "FROM XI_AF_MSG  ) "+
        "where diff > 15"

*2) Suppress multiple alerts:*  Communication channel polling interval is set to 15 minutes to enable check for such messages after every 15 minutes. A Dummy File Sender Channel is used:    This CC is only used to set polling interval to 15 minutes as we don’t have Availability Time Planning feature in SP17.  * JDBC Receiver CC used to call JDBC lookup: *
 

 

  Once, a message satisfying above mentioned criteria is identified, an alert will be raised.
But as we are querying the database, after 15 minutes, same message will lead to another alert though this would have already been taken care of.
 
Hence, there should be a way to disable selection of same messages next time.
 
In XI JDBC scenarios, there is a section for Update query which is mandatory after select query. The purpose of this is to disable selection of same messages again and again.
 
But, we wouldn’t prefer to have direct update in Oracle database.
 
Hence, we have followed an alternative approach to achieve this.
 
We have created a Database table (zbxi_t0012) in XI ABAP stack .This table has two fields: XI Message ID and Message creation date.
 
 
*ABAP Development*
 

 
*FUNCTION Z_ZBXI_SELECT_UPDATE_MSG_ID*   FUNCTION Z_ZBXI_SELECT_UPDATE_MSG_ID. *"---- ----------------------------------------------------------------- *"*"Local Interface: *" IMPORTING *" VALUE(I_MSGID) TYPE CHAR40 *" EXPORTING *" VALUE(E_MSGID) TYPE CHAR40 *"---- ----------------------------------------------------------------- DATA: lt_zbxi_t0012 TYPE STANDARD TABLE OF zbxi_t0012, ls_zbxi_t0012 TYPE zbxi_t0012, itab_zbxi_t0012 TYPE standard table of zbxi_t0012 with header line, lv_msgid TYPE zbxi_t0012-msg_id, lv_date TYPE zbxi_t0012-creation_date, lv_refdate TYPE sy-datum, l_tst TYPE timestampl, tzone TYPE timezone, lv_creation_date TYPE TIMESTAMPL. lv_msgid = i_msgid. tzone = 'UTC '. * Reference Date is taken as 3 months, so we wouldn't check messages older than 90 days lv_refdate = sy-datum. lv_refdate = lv_refdate - 90. * Convert Reference date in timestamp format CONVERT DATE lv_refdate INTO TIME STAMP lv_date TIME ZONE tzone. *Get message creation date select single INITTIMEST into lv_creation_date from SXMSPMAST where MSGGUID = lv_msgid. * Populate creation date in a variable select single MSG_ID CREATION_DATE from ZBXI_T0012 into (lv_msgid,lv_creation_date) where MSG_ID = lv_msgid . * If message already exists in table(alert has already been raised) ,return a dummy message ID, which would be used in * XI mapping as a check that alert shouldn't be raised in this case. if sy-subrc = 0. e_msgid = '00000000000000000000000000000000'. endif. *If message doesn't exist in ZBXI_T0012, check if it is older than 3 months,if yes, return dummy message ID again,no alert * will be raised if sy-subrc <> 0. if lv_creation_date lt lv_date. e_msgid = '00000000000000000000000000000000'. * If message doesn't exist in ZBXI_T0012, and if it is not older than 3 months ,update ZBXI_T0012 with this message, * return message ID,and raise an alert using XI mapping else. ls_zbxi_t0012-msg_id = lv_msgid. ls_zbxi_t0012-creation_date = lv_creation_date. INSERT zbxi_t0012 FROM ls_zbxi_t0012. e_msgid = lv_msgid. endif. endif. *Delete messages older than 3 months select * FROM ZBXI_T0012 into itab_zbxi_t0012 WHERE CREATION_DATE lt lv_date. if sy-subrc = 0. DELETE zbxi_t0012 from table itab_zbxi_t0012. endif. endselect. ENDFUNCTION. •    JDBC lookup is used to select records which satisfy the above mentioned criteria. This UDF will return all such Message IDs   *XI Message Mapping: *

*UDF getResultSet*