Messaging Gateway propagation can be monitored using the MGW_JOBS view and the Messaging Gateway log file. The view provides information on propagated messages and errors that may have occurred during propagation attempts. The log file can be used to determine the cause of the errors.

Besides showing configuration information, the MGW_JOBS view also has dynamic information that can be used to monitor message propagation. Applicable fields include STATUS, ENABLED, PROPAGATED_MSGS, EXCEPTIONQ_MSGS, FAILURES, LAST_ERROR_MSG, LAST_ERROR_DATE, and LAST_ERROR_TIME.

The STATUS field indicates current status of the job. READY means that the job is ready for propagation (but only if the ENABLED field is TRUE). RETRY means that a propagation failure occurred but that propagation will be retried. FAILED means that the agent has stopped propagation for the job due to an unrecoverable error or the maximum number of consecutive propagation failures has been reached. DELETE_PENDING means job removal is pending due to DBMS_MGWADM.REMOVE_JOB being called but certain cleanup tasks pertaining to the job are still outstanding. SUBSCRIBER_DELETE_PENDING means that DBMS_MGWADM.REMOVE_SUBSCRIBER has been called on an old style propagation job but certain cleanup tasks pertaining to the job are still outstanding.

The ENABLED field indicates whether the propagation job is currently enabled. TRUE indicates the job is enabled while FALSE indicates the job is disabled. No propagation will occur unless the job is enabled.

The PROPAGATED_MSGS field of the MGW_JOBS view indicates how many messages have been successfully propagated. This field is reset to zero when the Messaging Gateway agent is started.

If a Messaging Gateway propagation job has been configured with an exception queue, then the Messaging Gateway agent will move messages to that exception queue the first time the Messaging Gateway agent encounters a propagation failure caused by a message conversion failure. A message conversion failure is indicated by oracle.mgw.common.MessageException in the Messaging Gateway log file. The EXCEPTIONQ_MSGS field indicates how many messages have been moved to the exception queue. This field is reset to zero when the Messaging Gateway agent is started.

If an error occurs during message propagation for a propagation job, a count is incremented in the FAILURES field. This field indicates the number of failures encountered since the last successful propagation of messages. Each time a failure occurs, an error message and the time it occurred will be shown by LAST_ERROR_MSG, LAST_ERROR_DATE, and LAST_ERROR_TIME. When the number of failures reaches sixteen, Messaging Gateway halts propagation attempts for this propagation job. To resume propagation attempts you must call DBMS_MGWADM.RESET_JOB for the propagation job.

If an error occurs, then examine the Messaging Gateway log file for further information.

[221] Failed to access <messaging_system> queue: <queue>

An error occurred while trying to access either an Oracle Database Advanced Queuing queue or a non-Oracle queue. Check the linked exception error code and message in the log file.

[241] Failed to connect to database. SQL error: <error>, connect string: <connect_string>

This is probably caused by incorrect MGW agent connection information specified for DBMS_MGWADM.CREATE_AGENT or DBMS_MGWADM.ALTER_AGENT. Either the Messaging Gateway agent user or password is incorrect or the database specifier (database parameter) is incorrect. Verify that the connection information is correct for the connection type used by the agent, JDBC OCI or JDBC Thin.

If the database parameter is NULL, then check the Messaging Gateway log file for the following Oracle linked errors:

ORA-01034: ORACLE not available
ORA-27101: shared memory realm does not exist

These two errors together indicate that the Messaging Gateway agent is attempting to connect to the database using a local IPC connection, but the ORACLE_SID value is not correct.

A local connection is used when the database parameter is set to NULL. If a local connection is desired, the correct ORACLE_SID value must be set in the Messaging Gateway agent process. This can be done by adding the following line to the MGW initialization file, usually mgw.ora:

set ORACLE_SID = sid_value

ORACLE_SID need not be set in the MGW initialization file if the database parameter is not NULL.

If setting ORACLE_SID in the MGW initialization file does not work, then the database parameter must be set to a value that is not NULL.

If the JDBC Thin connection is used, then the database parameter must be not NULL. If the JDBC Thin connection is used and the database parameter is a TNSNames alias, make sure that the oracle.net.tns_names Java property is set in the MGW initialization file. The property can be set by adding the following line to the MGW initialization file:

setJavaProp oracle.net.tns_admin=<directory containing tnsnames.ora>

[415] Missing messages from source queue of job <job_name>

Possible causes include:

• The agent partially processed persistent messages that were dequeued by someone other than the Messaging Gateway agent.

• The propagation source queue was purged or re-created.

• A message was moved to the Oracle Database Advanced Queuing exception queue.

If this error occurs, then call procedure CLEANUP_GATEWAY in the DBMS_MGWADM package:

DBMS_MGWADM.CLEANUP_GATEWAY (
       action => DBMS_MGWADM.RESET_SUB_MISSING_MESSAGE, 
       sarg => <job_name>);

