6 Replies Latest reply on Nov 9, 2004 2:24 PM by 388003

    How to Identify Message in the Hub

      Here's a scenario I'm working on for tracking a message from start to finish (Publish to Subscribe):

      1. Database trigger fires and creates and publishes a message. I capture the messageID and other stuff and write it to a tracking table.

      2. Adapter picks up the message and writes to the Hub.

      3. At this point, let's say the subscribing adapter is down, how can I determine what the message is that's sitting in the Hub? Does the messageID generated in step 1 get carried into the Hub? If so, how can I determine this?

      Thanks for any help or suggestions.
        • 1. Re: How to Identify Message in the Hub

          The messageID generated by DB adapter is carried to the hub but it is encapsulated in the message body which goes to the "USER_DATA" column of the oai_hub_queue.
          One more of way of tracking a message is by using tracking fields facility in iStudio. Here you can specify one or more application view fields(unique for a messagetype) during design time. During runtime a particular message can be tracked using Oracle Enterprise Manager by specifying the messagetype and the value of the fields specified during design time.


          • 2. Re: How to Identify Message in the Hub
            Thank you for your reply.

            I researched the user_data column, and I have sifted through the AQ$_JMS_BYTES_MESSAGE object type. But which column contains the message id? I believe that I have looked at all of the columns (including the nested objects), but I can't figure out the column.

            • 3. Re: How to Identify Message in the Hub

              You have to look at the header properties in USER_DATA column.
              So the full path to look at is:
              One of the properties in AQ$_JMS_USERPROPARRAY will contain the MSGID which you are looking for.

              • 4. Re: How to Identify Message in the Hub
                Just as a thought, have you thought about using the Tracking Fields functionality? If you are using OEM Console, then leveraging this functionality is worthwhile too.
                • 5. Re: How to Identify Message in the Hub
                  Sorry, just read Sandeep's first reply above after my last post. Perhaps I should have used a "Tracking Field" before I replied - lol :-)
                  • 6. Re: How to Identify Message in the Hub
                    The problem of using the tracking field are :
                    - you depend on the OEM console
                    - tracking field is not generic

                    There's some solution to improve the tracability also it's not perfect and i wait for a better solution from Oracle

                    1) You can activate the OAI_HUB_QUEUE queue retention - you can now see the message processed but you loose the data history ( user_data field is empty when message is PROCESSED )

                    2) If you have DB adapters, you can also use the following properties in adapter.ini :
                    These parameters activate the queue persitence instead of the default file persistence which main inconvenience is not to be readable.
                    The main adavantages of these parameters is to keep the message in READY mode in the OAI_HUB_QUEUE when one subscriber is down or if there is an applicative error.
                    You can then easily read the message (I have the code if you need) or delete the message with a dequeue (it's better than a direct delete in the queue table which could be dangerous)

                    3) Another solution to track message is to look into log files - i use PERL to do that sometimes particulary to reconstruct message

                    4) An Oracle australian Consultant has built up a valuable solution which is called OAIMM and should be shared to everybody - but this solution as also maintance constraints

                    5) I'm looking for a more generic solution based on a trigger on OAI_HUB_QUEUE - but unlucky a trigger does not fire for an update on a BLOB - which is done by the adapters with the dbms_lob.write call

                    If someone found the solution, please POST we need it !