DMS Tables

The Dynamo Message System uses the following tables to store messaging data:

dms_client

The list of ATG instances that have started an SQL-JMS instance pointing to this database. Clients listed in this table may or may not still be active.

Column	Data Type	Constraint
`client_name`	VARCHAR(250)	NOT NULL
	The unique name the client uses to identify itself. By default this name is a combination of the Dynamo’s DRP IP address and port.
`client_id`	NUMERIC(19)	NULL
	The unique numeric representation of the client used internally by the SQL-JMS system.

dms_queue

The list of queues available for messaging.

Column	Data Type	Constraint
`queue_name`	VARCHAR(250)	NULL
	The unique name of the queue used by clients to send messages to and receive messages from a specific queue.
`queue_id`	NUMERIC(19)	NOT NULL
	The unique numeric representation of the queue used internally by the SQL-JMS system.
`temp_id`	NUMERIC(19)	NULL
	Denotes whether or not the queue is a temporary queue. If the queue is a temporary queue, the column contains the client ID of the client that created the temporary queue. If the queue is not a temporary queue, the column contains the value zero.

dms_queue_recv

The list of queue receivers that are registered with a queue. Each row represents a single receiver listening to a queue.

Column	Data Type	Constraint
`client_id`	NUMERIC(19)	NULL
	The unique numeric representation of the client used internally by the SQL-JMS system.
`receiver_id`	NUMERIC(19)	NOT NULL
	The numeric ID of the receiver listening to the queue.
`queue_id`	NUMERIC(19)	NULL
	The numeric ID of the queue the receiver is listening to.

dms_queue_entry

The list of messages currently in any queue. Each row in this table represents a single message in a queue.

Column	Data Type	Constraint
`queue_id`	NUMERIC(19)	NOT NULL
	The queue ID of the queue this message is currently in.
`msg_id`	NUMERIC(19)	NOT NULL
	The unique numeric representation of the message used internally by the SQL-JMS system.
`delivery_date`	NUMERIC(19)	NULL
	A Java long date value that specifies when the message should be delivered. The value is a date/time in the form of UTC milliseconds from the epoch start (1 January 1970 0:00 UTC). If there is to be no delayed delivery of the message, this column effectively holds a timestamp of when the message was put into the queue allowing it to be delivered as soon as possible.
`handling_client_id`	NUMERIC(19)	NULL
	The client ID of the client that is attempting to handle this message. If no client is attempting to handle this message yet, this column contains the value –1.
`read_state`	NUMERIC(19)	NULL
	The current state of the message. A message that is not currently being handled by a client has a value of zero. A message that is being handled has a non-zero value. Messages that are handled successfully are deleted from this table.

dms_topic

The list of topics available for messaging.

Column	Data Type	Constraint
`topic_name`	VARCHAR(250)	NULL
	The unique name of the topic used by clients to send messages to and receive messages from a specific topic.
`topic_id`	NUMERIC(19)	NOT NULL
	The unique numeric representation of the topic used internally by the SQL-JMS system.
`temp_id`	NUMERIC(19)	NULL
	Denotes whether or not the topic is a temporary topic. If the topic is a temporary topic, the column contains the client ID of the client that created the temporary topic. If the topic is not a temporary topic, the column contains the value zero.

dms_topic_sub

The list of topic subscribers that are currently registered with a topic. Each row represents a single subscriber listening to a topic.

Column	Data Type	Constraint
`client_id`	NUMERIC(19)	NULL
	The client ID of the client that created the subscriber.
`subscriber_name`	VARCHAR(250)	NULL
	The unique name used by the client to identify the subscriber.
`subscriber_id`	NUMERIC(19)	NOT NULL
	The subscriber ID of the subscriber receiving the message.
`topic_id`	NUMERIC(19)	NULL
	The topic ID of the topic the subscriber is registered to listen to.
`durable`	NUMERIC(1)	NULL
	Denotes whether or not the subscriber is durable. Durable subscribers will have a value of 1. Non-durable subscribers will have the value zero.
