|Oracle8i Application Developer's Guide - Advanced Queuing
Release 2 (8.1.6)
Part Number A76938-01
This chapter answers some of the most commonly asked questions about advanced queueing.
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" ).
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.
You cannot change the sort order for messages after you have created the queue table.
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'
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.
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.
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.
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.
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.
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.
Yes, 1024 subscribers or recipients for any queue.
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.
Only by dequeuing and enqueuing the message again. If you are changing the message payload then really it is a different 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.
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).
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.
Performance is not affected by the number of queues in a table.
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.
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.
No, AQ does not provide this information. To get around this, the application could save this information in the message.
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.
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.
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.
In addition to granting the roles, you would also need to grant execute to the user on the following packages:
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',