87 DBMS_MGWADM

Using DBMS_MGWADM

• Deprecated Subprograms

• Constants

---

Deprecated Subprograms

Oracle recommends that you do not use deprecated procedures in new applications. Support for deprecated features is for backward compatibility only.

The following subprograms are deprecated with Oracle Database 11g Release 1having been superseded by improved technology:

• ADD_SUBSCRIBER Procedure - use instead CREATE_JOB Procedure

• ALTER_PROPAGATION_SCHEDULE Procedure - use instead ALTER_JOB Procedure

• ALTER_SUBSCRIBER Procedure - use instead ALTER_JOB Procedure

• DB_CONNECT_INFO Procedure - use instead ALTER_AGENT Procedures

• DISABLE_PROPAGATION_SCHEDULE Procedure - use instead DISABLE_JOB Procedure

• ENABLE_PROPAGATION_SCHEDULE Procedure - use instead ENABLE_JOB Procedure

• REMOVE_SUBSCRIBER Procedure - use instead REMOVE_JOB Procedure

• RESET_SUBSCRIBER Procedure - use instead RESET_JOB Procedure

• SCHEDULE_PROPAGATION Procedure - use instead CREATE_JOB Procedure

• UNSCHEDULE_PROPAGATION Procedure - use instead REMOVE_JOB Procedure

---

Constants

• DBMS_MGWADM Constants—Cleanup Actions

• DBMS_MGWADM Constants—Force Values

• DBMS_MGWADM Constants—Logging Levels

• DBMS_MGWADM Constants—Named Property Constants

• DBMS_MGWADM Constants—Other Constants

• DBMS_MGWADM Constants—Propagation Types

• DBMS_MGWADM Constants—Queue Domain Types

• DBMS_MGWADM Constants—Shutdown Modes

• DBMS_MGWADM Constants—WebSphere MQ Interface Types

• DBMS_MGWADM Constants—target_type Argument of SET_OPTION and REMOVE_OPTION Procedures

• DBMS_MGWADM Constants—conntype Argument of CREATE_AGENT and ALTER_AGENT Procedures

Data Structures

The DBMS_MGWADM package defines the following OBJECT types.

Object Types

• SYS.MGW_MQSERIES_PROPERTIES Object Type

• SYS.MGW_PROPERTIES Object Type

• SYS.MGW_PROPERTY Object Type

• SYS.MGW_TIBRV_PROPERTIES Object Type

---

SYS.MGW_MQSERIES_PROPERTIES Object Type

This type specifies basic properties for a WebSphere MQ messaging system link.

Syntax

TYPE SYS.MGW_MQSERIES_PROPERTIES IS OBJECT (
   queue_manager       VARCHAR2(64),
   hostname            VARCHAR2(64),
   port                INTEGER,
   channel             VARCHAR2(64),
   interface_type      INTEGER,
   max_connections     INTEGER,
   username            VARCHAR2(64),
   password            VARCHAR2(64),
   inbound_log_queue   VARCHAR2(64),
   outbound_log_queue  VARCHAR2(64),

-- Methods 
STATIC FUNCTION construct 
RETURN SYS.MGW_MQSERIES_PROPERTIES,

STATIC FUNCTION alter_construct 
RETURN SYS.MGW_MQSERIES_PROPERTIES );

Attributes

Methods

---

SYS.MGW_PROPERTIES Object Type

This type specifies an array of properties.

Syntax

TYPE SYS.MGW_PROPERTIES AS VARRAY (2000) OF SYS.MGW_PROPERTY;

Attributes

Usage Notes

Unless noted otherwise, Messaging Gateway uses named properties as follows:

• Names with the MGWPROP$_ prefix are reserved. They are used for special purposes and are invalid when used as a normal property name.

• A property name can exist only once in a property list; that is, a list can contain only one value for a given name. The name is case-insensitive.

• In general, a property list is order-independent, and the property names may appear in any order. An alter property list is an exception.

• You can use a new property list to alter an existing property list. Each new property modifies the original list in one of the following ways: adds a new property, modifies a property, removes a property, or removes all properties.

The alter list is processed in order, from the first element to the last element. Thus the order in which the elements appear in the alter list is meaningful, especially when the alter list is used to remove properties from an existing list.

The property name and value are used to determine how that element affects the original list. The following rules apply:

• Add or modify property

  MGW_PROPERTY.NAME = property_name
  MGW_PROPERTY.VALUE = property_value
  

  If a property of the given name already exists, then the current value is replaced with the new value; otherwise the new property is added to the end of the list.

• Remove property

  MGW_PROPERTY.NAME = 'MGWPROP$_REMOVE'
  MGW_PROPERTY.VALUE = name_of_property_to_remove
  

  No action is taken if the property name does not exist in the original list.

• Remove all properties

  MGW_PROPERTY.NAME = 'MGWPROP$_REMOVE_ALL'
  MGW_PROPERTY.VALUE = not used
  

---

SYS.MGW_PROPERTY Object Type

This type specifies a named property which is used to specify optional properties for messaging links, foreign queues, and subscribers.

Syntax

TYPE SYS.MGW_PROPERTY IS OBJECT(
   name  VARCHAR2(500),
   value VARCHAR2(4000),

-- Methods  
STATIC FUNCTION construct    --- (1)
RETURN SYS.MGW_PROPERTY,

STATIC FUNCTION construct(   --- (2)
   p_name   IN VARCHAR2,
   p_value  IN VARCHAR2)
RETURN SYS.MGW_PROPERTY );

Attributes

Methods

---

SYS.MGW_TIBRV_PROPERTIES Object Type

