6
Frequently Asked Questions

This chapter answers some of the most commonly asked questions about advanced queueing.

How are messages that have been dequeued but are still retained in the queue table accessed?

Access them using SQL. Messages in the queue table (either because they are being retained or because they have not yet been processed). Each queue has a view that you can use (see "Select the Number of Messages in Different States for the Whole Database" ).

Message retention means the messages are there, but how does the subscriber access these messages?

Typically we expect the subscriber to access the messages using the dequeue interface. If, however, you would like to see processed or waiting messages you can either dequeue by message id or use SQL.

Can the sort order be changed after the Queue table is created?

You cannot change the sort order for messages after you have created the queue table.

How do I dequeue from an exception queue?

The Exception queue for a multiconsumer queue must also be a multiconsumer queue.

Expired messages in multi-consumer queues cannot be dequeued by the intended recipients of the message. However, they can be dequeued in the REMOVE mode once (only once) using a NULL consumer name in dequeue options. Messages can also be dequeued from exception queue by specifying the message ID.

Expired messages can be dequeued only by specifying message ID if the multiconsumer exception queue was created in a queue table without the compatible parameter or with the compatible parameter set to '8.0'

What does the latency parameter mean in scheduling propagation?

If latency < 0 was specified in the propagation schedule, then the job is rescheduled to run after the specified latency. The time at which the job actually runs depends on other factors such as the number of ready jobs and the number of job_queue_processes. It may also be affected by the value for job_queue_interval. Please refer to the MANAGING JOB QUEUES chapter of the Oracle8i Administrator's Guide for more information on job queues and SNP background processes.

How can I control the tablespaces in which the Queue tables are created?

You can pick a tablespace for storing the queue table and all its ancillary objects via the storage_clause parameter in DBMS_AQADM.CREATE_QUEUE_TABLE. However, once you pick the tablespace, all IOTs and indexes created for that queue table will go to the specified tablespace. Currently, you don't have a choice to split them between different tablespaces.

How do you associate OPS instance affinities with Queue tables?

In 8.1 you can associate OPS instance affinities with queue tables. If you are using q1 and q2 in different instances, you can use alter_queue_table (or even create queue table) on the queue table and set the 'primary_instance' to the appropriate instance_id.

Can you give me some examples of a subscriber rule containing - message properties - message data properties.

Yes, here is a simple rule that specifies message properties - rule = 'priority 1';
here are example rules that specify a combination of message properties and data attributes: rule = 'priority 1 AND tab.userdata.sal 1000' rule = '((priority between 0 AND 3) OR correlation = ''BACK_ORDERS'') AND tab.userdata.customer_name like ''JOHN DOE'')'

Note that user data properties or attributes apply only to object payloads and must be prefixed with tab.userdata in all cases. Check documentation for more examples.

Is registration for notification (OCI) the same as starting a listener?

No. Registration is an OCI client call to be used for asynchronous notifications (that is, push). It basically provides a notification from the server to the client when a message is available for dequeue. A client side function (callback) is invoked by the server when the message is available. Registration for notification is both non-blocking and non-polling.

What is the use of non-persistent queues?

To provide a mechanism for notification to all users that are currently connected. The non-persistent queue mechanism supports the enqueue of a message to a non-persistent queue and OCI notifications are used to deliver such messages to users that are currently registered for notification.

Is there a limit on the length of a recipient list? Or on the number of subscribers for a particular queue?

Yes, 1024 subscribers or recipients for any queue.

How can I clean out a queue with UNDELIVERABLE messages?

You can dequeue these messages by msgid. You can find the msgid by querying the queue table view. Eventually the messages are moved to the exception queue (you must have the AQ background process running for this to happen). You can dequeue these messages from the exception queue with a normal dequeue.

Is it possible to update the message payload after it has been enqueued?

Only by dequeuing and enqueuing the message again. If you are changing the message payload then really it is a different message.

Can Asynchronous Notification can be used to invoke an executable every time there is a new message.

Notification is possible only to OCI clients. The client does not have to be connected to the database to receive notifications. The client specifies a callback function which will be executed for each message. Asynchronous Notification cannot be used to invoke an executable but its possible for the callback function to invoke a stored procedure.

Does propagation work from Multiconsumer queues to Single consumer queues and vice versa?

Propagation from a multiconsumer queue to a single consumer queue is possible. The reverse is not possible (propagation is not possible from a single consumer queue).

Why do I sometimes get ORA-1555 error on dequeue?

