This model discards the Oracle user name and password.

The authorizations granted to the effective user ID of the gateway by the queue manager are the only associations an Oracle application has. For example, if the gateway user ID is granted permission to open or read messages, or place messages on a queue, then all Oracle applications that access the gateway can request those operations.

The effective user ID is determined by how the gateway runs:

• If the gateway runs as an MQI client application, then the user ID is determined by the MQI channel definition.

  See Also:

  Refer to IBM publications for more information about channel definitions

• If the gateway runs as an MQI server application, then the effective user ID of the gateway is the user account that started the Oracle Net listener and has authorization to all the WebSphere MQ objects that the Oracle application wants to access.

Oracle recommends using the relaxed model only if your application has minimal security requirements.

This model uses the Oracle user ID and password provided in the CREATE DATABASE LINK statement when a database link is created, or the current Oracle user ID and password if none was provided with CREATE DATABASE LINK.

The Oracle user ID:

• Must match a user account for the system that runs the gateway and for the system that runs the WebSphere MQ queue manager

• Must have authorization for all the accessed WebSphere MQ objects.

The authorization process to verify the Oracle user ID and password varies, depending on how the gateway runs.

This topic describes the access authorization for WebSphere MQ objects.

The effective user ID for the relaxed model and the Oracle user ID for the strict model require the WebSphere MQ authorizations described in Table 8-1.

When an Oracle distributed database contains a gateway, the gateway must be properly configured to take part in a distributed transaction. 

The outcome of a distributed transaction involving a gateway should be that all participating sites roll back or commit their parts of the distributed transaction. All participating sites, including gateway sites, that are updated during a distributed transaction must be protected against failure and must be able to take part in the two‐phase commit mechanism.

A gateway that updates a target system as part of a distributed transaction must be able to take part in the automatic recovery mechanism, which might require that recovery information be recorded in transaction memory at the target system.

If a SQL‐based gateway is involved in a distributed transaction, the distributed database must be in a consistent state after the distributed transaction is committed.

A database gateway or a SQL‐based gateway with the procedural option translates remote procedure calls into target system calls. From the viewpoint of the Oracle transaction model, the gateway is like an Oracle database executing a PL/SQL block containing SQL statements that are used to access an Oracle database.

For a database gateway, it is unknown if a target system call alters data. To ensure the consistency of a distributed database, it must be assumed that a database gateway updates the target system. Accordingly, all remote procedure calls sent to a database gateway take part in a distributed transaction and must be protected by the two‐phase commit protocol. For example, you could issue the following SQL*Plus statements:

EXECUTE REMOTE_PROC@FACTORY;
INSERT INTO DEBIT@FINANCE
ROLLBACK;

In this example, REMOTE_PROC is a remote procedure call to access a database gateway, DEBIT is an Oracle table residing in an Oracle database, and FACTORY and FINANCE are database links used to access the remote sites.

When gateways are involved in a distributed transaction, the transaction capabilities of the non‐Oracle data source determine whether the data source can participate in two‐phase commit operations or distributed transactions.

Depending on the capabilities of the non‐Oracle data source, transactions can be classified as one of the following types:

Transactions from an Oracle application (that invoke WebSphere MQ message queue operations and that are using the gateway) are managed by the Oracle transaction coordinator at the Oracle database where the transaction originates.

The Oracle Database Gateway for WebSphere MQ provides the following transaction types:

The gateway architecture includes a number of components. Any of these components can detect and report an error condition while processing PL/SQL code.

An error condition can be complex, involving error codes and supporting data from multiple components. In all cases, the Oracle application receives a single Oracle error code on which to act.

Error conditions are represented in the following ways:

• Errors from the Oracle database

  Messages from the Oracle database are in the format ORA‐xxxxx or PLS‐xxxxx, where xxxxx is a code number.  ORA‐xxxxx is followed by text explaining the error code.

  For example:

  PLS‐00306: wrong number or types of arguments in call to 'MQOPEN'
  ORA‐06550: line7, column 3:
  PL/SQL: Statement ignored
  

• Gateway and WebSphere MQ errors

  When possible, a WebSphere MQ error code is converted to an Oracle error code. If that is not possible, then the Oracle error ORA‐29400 with the corresponding WebSphere MQ error code is returned.

  For Example:

  ORA-29400: data cartridge error
  MQI MQCONNX failed. completion code=2, reason code=2058

  Note:

  Because the Oracle database distinguishes only between a successful or failed outcome of all user operations, MQI calls that return a warning are reported as a successful operation.

The error conditions that are described in this section are common error conditions that an application might receive while using the gateway.

The gateway has a trace feature for testing and debugging purposes.

The trace feature collects information about the gateway running environment, MQI calls, and parameter values of the MQI calls. The amount of trace data to collect is based on the tracing level selected with the TRACE_LEVEL parameter.

The trace data is written to the directory and file specified by the LOG_DESTINATION parameter.

If your application cannot connect to the gateway, then rerun the application with the gateway trace feature enabled.

If no trace information is written to the log file specified by LOG_DESTINATION, or if the log file is not created at all, then verify that:

• The Oracle Net configuration for the gateway and the Oracle database is set up properly.

• A database link exists between the Oracle database and the gateway was created.

If the Oracle Net configuration and database link are set up correctly, then check the operation of the gateway with the test.sql script:

1. Change directory to the gateway sample directory by entering:

   For Microsoft Windows:

   > cd %ORACLE_HOME%\dg4mq\sample
   

   For UNIX based systems:

   $ cd $ORACLE_HOME/dg4mq/sample
   

2. Using an editor, modify the test.sql script as follows:

   1. Specify the database link name that you created for the gateway. To do this, replace the characters @dg4mq with @dblink, where dblink is the name you chose when the database link was created.

   2. Replace the characters YOUR_QUEUE_NAME with a valid WebSphere MQ queue name.

3. Using SQL*Plus, connect to your Oracle database as a valid user.

4. Run test.sql, a script that sends and retrieves a message from a WebSphere MQ queue. A successful completion displays the following output:

   SQL> @test.sql
   message put on queue = 10203040506070809000
   MQPUT: CorrelId length = 24
   MQPUT: MsgId length = 24
   MQPUT returned with reason code 0
   MQGET returned with reason code 0
   message read back = 10203040506070809000
   

   An unsuccessful test displays the following output:

   SQL> @test.sql
   message put on queue = 10203040506070809000
   Error: Oracle Database Gateway for WebSphere MQ verification script failed.
   ORA-29400: data cartridge error
   MQI MQOPEN failed. completion code=2, reason code=2085