A type that specifies basic properties for a TIB/Rendezvous messaging system link. The Messaging Gateway agent creates a TIB/Rendezvous transport of type TibrvRvdTransport for each Messaging Gateway link.

Syntax

TYPE SYS.MGW_TIBRV_PROPERTIES IS OBJECT(
   service   VARCHAR2(128),
   daemon    VARCHAR2(128),
   network   VARCHAR2(256),
   cm_name   VARCHAR2(256),
   cm_ledger VARCHAR2(256),

-- Methods
STATIC FUNCTION construct 
RETURN SYS.MGW_TIBRV_PROPERTIES,

STATIC FUNCTION alter_construct 
RETURN SYS.MGW_TIBRV_PROPERTIES );

Attributes

Methods

Summary of DBMS_MGWADM Subprograms

---

ADD_SUBSCRIBER Procedure

This procedure adds a subscriber used to consume messages from a source queue for propagation to a destination.

Syntax

DBMS_MGWADM.ADD_SUBSCRIBER(
   subscriber_id     IN VARCHAR2,
   propagation_type  IN BINARY_INTEGER,
   queue_name        IN VARCHAR2,
   destination       IN VARCHAR2,
   rule              IN VARCHAR2 DEFAULT NULL,
   transformation    IN VARCHAR2 DEFAULT NULL,
   exception_queue   IN VARCHAR2 DEFAULT NULL
   options           IN SYS.MGW_PROPERTIES DEFAULT NULL);

Parameters

Usage Notes

If the non-Oracle messaging link being accessed for the subscriber uses a JMS interface, then the Messaging Gateway agent will use the Oracle JMS interface to access the Oracle Streams AQ queues. Otherwise the native Oracle Streams AQ interface will be used. Parameters are interpreted differently when the Messaging Gateway agent uses Oracle JMS for JMS connections.

Transformations are not currently supported if the Oracle JMS interface is used for propagation. The transformation parameter must be NULL.

OUTBOUND_PROPAGATION Subscribers

The parameters for a subscriber used for outbound propagation are interpreted as follows:

• queue_name specifies the local Oracle Streams AQ queue that is the propagation source. This must have a syntax of schema.queue.

• destination specifies the foreign queue to which messages are propagated. This must have a syntax of registered_queue@message_link.

• rule specifies an optional Oracle Streams AQ subscriber rule if the native Oracle Streams AQ interface is used, or a JMS selector if the Oracle JMS interface is used. If NULL, then no rule or selector is used.

• transformation specifies the transformation used to convert the Oracle Streams AQ payload to an ADT defined by Messaging Gateway.

  Messaging Gateway propagation dequeues messages from the Oracle Streams AQ queue using the transformation to convert the Oracle Streams AQ payload to a known ADT defined by Messaging Gateway. The message is then enqueued in the foreign messaging system based on the Messaging Gateway ADT.

• exception_queue specifies the name of a local Oracle Streams AQ queue to which messages are moved if an exception occurs. This must have a syntax of schema.queue.

If the native Oracle Streams AQ interface is used, then a subscriber will be added to the Oracle Streams AQ queue when this procedure is called, whether or not Messaging Gateway is running. The local subscriber will be of the form sys.aq$_agent('MGW_subscriber_id', NULL, NULL).

If the Oracle JMS interface is used, then the Messaging Gateway agent will create a JMS durable subscriber with the name of MGW_subscriber_id. If the agent is not running when this procedure is called, then the durable subscriber will be created the next time the agent starts.

The exception queue has the following caveats:

• The user is responsible for creating the Oracle Streams AQ queue to be used as the exception queue.

• The payload type of the source and exception queue must match.

• The exception queue must be created as a queue type of DBMS_AQADM.NORMAL_QUEUE rather than DBMS_AQADM.EXCEPTION_QUEUE. Enqueue restrictions prevent Messaging Gateway propagation from using an Oracle Streams AQ queue of type EXCEPTION_QUEUE as a Messaging Gateway exception queue.

INBOUND_PROPAGATION Subscribers

The parameters for a subscriber used for inbound propagation are interpreted as follows:

• queue_name specifies the foreign queue that is the propagation source. This must have a syntax of registered_queue@message_link.

• destination specifies the local Oracle Streams AQ queue to which messages are propagated. This must have a syntax of schema.queue.

• rule specifies an optional subscriber rule that is valid for the foreign messaging system. This is NULL if no rule is needed.

• transformation specifies the transformation used to convert an ADT defined by Messaging Gateway to the Oracle Streams AQ payload type.

  Messaging Gateway propagation dequeues messages from the foreign messaging system and converts the message body to a known ADT defined by Messaging Gateway. The transformation is used to convert the Messaging Gateway ADT to an Oracle Streams AQ payload type when the message is enqueued to the Oracle Streams AQ queue.

• exception_queue specifies the name of a foreign queue to which messages are moved if an exception occurs. This must have a syntax of registered_queue@message_link.

Whether or not a subscriber is needed depends on the requirements of the non-Oracle messaging system. If a durable subscriber is necessary, then it will be created by the Messaging Gateway agent. If the agent is not running at the time this procedure is called, then the creation of the subscriber on the non-Oracle messaging system will occur when the agent next starts.

The exception queue has the following caveats:

• The exception queue must be a registered non-Oracle queue.

• The source and exception queues must use the same messaging system link.

---

ALTER_AGENT Procedures

This procedure configures Messaging Gateway agent parameters.

Syntax