`active`	NUMERIC(1)	NULL
	Denotes whether or not the subscriber is flagged as active by the client.

dms_topic_entry

The list of messages waiting to be handled by a subscriber listening to a topic. Each row in this table represents a single message for a subscriber.

Column	Data Type	Constraint
`subscriber_id`	NUMERIC(19)	NOT NULL
	The subscriber ID of the subscriber receiving the message.
`msg_id`	numeric(19)	NOT NULL
	The unique identifier of the message used internally by the SQL-JMS system.
`delivery_date`	NUMERIC(19)	NULL
	A Java long date value that specifies when the message should be delivered. The value is a date/time in the form of UTC milliseconds from the epoch start (1 January 1970 0:00 UTC). If there is to be no delayed delivery of the message, this column effectively holds a timestamp of when the message was put into the queue allowing it to be delivered as soon as possible.
`read_state`	NUMERIC(19)	NULL
	The current state of the message. A message that is not currently being handled by a client has a value of zero. A message that is being handled has a non-zero value. Messages that are handled successfully are deleted from this table.

dms_msg

The list of actual messages currently in the SQL-JMS system. Each row represents a single message that may be in a single queue or waiting to be received by multiple topic subscribers.

Column	Data Type	Constraint
`msg_class`	VARCHAR(250)	NULL
	The Java class of the message.
`has_properties`	NUMERIC(1)	NULL
	Whether or not the message has properties beyond the standard JMS header properties, such as implementation or application specific properties.
`reference_count`	NUMERIC(10)	NULL
	The number of topic subscribers still waiting to receive the message.
`msg_id`	NUMERIC(19)	NOT NULL
	The unique identifier of the message used internally by the SQL-JMS system.
`timestamp`	NUMERIC(19)	NULL
	JMS header property: the time the message was handed off to the provider to be sent.
`correlation_id`	VARCHAR(250)	NULL
	JMS header property: the correlation ID. Currently, this property is unsupported and this column will always be null.
`reply_to`	NUMERIC(19)	NULL
	JMS header property: the destination where a reply to the message should be sent. This column uses the topic or queue ID to represent the reply destination. If no reply-to was specified in the JMS message the column has the value zero.
`destination`	NUMERIC(19)	NULL
	JMS header property: the destination to which the message is being sent. This column uses the topic or queue ID to represent the destination.
`delivery_mode`	NUMERIC(1)	NULL
	JMS header property: the delivery mode of the message.
`redelivered`	NUMERIC(1)	NULL
	JMS header property: an indication of whether the message is being redelivered.
`type`	VARCHAR(250)	NULL
	JMS header property: the message type.
`expiration`	NUMERIC(19)	NULL
	JMS header property: the message’s expiration value.
`priority`	NUMERIC(1)	NULL
	JMS header property: the message priority.
`small_body`	VARBINARY(250)	NULL
	The body of the message if the body is within the preset size for small bodies.
`large_body`	LONG VARBINARY	NULL
	The body of the message if the body is larger than the preset size for small bodies.

dms_msg_properties

This table contains the non-standard properties for messages currently in the SQL-JMS system. Each row represents one property for a single message. A single message with multiple non-standard properties will have multiple rows in the table.

Column	Data Type	Constraint
`msg_id`	NUMERIC(19)	NOT NULL
	The message ID of the message with which the property is associated.
`data_type`	NUMERIC(1)	NULL
	The data type of the property represented as a number.
`name`	VARCHAR(250)	NOT NULL
	The name of the property used by the JMS client to identify it within the JMS message.
`value`	VARCHAR(250)	NULL
	The value of the property represented as a String.

dms_limbo

This table identifies the Patch Bay instances that store delayed messages.

Column	Data Type	Constraint
`limbo_name`	VARCHAR(250)	NOT NULL
	The name of the Dynamo server the Patch Bay is on, which comes from `/atg/dynamo/service/ServerName`.