The call takes effect only if the propagation job has encountered the missing message problem and the agent is running. The agent treats the missing messages as nonpersistent messages and continues processing the propagation job.

[416] Missing log records in receiving log queue for job <job_name>

Possible causes include:

• Log records were dequeued from the log queues by someone other than the Messaging Gateway agent.

• The log queues were purged or re-created.

If this error occurs, then call procedure CLEANUP_GATEWAY in the DBMS_MGWADM package:

DBMS_MGWADM.CLEANUP_GATEWAY (
       action => DBMS_MGWADM.RESET_SUB_MISSING_LOG_REC,  
       sarg => <job_name>);

The call takes effect only if the propagation job has encountered the missing log records problem and the agent is running.

[417] Missing log records in sending log queue for job <job_name>

See previous error.

[421] WARNING: Unable to get connections to recover job <job_name>

This message is a warning message indicating that the Messaging Gateway agent failed to get a connection to recover the propagation job, because other propagation jobs are using them all. The agent will keep trying to get a connection until it succeeds.

If this message is repeated many times for a WebSphere MQ link, then increase the maximum number of connections used by the Messaging Gateway link associated with the propagation job.

[434] Failed to access queue <queue>; provider queue <queue>

This message indicates that a messaging system native queue cannot be accessed. The queue may have been registered by DBMS_MGWADM.REGISTER_FOREIGN_QUEUE, or it may be an Oracle Database Advanced Queuing queue. The linked exceptions should give more information.

Possible causes include:

• The foreign queue was registered incorrectly, or the Messaging Gateway link was configured incorrectly.

  Verify configuration information. If possible, use the same configuration information to run a sample application of the non-Oracle messaging system.

• The non-Oracle messaging system is not accessible.

  Check that the non-Oracle messaging system is running and can be accessed using the information supplied in the Messaging Gateway link.

• The Oracle Database Advanced Queuing queue does not exist. Perhaps the queue was removed after the Messaging Gateway propagation job was created.

  Check that the Oracle Database Advanced Queuing queue still exists.

[436] LOW MEMORY WARNING: total memory = < >, free_mem = < >

The Messaging Gateway agent JVM is running low on memory. Java garbage collection will be invoked, but this may represent a JVM heap size that is too small. Use the max_memory parameter of DBMS_MGWADM.ALTER_AGENT to increase the JVM heap size. If the Messaging Gateway agent is running, then it must be restarted for this change to take effect.

[703] Failed to retrieve information for transformation <transformation_id>

The Messaging Gateway agent could not obtain all the information it needs about the transformation. The transformation parameter of DBMS_MGWADM.CREATE_JOB must specify the name of the registered transformation and not the name of the transformation function.

Possible causes include:

• The transformation does not exist. Verify that the transformation has been created. You can see this from the following query performed as user SYS:

  SELECT TRANSFORMATION_ID, OWNER FROM DBA_TRANSFORMATIONS;
  

• The wrong transformation is registered with Messaging Gateway. Verify that the transformation registered is the one intended.

• The Messaging Gateway agent user does not have EXECUTE privilege on the object type used for the from_type or the to_type of the transformation indicated in the exception.

  It is not sufficient to grant EXECUTE to MGW_AGENT_ROLE and then grant MGW_AGENT_ROLE to the agent user. You must grant EXECUTE privilege on the object type directly to the agent user or to PUBLIC.

  The following example shows such a case for the from_type. It also shows the use of linked exceptions for determining the precise cause of the error.

  No EXECUTE Privilege on Object Type

  Errors occurred during processing of job JOB_AQ2MQ_2
  oracle.mgw.common.GatewayException: [703] Failed to retrieve information for
  transformation mgwuser.SAMPLEADT_TO_MGW_BASIC_MSG
  […Java stack trace here…]
  [Linked-exception]
  java.sql.SQLException: "from_type" is null
  […Java stack trace here…]

[720] AQ payload type <type> not supported; queue: <queue>

The payload type of the Oracle Database Advanced Queuing queue used by a Messaging Gateway propagation job is not directly supported by Messaging Gateway. For non-JMS propagation, Messaging Gateway directly supports the payload types RAW, SYS.MGW_BASIC_MSG_T and SYS.MGW_TIBRV_MSG_T.

Possible actions include:

• Configure the Messaging Gateway propagation job to use a transformation that converts the queue payload type to a supported type.

• Remove the Messaging Gateway propagation job and create a new job that uses an Oracle Database Advanced Queuing queue with a supported payload type.

  For Java Message Service (JMS) propagation, the Messaging Gateway propagation job must be removed and a new job created whose Oracle Database Advanced Queuing payload type is supported by Oracle Java Message Service (Oracle JMS). Transformations are not supported for JMS propagation.

[721] Transformation type <type> not supported; queue: <queue_name>, transform: <transformation>

A Messaging Gateway propagation job was configured with a transformation that uses an object type that is not one of the Messaging Gateway canonical types.