DBMS_MGWADM.ALTER_AGENT (
   max_connections  IN  BINARY_INTEGER DEFAULT NULL,
   max_memory       IN  BINARY_INTEGER DEFAULT NULL,
   max_threads      IN  BINARY_INTEGER DEFAULT NULL,
   service          IN  VARCHAR2 DEFAULT DBMS_MGWADM.NO_CHANGE );

DBMS_MGWADM.ALTER_AGENT (
   agent_name      IN   VARCHAR2,
   username        IN   VARCHAR2 DEFAULT DBMS_MGWADM.NO_CHANGE,
   password        IN   VARCHAR2 DEFAULT DBMS_MGWADM.NO_CHANGE,
   database        IN   VARCHAR2 DEFAULT DBMS_MGWADM.NO_CHANGE,
   conntype        IN   VARCHAR2 DEFAULT DBMS_MGWADM.NO_CHANGE,
   max_memory      IN   PLS_INTEGER DEFAULT NULL,
   max_threads     IN   PLS_INTEGER DEFAULT NULL,
   service         IN   VARCHAR2 DEFAULT DBMS_MGWADM.NO_CHANGE,
   initfile        IN   VARCHAR2 DEFAULT DBMS_MGWADM.NO_CHANGE,
   comment         IN   VARCHAR2  DEFAULT DBMS_MGWADM.NO_CHANGE );

Parameters

Usage Notes

• Default values for these configuration parameters are set when the Messaging Gateway agent is installed.

• Changes to the max_memory and max_threads parameters take effect the next time the Messaging Gateway agent is active. If the Messaging Gateway agent is currently active, then it must be shut down and restarted for the changes to take effect.

• The service parameter is used to set an Oracle Scheduler job class attribute. The job class is used to create a Scheduler job that starts the Messaging Gateway agent. An Oracle administrator must create the database service. If the value is NULL, the job class will belong to an internal service that is mapped to all instances.

• The max_connections parameter is being deprecated as of the Oracle RDBMS 11g release. The number of messaging connections used by the Messaging Gateway Agent is based on the value of the max_threads parameter.

• The username, password, and database parameters specify connection information used by the Messaging Gateway agent for connections to the Oracle Database. An Oracle administrator should create the user and grant it the role MGW_AGENT_ROLE.

---

ALTER_JOB Procedure

This procedure alters the properties of a propagation job.

Syntax

DBMS_MGWADM.ALTER_JOB (
   job_name          IN   VARCHAR2,
   rule              IN   VARCHAR2 DEFAULT DBMS_MGWADM.NO_CHANGE,
   transformation    IN   VARCHAR2 DEFAULT DBMS_MGWADM.NO_CHANGE,
   exception_queue   IN   VARCHAR2 DEFAULT DBMS_MGWADM.NO_CHANGE,
   poll_interval     IN   PLS_INTEGER DEFAULT 0,
   options           IN   SYS.MGW_PROPERTIES DEFAULT NULL,
   comments          IN   VARCHAR2 DEFAULT DBMS_MGWADM.NO_CHANGE );

Parameters

Usage Notes

• If the non-Oracle messaging link being accessed for the propagation job uses a JMS interface, then the Messaging Gateway agent will use the Oracle JMS interface to access the Oracle Streams AQ queues. Otherwise the native Oracle Streams AQ interface will be used. Parameters are interpreted differently when the Messaging Gateway agent uses Oracle JMS for JMS connections.

• The subscriber rule cannot be altered when propagating from a JMS source. Instead, the propagation job must be dropped and re-created with the new rule. For JMS, changing the message selector on a durable subscription is equivalent to deleting and re-creating the subscription.

• Transformations are not currently supported if the Oracle JMS interface is used for propagation. The transformation parameter must be DBMS_MGWADM.NO_CHANGE (the default value).

• The options parameter specifies a set of properties used to alter the current optional properties. Each property affects the current property list in a particular manner; add a new property, replace an existing property, remove an existing property or remove all properties.

---

ALTER_MSGSYSTEM_LINK Procedure for TIB/Rendezvous

Alters the properties of a TIB/Rendezvous messaging system link.

Syntax

DBMS_MGWADM.ALTER_MSGSYSTEM_LINK ( 
   linkname      IN  VARCHAR2,
   properties    IN  SYS.MGW_TIBRV_PROPERTIES,
   options       IN  SYS.MGW_PROPERTIES DEFAULT NULL,
   comment       IN  VARCHAR2 DEFAULT DBMS_MGWADM.NO_CHANGE );

Parameters

Usage Notes

To retain an existing value for a messaging link property with a VARCHAR2 data type, specify DBMS_MGWADM.NO_CHANGE for that particular property. To preserve an existing value for a property of another data type, specify NULL for that property.

The options parameter specifies a set of properties used to alter the current optional properties. Each property affects the current property list in a particular manner: add a new property, replace an existing property, remove an existing property, or remove all properties.

Some properties cannot be modified, and this procedure will fail if an attempt is made to alter such a property. For properties and options that can be changed, a few are dynamic, and Messaging Gateway uses the new values immediately. Others require the Messaging Gateway agent to be shut down and restarted before they take effect.

---

ALTER_MSGSYSTEM_LINK Procedure for WebSphere MQ

This procedure alters the properties of a WebSphere MQ messaging system link.

Syntax

DBMS_MGWADM.ALTER_MSGSYSTEM_LINK (
   linkname   IN  VARCHAR2,
   properties IN  SYS.MGW_MQSERIES_PROPERTIES,
   options    IN  SYS.MGW_PROPERTIES DEFAULT NULL,
   comment    IN  VARCHAR2 DEFAULT DBMS_MGWADM.NO_CHANGE);