`limbo_id`	NUMERIC(19)	NOT NULL
	The generated ID for internal identification of the Patch Bay instance.

dms_limbo_msg

The main table for delayed messages. Each row corresponds to a single message.

Column	Data Type	Constraint
`msg_id`	NUMERIC(19)	NOT NULL
	A generated ID identifying the message.
`limbo_id`	NUMERIC(19)	NOT NULL
	The generated ID for internal identification of the instance.
`delivery_date`	NUMERIC(19)	NOT NULL
	Specifies when the message should be sent (in system milliseconds).
`delivery_count`	NUMERIC(2)	NOT NULL
	The counter for failures to send a delayed message.
`msg_src_name`	VARCHAR(250)	NOT NULL
	The name of the message source that produced this message.
`port_name`	VARCHAR(250)	NOT NULL
	The output port to which this message is going.
`msg_class`	VARCHAR(250)	NOT NULL
	The actual class string of the message class; i.e. `getClass().getName()`.
`msg_class_type`	NUMERIC(1)	NOT NULL
	The actual class of the message object.
`jms_type`	VARCHAR(250)	NULL
	The JMS header type of the message.
`jms_expiration`	NUMERIC(19)	NULL
	The JMS header expiration of the message.
`jms_correlationid`	VARCHAR(250)	NULL
	The JMS header correlation ID of the message.

dms_limbo_replyto

This table stores the “reply to” headers for delayed messages.

Column	Data Type	Constraint
`msg_id`	NUMERIC(19)	NOT NULL
	A generated ID identifying the message.
`jms_replyto`	VARBINARY(500)	NULL
	The JMS header “reply to” of the message.

dms_limbo_body

This table stores the message bodies for delayed messages.

Column	Data Type	Constraint
`msg_id`	NUMERIC(19)	NOT NULL
	A generated ID identifying the message.
`msg_body`	LONG VARBINARY	NULL
	The body of the specified message type, e.g.; object, stream, etc.

dms_limbo_props

This table stores the message properties for delayed messages.

Column	Data Type	Constraint
`msg_id`	NUMERIC(19)	NOT NULL
	A generated ID identifying the message.
`prop_name`	VARCHAR(250)	NOT NULL
	The message property name.
`prop_value`	VARCHAR(250)	NOT NULL
	The message property value.

dms_limbo_ptypes

A sub-table of properties identifying the property types.

Column	Data Type	Constraint
`msg_id`	NUMERIC(19)	NOT NULL
	A generated ID identifying the message.
`prop_name`	VARCHAR(250)	NOT NULL
	The message property name.
`prop_type`	NUMERIC(1)	NOT NULL
	The property type.

dms_limbo_delay

The table for messages in the redelivery process.

Column	Data Type	Constraint
`msg_id`	NUMERIC(19)	NOT NULL
	A generated ID identifying the message.
`delay`	NUMERIC(19)	NOT NULL
	The delay between attempts to deliver the message (in milliseconds).
`max_attempts`	NUMERIC(2)	NOT NULL
	Maximum number of times to attempt to deliver the message.
`failure_port`	VARCHAR(250)	NOT NULL
	Port through which to send the message to failure destinations if all attempts to deliver the message are unsuccessful.
`jms_timestamp`	NUMERIC(19)	NULL
	JMS attribute used to create the new message for redelivery.
`jms_deliverymode`	NUMERIC(10)	NULL
	JMS attribute used to create the new message for redelivery.
`jms_priority`	NUMERIC(10)	NULL
	JMS attribute used to create the new message for redelivery.
`jms_messageid`	VARCHAR(250)	NULL
	JMS attribute used to create the new message for redelivery.
`jms_redelivered`	NUMERIC(1)	NULL
	JMS attribute used to create the new message for redelivery.
`jms_destination`	VARBINARY(500)	NULL
	JMS attribute used to create the new message for redelivery.

ATG Programming Guide