10 Replies Latest reply: Feb 6, 2012 11:03 AM by phcullen-Oracle RSS

    AQ to MQ Message Sequence Issue

    894284
      Hi,

      I am using Advanced queues on "Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi".

      The Advanced Queue is created - next many message(s) are enqueued one-at-a-time in order - part of the code is shown below....

      LOOP

      -- Convert to XML
      msg_xml := XMLType.createXML(msg, NULL);

      -- Add message to the queue
      enqueue_options.VISIBILITY := DBMS_AQ.ON_COMMIT;
      enqueue_options.SEQUENCE_DEVIATION := null;
      msg_properties.EXPIRATION := DBMS_AQ.NEVER;

      DBMS_AQ.ENQUEUE ( queue_name => 'Q_EXPR_MSG_TXN',
      enqueue_options => enqueue_options,
      message_properties => msg_properties,
      payload => msg_xml,
      msgid => msg_handle);


      END LOOP;


      The AQ sends the messages to an MQ - the MQ end is reporting messages are arriving as a batch (not one-at-a-time as placed on the AQ) and out of sequence...

      I can understand why the AQ sends the messages across as a batch however is this possible to change to send across one message at-a-time...?

      Why are the messages arriving out of sequence ..? (is this to do with batching)

      Regards Ian
        • 1. Re: AQ to MQ Message Sequence Issue
          phcullen-Oracle
          Hello,

          How is the queue table defined?

          How are you propagating the messages to MQ?

          What version of MQ is it?

          Thanks
          Peter
          • 2. Re: AQ to MQ Message Sequence Issue
            894284
            The queue...
            OWNER     ID_FEED_OWNER
            NAME     Q_EXPR_MSG_TXN
            QUEUE_TABLE     QT_EXPR_MSG_TXN
            QID     131912
            QUEUE_TYPE     NORMAL_QUEUE
            MAX_RETRIES     5
            RETRY_DELAY     0
            ENQUEUE_ENABLED     YES
            DEQUEUE_ENABLED     YES
            RETENTION     0
            USER_COMMENT     (null)
            NETWORK_NAME     (null)
            CREATED     06-JAN-12
            LAST_DDL_TIME     02-FEB-12

            The queue table...

            OWNER     ID_FEED_OWNER
            QUEUE_TABLE     QT_EXPR_MSG_TXN
            TYPE     OBJECT
            OBJECT_TYPE     SYS.XMLTYPE
            SORT_ORDER     ENQUEUE_TIME
            RECIPIENTS     MULTIPLE
            MESSAGE_GROUPING     NONE
            COMPATIBLE     10.0.0
            PRIMARY_INSTANCE     0
            SECONDARY_INSTANCE     0
            OWNER_INSTANCE     1
            USER_COMMENT     (null)
            SECURE     NO
            CREATED     06-JAN-12
            LAST_DDL_TIME     06-JAN-12

            Not sure about the MQ version as its on our clients side - i'll try and find this out....

            Regards
            • 3. Re: AQ to MQ Message Sequence Issue
              phcullen-Oracle
              Hello,

              How are you propagating the messages to MQ? Are you using MGW or the Procedural Gateway? Or something else?

              Thanks
              Peter
              • 4. Re: AQ to MQ Message Sequence Issue
                894284
                Hi,

                I am using the Oracle Messaging Gateway....

                To queue the messages I am using Oracle package 'DBMS_AQ' and calling 'ENQUEUE(...)'....

                DBMS_AQ.ENQUEUE ( queue_name => 'Q_EXPR_MSG_TXN',
                enqueue_options => enqueue_options,
                message_properties => msg_properties,
                payload => msg_xml,
                msgid => msg_handle);

                Regards Ian
                • 5. Re: AQ to MQ Message Sequence Issue
                  phcullen-Oracle
                  Hello,

                  By default MGW will send messages in batches of 30 and you can control this via

                  setJavaProp oracle.mgw.batch_size=<1 to 100>

                  in the mgw.ora. If you want them to go 1 by 1 you could set this to 1.

                  When you say they are arriving out of sequence what do you mean exactly? Is each message independent of each other or are they linked in some way.

                  Thanks
                  Peter
                  • 6. Re: AQ to MQ Message Sequence Issue
                    894284
                    Thanks Peter..

                    As part of each message there is a numeric count field starting at 1 to Infinite - messages are queued in order of this number...

                    Presently messages are being sent in batches of 30 as you stated....

                    On the MQ side when the messages are de-queued the messages are not in the same order they were queued - the dequeuing process is looking at the numeric count field and they are out of sequence...

                    Regards Ian

                    Edited by: user4033511 on 03-Feb-2012 09:36
                    • 7. Re: AQ to MQ Message Sequence Issue
                      phcullen-Oracle
                      Hello,

                      I am a bit surprised that they are out of order within a batch as the AQ component of MGW would just dequeue them in the order in which they were enqueued.

                      I would need to know the MQ version of the server and the MQ client that you are using and the MGW setup steps.

                      The fact that you have a sequence number implies that the messages need to be processed in order. Is that correct or are they independent?

                      Thanks
                      Peter
                      • 8. Re: AQ to MQ Message Sequence Issue
                        894284
                        Hi,

                        "The fact that you have a sequence number implies that the messages need to be processed in order. Is that correct or are they independent?" - the client has asked we add a sequence number to each message - I think the client use the message sequence number to identify any messages that 'go missing' so I think the order the messages are de-queued is important as they probably have logic to raise an alert for out of sequence messages...

                        In reagrd to your MQ questions I have emailed the client and I am waiting for a reply - I made the chnage you suggested in the .ora file and will test this shortly....

                        Regards Ian
                        • 9. Re: AQ to MQ Message Sequence Issue
                          894284
                          The client is using MQ7 for everything...
                          • 10. Re: AQ to MQ Message Sequence Issue
                            phcullen-Oracle
                            Hello,

                            As per Note 219410.1 in MOS 10.2 is not officially certified with MQ V7 and never will be now because 10.2 is in sustaining support. V7 caused some issues with MGW which affected access to the logging queues and therefore affected the propagation algorithm.

                            I would check that the MQ client and server version are 7.0.1.3 or higher as that fixed the main issue but it does not mean that there are not others.

                            If this issue can be reproduced with 11.1.0.7 + patches as per Note 219410.1 or 11.2.0.3 then you should log a service request and the behaviour can be investigated.

                            Thanks
                            Peter