Parameters

Usage Notes

To retain an existing value for a messaging link property with a VARCHAR2 data type, specify DBMS_MGWADM.NO_CHANGE for that particular property. To preserve an existing value for a property of another data type, specify NULL for that property.

The options parameter specifies a set of properties used to alter the current optional properties. Each property affects the current property list in a particular manner: add a new property, replace an existing property, remove an existing property, or remove all properties.

Some properties cannot be modified, and this procedure will fail if an attempt is made to alter such a property. For properties and options that can be changed, a few are dynamic, and Messaging Gateway uses the new values immediately. Others require the Messaging Gateway agent to be shut down and restarted before they take effect.

---

ALTER_PROPAGATION_SCHEDULE Procedure

This procedure alters a propagation schedule.

Syntax

DBMS_MGWADM.ALTER_PROPAGATION_SCHEDULE (
   schedule_id  IN VARCHAR2,
   duration     IN NUMBER DEFAULT NULL,
   next_time    IN VARCHAR2 DEFAULT NULL,
   latency      IN NUMBER DEFAULT NULL);

Parameters

Usage Notes

This procedure always overwrites the existing value for each parameter. If a given parameter is not specified, then the existing values are overwritten with the default value.

---

ALTER_SUBSCRIBER Procedure

This procedure alters the parameters of a subscriber used to consume messages from a source queue for propagation to a destination.

Syntax

DBMS_MGWADM.ALTER_SUBSCRIBER (
   subscriber_id    IN VARCHAR2,
   rule             IN VARCHAR2 DEFAULT DBMS_MGWADM.NO_CHANGE,
   transformation   IN VARCHAR2 DEFAULT DBMS_MGWADM.NO_CHANGE,
   exception_queue  IN VARCHAR2 DEFAULT DBMS_MGWADM.NO_CHANGE,
   options          IN SYS.MGW_PROPERTIES DEFAULT NULL );

Parameters

Usage Notes

If the non-Oracle messaging link being accessed for the subscriber uses a JMS interface, then the Messaging Gateway agent will use the Oracle JMS interface to access the Oracle Streams AQ queues. Otherwise the native Oracle Streams AQ interface will be used. Parameters are interpreted differently when the Messaging Gateway agent uses Oracle JMS for JMS connections.

When propagating from a JMS source, the subscriber rule cannot be altered. Instead, the subscriber must be removed and added with the new rule. For JMS, changing the message selector on a durable subscription is equivalent to deleting and re-creating the subscription.

Transformations are not currently supported if the Oracle JMS interface is used for propagation. The transformation parameter must be DBMS_MGWADM.NO_CHANGE (the default value).

The options parameter specifies a set of properties used to alter the current optional properties. Each property affects the current property list in a particular manner: add a new property, replace an existing property, remove an existing property, or remove all properties.

---

CLEANUP_GATEWAY Procedures

This procedure cleans up Messaging Gateway. The procedure performs cleanup or recovery actions that may be needed when Messaging Gateway is left in some abnormal or unexpected condition. The MGW_GATEWAY view lists Messaging Gateway status and configuration information that pertains to the cleanup actions.

Syntax

DBMS_MGWADM.CLEANUP_GATEWAY(
   action       IN   BINARY_INTEGER,
   sarg         IN   VARCHAR2 DEFAULT NULL);

DBMS_MGWADM.CLEANUP_GATEWAY(
   agent_name   IN   VARCHAR2,   action       IN   BINARY_INTEGER,   sarg         IN   VARCHAR2 DEFAULT NULL );

Parameters

Usage Notes

CLEAN_STARTUP_STATE

sarg is not used and must be NULL.

The CLEAN_STARTUP_STATE action recovers Messaging Gateway to a known state when the Messaging Gateway agent has crashed or some other abnormal event occurs, and Messaging Gateway cannot be restarted. This should be done only when the Messaging Gateway agent has been started but appears to have crashed or has been nonresponsive for an extended period of time.

The CLEAN_STARTUP_STATE action may be needed when the MGW_GATEWAY view shows that the AGENT_STATUS value is something other than NOT_STARTED or START_SCHEDULED, and the AGENT_PING value is UNREACHABLE for an extended period of time.

If the AGENT_STATUS value is BROKEN, then the Messaging Gateway agent cannot be started until the problem has been resolved and the CLEAN_STARTUP_STATE action used to reset the agent status. A BROKEN status can indicate that the Messaging Gateway start job detected a Messaging Gateway agent already running. This condition that should never occur under normal use.

Cleanup tasks include:

• Removing the Scheduler job used to start the external Messaging Gateway agent process.

• Setting certain configuration information to a known state. For example, setting the agent status to NOT_STARTED.

Execution of this command fails if:

• The agent status is NOT_STARTED or START_SCHEDULED.

• No shutdown attempt has been made prior to calling this procedure, except if the agent status is STARTING.

• The Messaging Gateway agent is successfully contacted.

  The assumption is that the agent is active, and this procedure fails. If the agent does not respond after several attempts have been made, then the cleanup tasks are performed. This procedure takes at least several seconds and possibly up to one minute. This is expected behavior under conditions where this particular cleanup action is appropriate and necessary.

CLEAN_LOG_QUEUES

sarg is not used and must be NULL.

The Messaging Gateway agent will clean log queues for all configured messaging system links. The agent will temporarily stop all propagation activity and then remove all obsolete and bad log records from the log queues for all links. The procedure will fail if the Messaging Gateway agent is not running.

This cleanup action is automatically performed each time the Messaging Gateway agent is started.

