Skip to Content
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*  

To report this post you need to login first.

7 Comments

You must be Logged on to comment or reply to a post.

  1. Shweta Walaskar Post author
    Hello,

    Thanks a lot for your comments.Following points have been added:

    1)Before describing the technical configuration, add which end to to end scenario have you configured, file to ?? via jdbc lookup.—>

    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.

    2) Also mention in brief which step will raise Alert as alerts are triggered only when error is encountered.—->

    If a message doesn’t exist in zbxi_t0012 and it is not older than 3 months, this FM would first update zbxi_t0012 with this message ID and then, would return the same message ID which would be passed to UDF throwAlert to throw a runtime exception leading to mapping failure.

    (0) 
  2. Jorge Lopez
    Thanks for sharing.

    Did you first try to find out why it was taking so long for some queues in the Adapter Engine to proccess the messages? Maybe it would have been enough by increasing the number of threads for the specific adapter giving problems. Also setting priority in the AE queues.

    As an alternative to a custom solution, I would have thought of using the Java Scheduler, available from PI 7.1, to schedule a job to run every 15 minutes and do the query. You would need to deploy an EJB in the JEE for that.

    (0) 
    1. Shweta Walaskar Post author
      Thanks a lot for the comments and a better solution to overcome this problem.

      In our case,these messages used to be in status ‘To be delivered’ always when SOAP server on receiver side was down.Because of these messages ,subsequent messages were blocked.Increasing number of threads didn’t help us.
      This problem used to be solved whenever SOAP server on receiver end was restarted, but by the time,it was done ,there used to be a delay which wasn’t acceptable,hence it was important for us to know this on time.

      We had no option of using Java Scheduler as we were working on XI3.0

      But nice to know a better option to achieve this.

      (0) 
  3. Sherin Jose

    Hi Shweta,

    This is a great blog and exactly what i need at the moment.

    We have a similar requirement in our system landscape and this looks to be more perfect. Could you please share the UDF used here as i am finding it difficult to read it from the attached sheet in the blog.

    (0) 

Leave a Reply