|Oracle9i Application Developer's Guide - Advanced Queuing
Release 1 (9.0.1)
Part Number A88890-02
This section answers some of the most commonly asked questions about Advanced Queuing.
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 "Selecting 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 multiconsumer 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 an 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 a latency less than 0 was specified in the propagation schedule, 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 Oracle9i Database 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 do not 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
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
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 =
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 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, 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 it is 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 suggest you dequeue them in batches:
FIRST_MESSAGE for one, and
NEXT_MESSAGE for the next, say, 1000 messages, and then
FIRST_MESSAGE again, and so on.
subscriber_types and their values are:
1 - Current Subscriber. The subscribers name, address and protocol are in the same row.
2 - Ex subscriber - A subscriber that unsubscribed but had agent entries in the history
4 - Address - Used to store addresses of recipients. The name is always
NULL. The address is always non-NULL.
8 - Proxy for Propagation - The name is always
database proxy to local queues, address=
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.
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
step_no are the same for more than one message enqueued from the same session.
In Oracle9i, OMB functionality is provided in the Oracle database. So, if you are using the Oracle9i database, use the functionality offered by the database.
You do not need OMB.
With Oracle8i, use OMB in the following scenarios:
Use JMS functionality directly from the database in other scenarios.
Yes, you can specify a security policy with AQ queue tables. While dequeuing, use the dequeue condition (
deq_cond) or the correlation ID for the policy to be applied. You can use "1=1" as the dequeue condition. If you do not use a dequeue condition or correlation ID, the dequeue will result in an error.
The Advanced Queuing retention feature can be used to automatically clean up messages after the user-specified duration after consumption.
You can do a dequeue with the subscriber name or by message ID. This consumes the messages, which will be cleaned up after their retention time expires.
To clean up messages for a particular subscriber, you can remove the subscriber and add the subscriber again. Removing the subscriber removes all the messages for that subscriber.
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
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. The behavior you observe will be acceptable in most cases. The solution is to try to isolate the calls to
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. 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:
createDurableSubscriberand 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 JMS operational calls. Hence the error ORA - 4020 will not be raised.
TopicSession.commitcall before calling
createDurableSubscriberand 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
dbms_java.grant_permission ('JAVASYSPRIV', 'SYS:java.net.SocketPermission', '*', 'accept,connect,listen,resolve');
IDAP is Internet Data Access Presentation. An IDAP document encapsulates the AQ operation request and response in XML. IDAP is used to perform AQ operations such as enqueue, dequeue, send notifications, register for notifications, and propagation over the Internet standard transports-HTTP(s) and email. In addition, IDAP encapsulates transactions, security, transformation, and the character set ID for requests.
Internet access functionality for AQ is supported on Apache. This feature is certified to work with Apache, along with the Tomcat or Jserv servlet execution engines. However, the code does not prevent the servlet from working with other Web server and servlet execution engines that support Java Servlet 2.0 or higher interfaces.
When you send IDAP messages via SMTP, each request is a separate transaction. The IDAP request must contain <AQXmlCommit/> as part of the message request to ensure that the operation is committed.
You can create an alias to an AQ agent in Oracle Internet Directory (OID). You can use these AQ agent aliases in the IDAP document sent over the Internet to perform AQ operations. Using aliases prevents exposing the internal name of the AQ agent.
Yes, you can use your own authentication framework for authentication. HTTP
POST requests to the AQ Servlet for AQ operations must be authenticated by the Web server. For example, in Apache, the following can be used to restrict access (using basic authentication) to servlets installed under aqserv/servlet. In this example, all users sending
POST requests to the servlet are authenticated using the users file in
<Location /aqserv/servlet>AuthName "Restrict AQ Servlet Access" AuthType Basic AuthUserFile /apache/htdocs/userdb/users require valid-user</Location>
Yes, IDAP is transport independent. The IDAP document is sent over HTTP(s) or email. You can use IDAP to perform AQ operations over any transport. The IDAP client request can be sent over any transport to the AQ Servlet. The AQ Servlet parses the IDAP document and performs the AQ operation. The response for the AQ operation is also in IDAP. It can be sent to the client over your transport. In fact, you can implement AQ propagation over your transport.
All types of events--system events, user events, and notifications on queues--can be registered with OID. System events are database startup, database shutdown, and system error events. User events include user log on and user log off, DDL statements (create, drop, alter), and DML statement triggers. Notifications on queues include OCI notifications, PL/SQL notifications, and email notifications.
You can create aliases for an AQ agent in OID. These aliases can be specified while performing AQ operations-enqueue, dequeue, and notifications. This is specifically useful while performing AQ operations over the Internet when you do not want to expose an internal agent name. An alias can be used in an AQ operation (IDAP request).
Enqueue and dequeue of the message will raise the error to the application. If the error occurs during the dequeue operation, the retry count of the message is incremented. If the retry count exceeds
max_retries, the message is moved to the exception queue. If the error occurs during propagation, it is handled in a manner similar to dequeue; propagation of the message will fail. It will be attempted again and the message will be moved to the exception queue when retry count exceeds
max_retries for the queue.
Transformation of XML data can be done in one of the following ways:
XMLTypeto return an object of
XMLTypeafter applying the supplied
XMLTypeobject by applying an XSLT transformation to it, using the package
Performance is not affected by the number of queues in a table.
Yes, if it is optimized, propagation happens in batches.
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.
Creating an index on the queue table is useful in the following scenarios:
corr_idof the underlying queue table
SELECTon the underlying queue table. An index on
<QueueTableName>can be created to expedite the performance this
We do not have a specific performance evaluation of JMS versus the PL/SQL API. In general, the PL/SQL API is slightly better than the JMS API. The performance of the JMS and PL/SQL APIs in version 8.1.7 and higher should be comparable.
See Chapter 17 for a full discussion. The following summarizes the steps required to set up Internet access for AQ queues:
oracle.AQ.xml.AQxmlServletclass. If you are using a servlet execution engine that supports the Java Servlet 2.0 specification (like Apache Jserv), you must create a servlet that extends the
oracle.AQ.xml.AQxmlServlet20class. Implement the
init()method in the servlet to specify database connection parameters.
POSTrequests to the AQ Servlet. Only authenticated users are allowed to access the AQ Servlet.
DBMS_AQADM.CREATE_AQ_AGENT.Map the AQ agent to the database users using
Here are the steps for setting up your database for email notifications:
See Chapter 17 for a full discussion. Currently, these operations are supported by Oracle Email Server 5.5 and higher. In summary, follow the steps for setting up Internet access for AQ. In addition, do the following:
ORACLE_SMTP_AGENT.This user will be used to access the AQ Servlet. Also specify
init()method of the AQ Servlet.
$ORACLE_HOME/admin/emailrule.sqlto create an AQ schema on the email server database.
See Chapter 17 for a full discussion. In summary, follow the steps for setting up Internet access for AQ. The destination databases need to be set up for Internet access, as follows:
webdest.oracle.comand listening for requests on port 8081, then the connect string of the database is
(DESCRIPTION=(ADDRESS=(PROTOCOL=http)(HOST=webdest.oracle.com)(PORT=8081))If SSL is used, specify https as the protocol in the connect string. The database link is created as follows:
create public database link propdb connect to john identified by welcome using '(DESCRIPTION=(ADDRESS=(PROTOCOL=http)(HOST=webdest.oracle.com)(PORT=8081))';where user
Welcomeis used to authenticate with the Web server, and is also known by the term AQ HTTP agent.
execute dbms_aqadm.set_aq_propagationwallet('/home/myuid/cwallet.sso', 'welcome');
oracle.AQ.xml.AQxmlServlet20(if you are using a Servlet 2.0 execution engine like Apache Jserv) or extends
oracle.AQ.xml.AQxmlServlet(if you are using a Servlet 2.2 execution engine like Tomcat). This servlet must connect to the destination database. The servlet must be deployed on the Web server in the path