RESET_SUB_MISSING_LOG_REC

sarg specifies a Messaging Gateway job name (or subscriber ID) to be reset. It must not be NULL.

The Messaging Gateway agent recovers a Messaging Gateway propagation job that has failed due to a missing log record. The agent will reset the source and destination log records. The procedure will fail if the Messaging Gateway agent is not running.

RESET_SUB_MISSING_MESSAGE

sarg specifies a Messaging Gateway job name (or subscriber ID) to be reset. It must not be NULL.

The Messaging Gateway agent recovers a Messaging Gateway propagation job that has failed due to a missing persistent source message. The agent will treat the message as a non-persistent message and continue processing that propagation job. The procedure will fail if the Messaging Gateway agent is not running.

---

CREATE_AGENT Procedure

This procedure creates a Messaging Gateway agent that will be used to process propagation jobs.

Syntax

DBMS_MGWADM.CREATE_AGENT (
   agent_name     IN   VARCHAR2,
   username       IN   VARCHAR2 DEFAULT NULL,
   password       IN   VARCHAR2 DEFAULT NULL,
   database       IN   VARCHAR2 DEFAULT NULL,
   conntype       IN   VARCHAR2 DEFAULT DBMS_MGWADM.JDBC_OCI,
   max_memory     IN   PLS_INTEGER DEFAULT 64,
   max_threads    IN   PLS_INTEGER DEFAULT 1,
   service        IN   VARCHAR2 DEFAULT NULL,
   initfile       IN   VARCHAR2 DEFAULT NULL,
   comment        IN   VARCHAR2  DEFAULT NULL );

Parameters

Usage Notes

• The Messaging Gateway automatically configures a default agent when Messaging Gateway is installed. The name of the default agent is DEFAULT_AGENT. This procedure can be used to create additional agents.

• The username, password, and database parameters specify connection information used by the Messaging Gateway agent for connections to the Oracle Database. An Oracle administrator should create the database user and grant it the role MGW_AGENT_ROLE. It is not mandatory that the connection information be specified when this procedure is called but it must be set before the agent can be started.

• The service parameter is used to create an Oracle Scheduler job class. The job class is used to create a Scheduler job that starts the Messaging Gateway agent. An Oracle administrator must create the database service. If the value is NULL, the job class will belong to an internal service that is mapped to all instances.

---

CREATE_JOB Procedure

This procedure creates a job used to propagate message from a source to a destination.

Syntax

DBMS_MGWADM.CREATE_JOB (
   job_name           IN   VARCHAR2,
   propagation_type   IN   PLS_INTEGER,
   source             IN   VARCHAR2,
   destination        IN   VARCHAR2,
   rule               IN   VARCHAR2 DEFAULT NULL,
   transformation     IN   VARCHAR2 DEFAULT NULL,
   exception_queue    IN   VARCHAR2 DEFAULT NULL,
   poll_interval      IN   PLS_INTEGER DEFAULT NULL,
   options            IN   SYS.MGW_PROPERTIES DEFAULT NULL,
   enabled            IN   BOOLEAN DEFAULT TRUE,
   comments           IN   VARCHAR2 DEFAULT NULL);

Parameters

Usage Notes

• The job must be enabled and Messaging Gateway agent started in order for messages to be propagated.

• If the non-Oracle messaging link being accessed for the propagation job uses a JMS interface, then the Messaging Gateway agent will use the Oracle JMS interface to access the Oracle Streams AQ queues. Otherwise the native Oracle Streams AQ interface will be used. Parameters are interpreted differently when the Messaging Gateway agent uses Oracle JMS for JMS connections.

• Transformations are not currently supported if the Oracle JMS interface is used for propagation. The transformation parameter must be NULL.

OUTBOUND_PROPAGATION Jobs

The parameters for an outbound propagation job are interpreted as follows:

• source specifies the local Oracle Streams AQ queue that is the propagation source. This must have syntax of schema.queue. This can be either a multiple consumer queue or a single consumer queue.

• destination specifies the non-Oracle queue to which messages are propagated. This must have syntax of registered_queue@message_link.

• rule specifies an optional Oracle Streams AQ subscriber rule if the native Oracle Stream AQ interface is used, or a JMS selector if the Oracle JMS interface is used. If NULL, then no rule or selector is used. This parameter must be NULL if the native Oracle Stream AQ interface is used and the propagation source is a single consumer queue.

• transformation specifies the transformation used to convert the Oracle Streams AQ payload to an ADT defined by Messaging Gateway. The full transformation name (schema.name) should be used if one is specified.

  Messaging Gateway propagation dequeues messages from the Oracle Streams AQ queue using the transformation to convert the Oracle Streams AQ payload to a known ADT defined by Messaging Gateway. The message is then enqueued in the non-Oracle messaging system based on the Messaging Gateway ADT.

• exception_queue specifies the name of a local Oracle Streams AQ queue to which messages are moved if an exception occurs. The syntax must be schema.queue.

  If the native Oracle Streams AQ interface is used and the source is a multiple consumer queue, then a subscriber will be added to the Oracle Streams AQ queue when this procedure is called, whether or not the Messaging Gateway agent is running. The local subscriber will be of the form sys.aq$_agent('MGW_job_name', NULL, NULL).

  If the Oracle JMS interface is used, then the Messaging Gateway agent will create a JMS durable subscriber with the name of MGW_job_name. If the agent is not running when this procedure is called, then the durable subscriber will be created the next time the agent starts.

  The exception queue has the following conditions:

  • The user is responsible for creating the Oracle Streams AQ queue to be used as the exception queue.

  • The payload type of the source queue and exception queue must match.

  • The exception queue must be created as a queue type of DBMS_AQADM.NORMAL_QUEUE. Enqueue restrictions prevent Messaging Gateway from using an Oracle Streams AQ queue of type DBMS_AQADM.EXCEPTION_QUEUE as a Messaging Gateway exception queue.

