The requiredQueueNames and requiredTopicNames properties can be used to add new destinations to the system, but they cannot be used to remove destinations. Removing a destination from one of those properties just means that the system does not make sure that the destination exists when it starts up; it does not actually remove the destination from the system.

Oracle ATG Web Commerce includes a browser-based interface that you can use to administer and remove queues and topics. See Using the SQL-JMS Administration Interface below.

Removing a queue or topic involves more than just removing rows from the dms_queue and dms_topic tables. Any messages in those queues or topics also have to be removed, as well as any subscriptions associated with those queues or topics.

The process of removing a queue or topic should preferably be done when the Oracle ATG Web Commerce application is shut down. If you want to perform this task on a running Oracle ATG Web Commerce application, you must first make sure that all message producers and consumers are closed and unsubscribed.

Removing a Queue

You can remove a queue using the SQL-JMS Administration Interface, or by issuing SQL statements. For example, the following SQL statements remove a queue named fooQueue:

DELETE FROM dms_msg_properties
  WHERE msg_id IN (SELECT msg_id
                     FROM dms_queue_entry
                     WHERE queue_id IN (SELECT queue_id
                                          FROM dms_queue
                                          WHERE queue_name = 'fooQueue'))
DELETE FROM dms_msg
  WHERE msg_id IN (SELECT msg_id
                     FROM dms_queue_entry
                     WHERE queue_id IN (SELECT queue_id
                                          FROM dms_queue
                                          WHERE queue_name = 'fooQueue'))
DELETE FROM dms_queue_entry
  WHERE queue_id IN (SELECT queue_id
                       FROM dms_queue
                       WHERE queue_name = 'fooQueue')
DELETE FROM dms_queue
  WHERE queue_name = 'fooQueue'
Removing a Topic

You can remove a topic using the SQL-JMS Administration Interface, or by issuing SQL statements. Before you remove a topic, however, make sure that no message producer is still publishing to the topic, and that all durable subscribers to the topic were deleted. See Removing Durable Subscribers below for more information.

The following SQL statements delete a topic named fooTopic, along with any remaining subscribers to that topic:

DELETE FROM dms_msg_properties
  WHERE msg_id IN (SELECT msg_id
                     FROM dms_topic_entry
                     WHERE subscriber_id IN (SELECT subscriber_id
                                               FROM dms_topic_sub
                                               WHERE topic_id IN (SELECT topic_id
                                                                    FROM dms_topic
                                                                    WHERE
                                                        topic_name = 'fooTopic')))
DELETE FROM dms_msg
  WHERE msg_id IN (SELECT msg_id
                     FROM dms_topic_entry
                     WHERE subscriber_id IN (SELECT subscriber_id
                                               FROM dms_topic_sub
                                               WHERE topic_id IN (SELECT topic_id
                                                                    FROM dms_topic
                                                                    WHERE
                                                        topic_name = 'fooTopic')))
DELETE FROM dms_topic_entry
  WHERE subscriber_id IN (SELECT subscriber_id
                            FROM dms_topic_sub
                            WHERE topic_id IN (SELECT topic_id
                                                 FROM dms_topic
                                                 WHERE topic_name = 'fooTopic'))
DELETE FROM dms_topic_sub
  WHERE topic_id IN (SELECT topic_id
                       FROM dms_topic
                       WHERE topic_name = 'fooTopic')
DELETE FROM dms_topic
  WHERE topic_name = 'fooTopic'
Removing Durable Subscribers

Durable subscriptions hold messages for topic subscribers even when those subscribers are not online. If a message is sent to a destination that has a durable subscriber, the message is stored in the database until that subscriber comes online and reads its message.

However, if a client never comes online to read its messages, perhaps because the application is no longer active or has been changed to use another durable subscription name, those messages build up in the database. If durable subscribers disappear from the system, the appropriate entries in the database should also be removed to prevent messages from building up without bound.

You can remove SQL JMS subscribers using the SQL-JMS Administration Interface, or you can remove them programmatically. There is a standard JMS method for removing durable subscribers, TopicSession.unsubscribe(). This method deletes the state being maintained on behalf of the subscriber by its provider. You should not delete a durable subscription while it has an active TopicSubscriber for it, or while a message received by it is part of a transaction or has not been acknowledged in the session.

The following code removes a durable subscriber:

SqlJmsManager manager = (SqlJmsManager) service;
XATopicConnection xac = manager.createXATopicConnection();
xac.start();
XATopicSession xas = xac.createXATopicSession();
TopicSession ts = xas.getTopicSession();
tx.unsubscribe("fooTopic");
xac.close();

Copyright © 1997, 2013 Oracle and/or its affiliates. All rights reserved. Legal Notices