For an outbound job, the transformation from_type must be the Oracle Database Advanced Queuing payload type, and the to_type must be a Messaging Gateway canonical type. For an inbound job, the transformation from_type must be a Messaging Gateway canonical type and the to_type must be the Oracle Database Advanced Queuing payload type.

[722] Message transformation failed; queue: <queue_name>, transform: <transformation>

An error occurred while attempting execution of the transformation. ORA-25229 is typically thrown by Oracle Database Advanced Queuing when the transformation function raises a PL/SQL exception or some other Oracle error occurs when attempting to use the transformation.

Possible causes include:

• The Messaging Gateway agent user does not have EXECUTE privilege on the transformation function. This is illustrated in the following example.

  It is not sufficient to grant EXECUTE to MGW_AGENT_ROLE and then grant MGW_AGENT_ROLE to the Messaging Gateway agent user. You must grant EXECUTE privilege on the transformation function directly to the Messaging Gateway agent user or to PUBLIC.

  No EXECUTE Privilege on Transformation Function

  Errors occurred during processing of job JOB_MQ2AQ_2
  oracle.mgw.common.GatewayException: [722] Message transformation failed queue:
  MGWUSER.DESTQ_SIMPLEADT, transform: MGWUSER.MGW_BASIC_MSG_TO_SIMPLEADT
  […Java stack trace here…]
  [Linked-exception]
  oracle.mgw.common.MessageException: [722] Message transformation failed;
  queue: MGWUSER.DESTQ_SIMPLEADT, transform: 
  MGWUSER.MGW_BASIC_MSG_TO_SIMPLEADT
  […Java stack trace here…]
  [Linked-exception]
  java.sql.SQLException: ORA-25229: error on transformation of message msgid:
  9749DB80C85B0BD4E03408002086745E
  ORA-00604: error occurred at recursive SQL level 1
  ORA-00904: invalid column name
  […Java stack trace here…]
  

• The transformation function does not exist, even though the registered transformation does. If the transformation function does not exist, it must be re-created.

• The Messaging Gateway agent user does not have EXECUTE privilege on the payload object type for the queue indicated in the exception.

  It is not sufficient to grant EXECUTE to MGW_AGENT_ROLE and then grant MGW_AGENT_ROLE to the Messaging Gateway agent user. You must grant EXECUTE privilege on the object type directly to the Messaging Gateway agent user or to PUBLIC.

• The transformation function raised the error. Verify that the transformation function can handle all messages it receives.

[724] Message conversion not supported; to AQ payload type: <type>, from type: <type>

A Messaging Gateway propagation job is configured for inbound propagation where the canonical message type generated by the non-Oracle messaging system link is not compatible with the Oracle Database Advanced Queuing queue payload type. For example, propagation from a TIB/Rendezvous messaging system to an Oracle Database Advanced Queuing queue with a SYS.MGW_BASIC_MSG_T payload type, or propagation from WebSphere MQ to an Oracle Database Advanced Queuing queue with a SYS.MGW_TIBRV_MSG_T payload type.

Possible actions include:

• Configure the Messaging Gateway propagation job with a transformation that maps the canonical message type generated by the non-Oracle messaging link to the Oracle Database Advanced Queuing payload type.

• Remove the Messaging Gateway propagation job and create a new job whose Oracle Database Advanced Queuing queue payload type matches the canonical message type generated by the non-Oracle link.

[725] Text message not supported for RAW payload

A Messaging Gateway propagation job is configured for inbound propagation to an Oracle Database Advanced Queuing destination having a RAW payload type. A text message was received from the source (non-Oracle) queue resulting in a message conversion failure.

If support for text data is required, remove the Messaging Gateway propagation job and create a new job to an Oracle Database Advanced Queuing destination whose payload type supports text data.

[726] Message size <size> too large for RAW payload; maximum size is <size>

A Messaging Gateway propagation job is configured for inbound propagation to an Oracle Database Advanced Queuing destination having a RAW payload type. A message conversion failure occurred when a message containing a large RAW value was received from the source (non-Oracle) queue.

If large data support is required, remove the Messaging Gateway propagation job and create a new job to an Oracle Database Advanced Queuing destination whose payload type supports large data, usually in the form of an object type with a BLOB attribute.

[728] Message contains too many large (BLOB) fields

The source message contains too many fields that must be stored in BLOB types. SYS.MGW_TIBRV_MSG_T is limited to three BLOB fields. Reduce the number of large fields in the message, perhaps by breaking them into smaller fields or combining them into fewer large fields.

[729] Message contains too many large (CLOB) fields

The source message contains too many fields that contain a large text value that must be stored in a CLOB. SYS.MGW_TIBRV_MSG_T is limited to three CLOB fields. Reduce the number of large fields in the message, perhaps by breaking them into smaller fields or combining them into fewer large fields.

[805] MQSeries Message error while enqueuing to queue: <queue>

WebSphere MQ returned an error when an attempt was made to put a message in a WebSphere MQ queue. Check the linked exception error code and message in the log file. Consult WebSphere MQ documentation.