INBOUND_PROPAGATION Jobs

The parameters for an inbound propagation job are interpreted as follows:

• source specifies the non-Oracle queue that is the propagation source. The syntax must be registered_queue@message_link.

• destination specifies the local Oracle Streams AQ queue to which messages are propagated. The syntax must be schema.queue.

• rule specifies an optional subscriber rule that is valid for the non-Oracle messaging system. This should be NULL if no rule is needed.

• transformation specifies the transformation used to convert an ADT defined by Messaging Gateway to the Oracle Streams AQ payload type. The full transformation name (schema.name) should be used if one is specified

  Messaging Gateway propagation dequeues messages from the non-Oracle messaging system and converts the message body to a known ADT defined by Messaging Gateway. The transformation is used to convert the Messaging Gateway ADT to an Oracle Streams AQ payload type when the message is enqueued to the Oracle Streams AQ queue.

• exception_queue specifies the name of a registered non-Oracle queue to which messages are moved if an exception occurs. The syntax must be registered_queue@message_link.

  Whether or not a subscriber is needed for the source queue depends on the requirements of the non-Oracle messaging system. If a durable subscriber is necessary, then the Messaging Gateway agent will create it. If the agent is not running when this procedure is called, then the subscriber will be created on the non-Oracle messaging system the next time the agent starts.

  The exception queue has the following conditions:

  • The exception queue must be a registered non-Oracle queue.

  • The source queue and exception queue must use the same messaging system link.

---

CREATE_MSGSYSTEM_LINK Procedures for TIB/Rendezvous

Creates a link to a TIB/Rendezvous messaging system.

Syntax

DBMS_MGWADM.CREATE_MSGSYSTEM_LINK ( 
   linkname     IN  VARCHAR2,
   properties   IN  SYS.MGW_TIBRV_PROPERTIES,
   options      IN  SYS.MGW_PROPERTIES DEFAULT NULL,
   comment      IN  VARCHAR2 DEFAULT NULL );

DBMS_MGWADM.CREATE_MSGSYSTEM_LINK ( 
   linkname     IN  VARCHAR2,
   agent_name   IN  VARCHAR2,
   properties   IN  SYS.MGW_TIBRV_PROPERTIES,
   options      IN  SYS.MGW_PROPERTIES DEFAULT NULL,
   comment      IN  VARCHAR2 DEFAULT NULL );

Parameters

Usage Notes

The Messaging Gateway default agent will process the propagation jobs associated with this link if an agent name is not specified.

---

CREATE_MSGSYSTEM_LINK Procedures for WebSphere MQ

This procedure creates a messaging system link to a WebSphere MQ messaging system.

Syntax

DBMS_MGWADM.CREATE_MSGSYSTEM_LINK(
   linkname    IN VARCHAR2,
   properties  IN SYS.MGW_MQSERIES_PROPERTIES,
   options     IN SYS.MGW_PROPERTIES DEFAULT NULL,
   comment     IN VARCHAR2 DEFAULT NULL);

DBMS_MGWADM.CREATE_MSGSYSTEM_LINK(
   linkname    IN VARCHAR2,
   agent_name  IN  VARCHAR2,
   properties  IN SYS.MGW_MQSERIES_PROPERTIES,
   options     IN SYS.MGW_PROPERTIES DEFAULT NULL,
   comment     IN VARCHAR2 DEFAULT NULL);

Parameters

Usage Notes

The Messaging Gateway default agent will process the propagation jobs associated with this link if an agent name is not specified.

---

DB_CONNECT_INFO Procedure

This procedure configures connection information used by the Messaging Gateway default agent for connections to Oracle Database.

Syntax

DBMS_MGWADM.DB_CONNECT_INFO (
   username      IN VARCHAR2,
   password      IN VARCHAR2,
   database      IN VARCHAR2 DEFAULT NULL);

Parameters

Usage Notes

The Messaging Gateway agent connects to Oracle Database as the user configured by this procedure. An Oracle administrator should create the user, grant it the role MGW_AGENT_ROLE, and then call this procedure to configure Messaging Gateway. Role MGW_AGENT_ROLE is used to grant this user special privileges needed to access Messaging Gateway configuration information stored in the database, enqueue or dequeue messages to and from Oracle Streams AQ queues, and perform certain Oracle Streams AQ administration tasks.

---

DISABLE_JOB Procedure

This procedure disables a propagation job.

Syntax

DBMS_MGWADM.DISABLE_JOB (
   job_name  IN VARCHAR2);

Parameters

---

DISABLE_PROPAGATION_SCHEDULE Procedure

This procedure disables a propagation schedule.

Syntax

DBMS_MGWADM.DISABLE_PROPAGATION_SCHEDULE (
   schedule_id  IN VARCHAR2);

Parameters

---

ENABLE_JOB Procedure

This procedure enables a propagation job.

Syntax

DBMS_MGWADM.ENABLE_JOB (
   job_name  IN VARCHAR2 );

Parameters

---

ENABLE_PROPAGATION_SCHEDULE Procedure

This procedure enables a propagation schedule.

Syntax

DBMS_MGWADM.ENABLE_PROPAGATION_SCHEDULE (
   schedule_id  IN VARCHAR2 );

Parameters

---