You are probably using the NEXT_MESSAGE navigation option for dequeue. This uses the snapshot created during the first dequeue call. After that the other dequeue calls generate more undo which fills up the rollback segment and hence generates 1555.

The workaround is to use the FIRST_MESSAGE option to dequeue the message. This will re-execute the cursor and get a new snapshot. This might not perform as well so we would suggest you dequeue them in batches - FIRST_MESSAGE for one and then NEXT_MESSAGE for the next say 1000 messages and then FIRST_MESSAGE again and so on.

What is the max number of queues that a table can have without affecting performance?

Performance is not affected by the number of queues in a table.

When messages are moved from one queue to another using propagation, is there any optimization to move the messages in batches, rather than one at a time?

Yes, it is optimized, propagation happens in batches.

Also if the remote queue is in a different database, we use a sequencing algorithm to avoid the need for a two-phase commit.

When a message needs to be sent to multiple queues in the same destination it is sent multiple times. If the message needs to be sent to multiple consumers in the same queue at the destination, it is sent only once.

What are the different subscriber types recorded on the subscriber table?

The subscriber_types and their values are:

1 - Current Subscriber. The subscribers name, address & protocol are in the same row.

2 - Ex subscriber - A subscriber that unsubscribed but had agent entries in the history aq$_queuetable_h IOT

4 - Address - Used to store addresses of recipients. The name is always NULL. address is always non-NULL.

8 - Proxy for Propagation - The name is always NULL.

database proxy to local queues, address=NULL, protocol=0

database proxy to remote queues, address=dblink address, protocol=0

3rd party proxies, address = NULL, protocol = 3rd party protocol.

After a message has been moved to an exception queue, is there any way, using SQL or otherwise, of identifying which queue the message resided in before moving to the exception queue?

No, AQ does not provide this information. To get around this, the application could save this information in the message.

What is the order in which messages are dequeued if many messages are enqueued in the same second?

When the enq_time is the same for messages, there is another field called step_no that will be monotonically increasing (for each message that has the same enq_time). Hence this helps in maintaining the order of the messages. There will be no situation when both enq_time and step_no are the same for more than one message enqueued from the same session.

Why do the JMS dbms_aqadm.add_subscriber and dbms_aqadm.remove_subscriber calls sometimes hang when there are concurrent enqueues or dequeues happening on the same queue to which these calls are issued?

Add_subscriber and remove_subscriber are administrative operations on a queue. Though AQ does not prevent applications from issuing administrative and operational calls concurrently, they are executed serially. Both add_subscriber and remove_subscriber will block until pending transactions that have enqueued or dequeued messages commit and release the resources they hold. It is expected that adding and removing subscribers will not be a frequent event. It will mostly be part of the setup for the application. Hence the behavior you observe will be acceptable in most cases. The solution is to try to isolate the calls to add_subscriber and remove_subscriber at the setup or cleanup phase when there are no other operations happening on the queue. That will make sure that they will not stay blocked waiting for operational calls to release resources.

Why do the TopicSession.createDurableSubscriber and TopicSession.unubscribe calls raise JMSException with the message "ORA - 4020 - deadlock detected while trying to lock object"?

CreateDurableSubscriber and unsubscribe calls require exclusive access to the Topics. Hence if there are pending JMS operations (send/publish/receive) on the same Topic before these calls are issued, the ORA - 4020 exception is raised.

There are two solutions to the problem:

1. Try to isolate the calls to createDurableSubscriber and unsubscribe at the setup or cleanup phase when there are no other JMS operations happening on the Topic. That will make sure that the required resources are not held by other JMS0 operational calls. Hence the error ORA - 4020 will not be raised.

2. Issue a TopicSession.commit call before calling createDurableSubscriber and unsubscribe call.

Why doesn't AQ_ADMINISTRATOR_ROLE or AQ_USER_ROLE always work for AQ applications using Java/JMS api?

In addition to granting the roles, you would also need to grant execute to the user on the following packages:

• grant execute on sys.dbms_aqin to <userid>

• grant execute on sys.dbms_aqjms to <userid>

Why do I get java.security.AccessControlException when using JMS MessageListeners from java stored procedures inside Oracle8i JServer

To use MessageListeners inside Oracle8i JServer, you can do one for the following

1. GRANT JAVASYSPRIV to <userid>

2. call dbms_java.grant_permission ('JAVASYSPRIV', 'SYS:java.net.SocketPermission',

'*', 'accept,connect,listen,resolve');