REGISTER_FOREIGN_QUEUE Procedure

This procedure registers a non-Oracle queue entity in Messaging Gateway.

Syntax

DBMS_MGWADM.REGISTER_FOREIGN_QUEUE(
   name            IN VARCHAR2,
   linkname        IN VARCHAR2,
   provider_queue  IN VARCHAR2 DEFAULT NULL,
   domain          IN INTEGER DEFAULT NULL,
   options         IN SYS.MGW_PROPERTIES DEFAULT NULL,
   comment         IN VARCHAR2 DEFAULT NULL);

Parameters

Usage Notes

This procedure does not create the physical queue in the non-Oracle messaging system. The non-Oracle queue must be created using the administration tools for that messaging system.

---

REMOVE_AGENT Procedure

This procedure removes a Messaging Gateway agent.

Syntax

DBMS_MGWADM.REMOVE_AGENT(
   agent_name   IN   VARCHAR2 );

Parameters

Usage Notes

All messaging system links associated with this Messaging Gateway agent must be removed and the agent must be stopped before it can be removed. The Messaging Gateway default agent cannot be removed.

---

REMOVE_JOB Procedure

This procedure removes a propagation job.

Syntax

DBMS_MGWADM.REMOVE_JOB(
   job_name   IN   VARCHAR2,   force      IN   PLS_INTEGER DEFAULT DBMS_MGWADM.NO_FORCE);

Parameters

Usage Notes

• The Messaging Gateway agent uses various resources of the Oracle Database and the non-Oracle messaging system for its propagation work. These resources need to be released when the job is removed. For example, Messaging Gateway may create a durable subscriber on the source queue that should be removed when the job is removed. Therefore, this procedure should normally be called when the Messaging Gateway agent is running and able to access the non-Oracle messaging system associated with this job.

• For outbound propagation, a local subscriber is removed from the Oracle Streams AQ queue when the propagation source is a multiple consumer queue.

---

REMOVE_MSGSYSTEM_LINK Procedure

This procedure removes a messaging system link for a non-Oracle messaging system.

Syntax

DBMS_MGWADM.REMOVE_MSGSYSTEM_LINK(
   linkname  IN VARCHAR2);

Parameters

Usage Notes

All registered queues associated with this link must be removed before the messaging system link can be removed. This procedure fails if there is a registered foreign (non-Oracle) queue that references this link.

---

REMOVE_OPTION Procedure

This procedure removes a Messaging Gateway configuration option. It can be used to remove an agent option, a messaging link option, or a propagation job option.

Syntax

DBMS_MGWADM.REMOVE_OPTION (
   target_type   IN   PLS_INTEGER,
   target_name   IN   VARCHAR2,
   option_name   IN   VARCHAR2);

Parameters

Usage Notes

DBMS_MGWADM.AGENT_JAVA_PROP Target

The procedure removes an agent option used to set a Java System property when the Messaging Gateway agent is started. The agent must be restarted for the change to take effect.

The parameters are interpreted as follows:

• target_name specifies the name of the Messaging Gateway agent. DBMS_MGWADM.DEFAULT_AGENT can be used for the default agent.

• option_name specifies the Java System property

• encrypted can be either TRUE or FALSE

DBMS_MGWADM.MSGLINK_OPTION Target

The procedure removes a single option for a Messaging Gateway messaging system link. This is equivalent to calling DBMS_MGWADM.ALTER_MSGSYSTEM_LINK and using the options parameter to remove an option.

The parameters are interpreted as follows:

• target_name specifies the name of the message system link

• option_name specifies the option to set

• encrypted must be FALSE

DBMS_MGWADM.JOB_OPTION Target

The procedure removes a single option for a Messaging Gateway propagation job. This is equivalent to calling DBMS_MGWADM.ALTER_JOB and using the options parameter to remove an option.

The parameters are interpreted as follows:

• target_name specifies the name of the propagation job

• option_name specifies the option to set

• encrypted must be FALSE

---

REMOVE_SUBSCRIBER Procedure

This procedure removes a subscriber used to consume messages from a source queue for propagation to a destination.

Syntax

DBMS_MGWADM.REMOVE_SUBSCRIBER (
   subscriber_id  IN VARCHAR2,
   force          IN BINARY_INTEGER DEFAULT DBMS_MGWADM.NO_FORCE );

Parameters

Usage Notes

• The Messaging Gateway agent uses various resources of Oracle Database and the non-Oracle messaging system for its propagation work. These resources are typically associated with each subscriber and need to be released when the subscriber is no longer needed. Therefore, this procedure should only be called when the Messaging Gateway agent is running and able to access the non-Oracle messaging system associated with this subscriber.

• For outbound propagation, a local subscriber is removed from the Oracle Streams AQ queue.

---

RESET_JOB Procedure

This procedure resets the propagation error state for a propagation job.

Syntax

DBMS_MGWADM.RESET_JOB (
   job_name   IN   VARCHAR2);

Parameters

Usage Notes

This procedure can be used to reset a propagation job that has been set to a failed state and propagation activities have been stopped. The administrator should correct the problem and then call this procedure to allow the agent to retry the propagation job. The STATUS field of the MGW_JOBS view indicates the job status.

---

RESET_SUBSCRIBER Procedure

This procedure resets the propagation error state for a subscriber.

Syntax

DBMS_MGWADM.RESET_SUBSCRIBER (
   subscriber_id  IN VARCHAR2 );

Parameters

---

SCHEDULE_PROPAGATION Procedure

This procedure schedules message propagation from a source to a destination. The schedule must be enabled and Messaging Gateway started in order for messages to be propagated.

Syntax

DBMS_MGWADM.SCHEDULE_PROPAGATION (
   schedule_id       IN VARCHAR2,
   propagation_type  IN BINARY_INTEGER,
   source            IN VARCHAR2,
   destination       IN VARCHAR2,
   start_time        IN DATE DEFAULT SYSDATE,
   duration          IN NUMBER DEFAULT NULL,
   next_time         IN VARCHAR2 DEFAULT NULL,
   latency           IN NUMBER DEFAULT NULL);

Parameters

Usage Notes

For outbound propagation, parameters are interpreted as follows:

• source specifies the local Oracle Streams AQ queue from which messages are propagated. This must have a syntax of schema.queue.

• destination specifies the foreign queue to which messages are propagated. This must have a syntax of registered_queue@message_link.

For inbound propagation, parameters are interpreted as follows:

• source specifies the foreign queue from which messages are propagated. This must have a syntax of registered_queue@message_link.

• destination specifies the local Oracle Streams AQ queue to which messages are propagated. This must have a syntax of schema.queue.

The schedule is set to an enabled state when it is created.

---

SET_LOG_LEVEL Procedures

This procedure dynamically alters the Messaging Gateway agent logging level. The Messaging Gateway agent must be running.

Syntax

DBMS_MGWADM.SET_LOG_LEVEL (
   log_level    IN   BINARY_INTEGER);

DBMS_MGWADM.SET_LOG_LEVEL (
   agent_name   IN   VARCHAR2,
   log_level    IN   BINARY_INTEGER);

Parameters

---

SET_OPTION Procedure

This procedure sets a Messaging Gateway configuration option. It can be used to set an agent option, a messaging link option, or a propagation job option.

Syntax

DBMS_MGWADM.SET_OPTION (
   target_type   IN   PLS_INTEGER,
   target_name   IN   VARCHAR2,
   option_name   IN   VARCHAR2,
   option_value  IN   VARCHAR2,
   encrypted     IN   BOOLEAN DEFAULT FALSE );

Parameters

Usage Notes

DBMS_MGWADM.AGENT_JAVA_PROP Target

The procedure will store an agent option used to set a Java System property when the Messaging Gateway agent is started. The agent must be restarted for the change to take effect.

The parameters are interpreted as follows:

• target_name specifies the name of the Messaging Gateway agent. DBMS_MGWADM.DEFAULT_AGENT can be used for the default agent.

• option_name specifies the Java System property

• encrypted can be either TRUE or FALSE

DBMS_MGWADM.MSGLINK_OPTION Target

The procedure will set or alter a single option for a Messaging Gateway messaging system link. This is equivalent to calling DBMS_MGWADM.ALTER_MSGSYSTEM_LINK and using the options parameter to set an option.

The parameters are interpreted as follows:

• target_name specifies the name of the message system link

• option_name specifies the option to set

• encrypted must be FALSE

DBMS_MGWADM.JOB_OPTION Target

The procedure will set or alter a single option for a Messaging Gateway propagation job. This is equivalent to calling DBMS_MGWADM.ALTER_JOB and using the options parameter to set an option.

The parameters are interpreted as follows:

• target_name specifies the name of the propagation job

• option_name specifies the option to set

• encrypted must be FALSE

---

SHUTDOWN Procedures

This procedure shuts down the Messaging Gateway agent. No propagation activity occurs until Messaging Gateway is restarted.

Syntax

DBMS_MGWADM.SHUTDOWN (
   sdmode      IN BINARY_INTEGER DEFAULT DBMS_MGWADM.SHUTDOWN_NORMAL);

DBMS_MGWADM.SHUTDOWN (
   agent_name  IN VARCHAR2);

Parameters

Usage Notes

The Messaging Gateway default agent is shut down if no agent name is specified.

---

STARTUP Procedures

This procedure starts the Messaging Gateway agent. It must be called before any propagation activity can take place.

Syntax

DBMS_MGWADM.STARTUP(
   instance       IN  BINARY_INTEGER DEFAULT 0,
   force          IN  BINARY_INTEGER DEFAULT DBMS_MGWADM.NO_FORCE);

DBMS_MGWADM.STARTUP(
   agent_name     IN  VARCHAR2);

Parameters

Usage Notes

• The Messaging Gateway default agent will be started if an agent name is not specified.

• The force and instance parameters are no longer used and will be ignored. If the instance affinity parameters were being used to start the default agent on a specific instance, the administrator will need to create a database service and then assign that service to the default agent using the DBMS_MGWADM.ALTER_AGENT procedure.

• The Messaging Gateway agent cannot be started until an agent user has been configured by the DBMS_MGWADM.CREATE_AGENT or DBMS_MGWADM.ALTER_AGENT subprograms.

---

UNREGISTER_FOREIGN_QUEUE Procedure

This procedure removes a non-Oracle queue entity in Messaging Gateway.

Syntax

DBMS_MGWADM.UNREGISTER_FOREIGN_QUEUE(
   name         IN VARCHAR2,
   linkname     IN VARCHAR2);

Parameters

Usage Notes

• This procedure does not remove the physical queue in the non-Oracle messaging system.

• All propagation jobs, subscribers and schedules referencing this queue must be removed before it can be unregistered. This procedure fails if a propagation job, subscriber, or propagation schedule references the non-Oracle queue.

---

UNSCHEDULE_PROPAGATION Procedure

This procedure removes a propagation schedule.

Syntax

DBMS_MGWADM.UNSCHEDULE_PROPAGATION (
   schedule_id   IN VARCHAR2 );

Parameters