7 Configuration

7.2.3.1 Step 1: Choose a System ID for the Gateway

The gateway SID is a string of 1 to 64 alphanumeric characters that identifies a gateway instance. The SID is used in the gateway boot file and as part of the file name for the gateway parameter file. Choose a SID different from the default SID and different from dg4mqs and dg4mqc.

You need a distinct gateway instance, and SID, for each queue manager you want to access. If you want to access two different queue managers, then you need two gateway SIDs, one for each instance of the gateway. If you have one queue manager and want to access it sometimes with one set of gateway parameter settings and at other times with different gateway parameter settings, then you can do this by having multiple gateway SIDs for one queue manager.

7.2.3.2 Step 2: Customize the Gateway Initialization File

The gateway initialization file (initsid.ora) supports all database gateway initialization parameters described in Appendix C, "Gateway Initialization Parameters". The initialization file must be available when the gateway is started.

During installation, a default initialization file is created in ORACLE_HOME\dg4mq\admin\initsid.ora on Microsoft Windows and $ORACLE_HOME/dg4mq/admin/initsid.ora, on UNIX based systems where sid is the default SID of dg4mqs or dg4mqc. If you chose an SID other than the default, then rename this file using the SID you chose in Step 1: Choose a System ID for the Gateway. Customize the default initialization file as necessary.

The following entries might appear in an initialization file:

LOG_DESTINATION=log_file
QUEUE_MANAGER=manager_name
AUTHORIZATION_MODEL=auth_model
TRANSACTION_MODEL=tx_model
TRANSACTION_LOG_QUEUE=tx_queue_name
TRANSACTION_RECOVERY_USER=rec_user
TRANSACTION_RECOVERY_PASSWORD=rec_password
TRACE_LEVEL=0
MQSERVER=channel
MQCCSID=character_set

In this file:

• log_file specifies the full path name of the gateway log file.

• manager_name is the name of the WebSphere MQ queue manager to access.

• auth_model is the authorization model to use. The default value is RELAXED.

• tx_model is the transaction model to use. The default is SINGLE_SITE.

• tx_queue_name is the name of the queue for logging transaction IDs for distributed transactions. This is used only when tx_model is set to COMMIT_CONFIRM.

• rec_user specifies the user name that the gateway uses to start recovery of a distributed transaction. This is used only when auth_model is set to STRICT and tx_model is set to COMMIT_CONFIRM.

• rec_password specifies the password of the user name that the gateway uses to start recovery of a distributed transaction.

• channel specifies the location of the WebSphere MQ server and the communication method to use. The channel format is:

  channel_name/connection_type/hostname [(port_number)].

  For example:

  MQSERVER=CHAN9/TCP/dolphin(1425)
  

• character_set specifies the coded character set number used by the gateway when communicating with the WebSphere MQ queue manager. This is an optional parameter.

  This parameter is set only if the system that is running the WebSphere MQ queue manager uses a different encoding scheme than the system that runs the gateway. When set, the value of character_set is used by the WebSphere MQ client software on the gateway system to convert the data.

Refer to Chapter 8, "Gateway Running Environment" for more information on transaction and security models.

7.3.2.1 Step 1: Configure the Oracle Net Oracle Net Listener for the Gateway

Two entries must be added to the listener.ora file:

• A list of Oracle Net addresses for the Oracle Net Listener to listen on

• The gateway process that the Oracle Net Listener should start in response to incoming connection requests

  Note:

  The Oracle Net Listener and the gateway must reside on the same node. If you already have a Oracle Net Listener running on the node, then you must make the changes suggested in Step 1 and 2 to your existing listener.ora and tnsnames.ora files.

  After making the changes, you can reload the changes by running the reload subcommand in the lsnrctl utility without shutting down the Oracle Net Listener.

Specifying Oracle Net Addresses for the Oracle Net Listener

If you are using Oracle Net and the TCP/IP protocol adapter, then the syntax of an entry in the listener.ora file is:

LISTENER=
  (ADDRESS_LIST=
      (ADDRESS= 
          (PROTOCOL=TCP)
          (HOST=host_name)
          (PORT=port_number)
       )
  )

In this entry:

• host_name is the name of the system where the gateway is installed.

• port_number specifies the IP port number used by the Oracle Net Listener. If you have other listeners running on host_name, such as the listener of an Oracle database on the same system, then the value of port_number must be different from the other listener port numbers.

If you are using Oracle Net and the interprocess socket call (IPC) protocol adapter, the syntax of the entry in listener.ora file is:

LISTENER=
  (ADDRESS_LIST=
     (ADDRESS= 
         (PROTOCOL=IPC)
         (KEY=key_name)
     )
   )

In this entry:

• IPC specifies that the protocol used for connections is IPC.

• key_name is the unique user-defined service name.

Entry for the Gateway

To configure the Oracle Net Listener to listen for a gateway instance in incoming connection requests, add an entry to the listener.ora file using the following syntax:

SID_LIST_LISTENER=
   (SID_LIST=
      (SID_DESC=
          (SID_NAME=gateway_sid)
          (ORACLE_HOME=gateway_directory)
          (PROGRAM=driver)
       )
    )

In this entry:

• gateway_sid specifies the SID of the gateway and matches the gateway SID specified in the connect descriptor entry in the tnsnames.ora file. Refer to Configuring Oracle Net for Oracle Database.

• gateway_directory specifies the gateway directory in which the gateway software resides.

• driver is the name of the gateway executable file. If the gateway uses a local WebSphere MQ server, then the file name is dg4mqs. The file name is dg4mqc if the gateway is run as a WebSphere MQ client to access a remote WebSphere MQ server.

When you add an entry for multiple gateway instances, add the entry to the existing SID_LIST syntax:

SID_LIST_LISTENER=
 (SID_LIST=
      (SID_DESC=.
                .
                .
      )
      (SID_DESC=.
                .
                .
      )
      (SID_DESC= 
          (SID_NAME=gateway_sid)
          (ORACLE_HOME=gateway_directory)
          (PROGRAM=driver)
       )
  )

The following are examples of entry made to the listener.ora file:

For Microsoft Windows:

(SID_DESC =
         (SID_NAME=dg4mqs)
         (ORACLE_HOME=gateway_directory)
         (PROGRAM=dg4mqs)
)

For UNIX based systems:

(SID_DESC =
         (SID_NAME=dg4mqs)
         (ORACLE_HOME=/oracle/app/oracle/product/dg4mq)
         (PROGRAM=dg4mqs)
)

7.3.2.2 Step 2: Stop and Start the Oracle Net Listener for the Gateway

The Oracle Net Listener must be started or reloaded to initiate the new settings.

• Set the gateway directory name:

  For Microsoft Windows:

  set TNS_ADMIN=c:\orant\network\admin
  

  If you are using the Bourne or Korn shell, then enter:

  $ ORACLE_HOME=gateway_directory;export ORACLE_HOME
  

  If you have the C shell, then enter:

  $ setenv ORACLE_HOME gateway_directory
  

  In this entry:

  gateway_directory specifies the directory where the gateway software is installed.

• If the listener is already running, then use the lsnrctl command to reload the listener with the new settings:

  For Microsoft Windows:

  c:\orant\bin> lsnrctl reload your_listener_name
  

  For UNIX based systems:

  $ cd $ORACLE_HOME/bin
  $ ./lsnrctl reload your_listener_name
  

  In this entry:

  ORACLE_HOME specifies the directory where the gateway software is installed.

• Check the status of the listener with the new settings:

  For Microsoft Windows:

  c:\orant\bin> lsnrctl status your_listener_name
  

  For UNIX based systems:

  $ ./lsnrctl status listener_name
  

  The following are examples of the output from a lsnrctl status check:

For Microsoft Windows:

Connecting to (ADDRESS=(PROTOCOL=IPC)(KEY=ORAIPC))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for MS Windows: version 11.2.0.1.0 - Production
Start Date                21-JUN-09 18:16:10
Uptime                    0 days 0 hr. 2 min. 19 sec
Trace Level               off
Security                  OFF
SNMP                      OFF
Listener Parameter File   \oracle\app\oracle\product\dg4mqs\network\admin\listener.ora
Listener Log File         \oracle\app\oracle\product\dg4mqs\network\log\listener.log
Services Summary...
  dg4mqs            has 1 service handler(s)
The command completed successfully

For UNIX based systems:

Connecting to (ADDRESS=(PROTOCOL=IPC)(KEY=ORAIPC))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Solaris: version 11.2.0.1.0 - Production
Start Date                21-Jun-09 18:16:10
Uptime                    0 days 0 hr. 2 min. 19 sec
Trace Level               off
Security                  OFF
SNMP                      OFF
Listener Parameter File   /oracle/app/oracle/product/dg4mqs/network/admin/listener.ora
Listener Log File         /oracle/app/oracle/product/dg4mqs/network/log/listener.log
Services Summary...
  dg4mqs            has 1 service handler(s)
The command completed successfully

In the example, dg4mqs is the default SID value that was assigned during installation. You can use any valid ID for the SID, or keep the default.

7.4.2.1 TCP/IP Example

An Oracle database accesses the gateway using Oracle Net and the TCP/IP protocol adapter. The syntax of the connect descriptor entry in tnsnames.ora is:

tns_name_entry=
  (DESCRIPTION=
     (ADDRESS=
          (PROTOCOL=TCP)
          (HOST=host_name)
          (PORT=port_number)
     )
     (CONNECT_DATA=
     (SID=gateway_sid)
     )
     (HS=OK)
) 

In this example:

• tns_name_entry is the tns_name_entry of the CREATE DATABASE LINK statement. Refer to "Creating Database Links" for more information.

• TCP specifies that the protocol used for connections is TCP/IP.

• port_number is the port number used by the Oracle Net Oracle Net Listener that listens for the gateway. This port number can be found in the listener.ora file that is used by the Oracle Net Listener. Refer to "Specifying Oracle Net Addresses for the Oracle Net Listener".

• host_name specifies the system on which the gateway is running. The Oracle Net Listener host name can be found in the listener.ora file used by the Oracle Net Listener that is listening for the gateway. Refer to "Specifying Oracle Net Addresses for the Oracle Net Listener".

• gateway_sid specifies the SID of the gateway and matches the SID specified in the listener.ora file of the Oracle Net Listener that listens for the gateway.

7.4.2.2 IPC Example

An Oracle database accesses the gateway using Oracle Net and the IPC protocol adapter. The syntax of the connect descriptor entry in tnsnames.ora is:

tns_name_entry=
    (DESCRIPTION=
        (ADDRESS=
        (PROTOCOL=IPC)
        (KEY=key_name)
    )
    (CONNECT_DATA=
        (SID=gateway_sid)
    )
     (HS=OK)
  ) 

where:

• tns_name_entry is the tns_name_entry of the CREATE DATABASE LINK statement. Refer to "Creating Database Links" for more information.

• IPC specifies that the protocol used for connections is IPC.

• key_name is the service name.

• gateway_sid specifies the SID of the gateway and matches the SID specified in the listener.ora file of the Oracle Net Listener that is listening for the gateway.

7.7.1.1 Step 1: Choose a Repository Server

A repository server is an Oracle database on which the Visual Workbench repository is installed.

7.7.1.2 Step 2: Locate the Installation Scripts

The Visual Workbench repository installation scripts are installed with the Visual Workbench. If the repository is to be installed on the same system as Oracle Visual Workbench, then your repository server already has all the required installation scripts. Proceed to Step 3.

1. Create a directory on the repository server to be the script directory. For example:

   For Microsoft Windows:

   > md %ORACLE_HOME%\dg4mqadmin\repo
   

   For UNIX based systems:

   $ mkdir $ORACLE_HOME/dg4mq/admin/repo
   $ chmod 777 $ORACLE_HOME/dg4mq/admin/repo
   

2. Use a file transfer program to transfer the repository zip file (reposXXX.zip, where XXX is the release number), or move all script files with the .sql suffix from the script file directory (ORACLE_HOME\dg4mqvwb\server\admin on Windows) on the Visual Workbench system to the script file directory on the repository server system.

7.7.1.3 Step 3: Upgrade the Visual Workbench Repository

Upgrade your existing Visual Workbench repository installation scripts by copying the pgmxxx.sql files installed with the Oracle Database Gateway for WebSphere MQ in the ORACLE_HOME\dg4mq\admin\deploy directory on Microsoft Windows and ORACLE_HOME/dg4mq/admin/deploy directory on UNIX based systems to the script file directory on the repository server system.

7.7.1.4 Step 4: Ensure that the UTL_RAW Package is Installed

All data mapping packages generated by the Visual Workbench use the UTL_RAW package, which provides routines for manipulating raw data.

From SQL*Plus, as the SYS user, issue the following statement:

SQL> DESCRIBE UTL_RAW

If the DESCRIBE statement is successful, then your repository server already has UTL_RAW installed, and you can proceed to Step 4.

If the DESCRIBE statement fails, then install UTL_RAW:

From SQL*Plus, as the SYS user, run the utlraw.sql and prvtrawb.plb scripts that are in the ORACLE_HOME\rdbms\admin directory on Microsoft Windows and ORACLE_HOME/rdbms/admin directory on UNIX based systems. You must run the utlraw.sql script first.

SQL> @utlraw.sql
SQL> @prvtrawb.plb

7.7.1.5 Step 5: Ensure that the DBMS_OUTPUT Package is Enabled

The sample programs and installation verification programs on the distribution CD-ROM use the standard DBMS_OUTPUT package.

From SQL*Plus, as SYS user, issue the following statement:

SQL> DESCRIBE DBMS_OUTPUT

If the DESCRIBE statement is successful, then your repository server has DBMS_OUTPUT installed, and you can proceed to Step 6.

If the DESCRIBE statement fails, then install DBMS_OUTPUT.

7.7.1.6 Step 6: Create a Database Link

Create a database link on your Oracle Production System Server to access the Oracle Database Gateway for WebSphere MQ.

If you do not already have a database link, then refer to "Administering the Database Links Alias Library" for more information about creating database links.

7.7.2.1 Step 1: Enter the Database Connection Information

Use the default vale of LOCAL by pressing Enter. Next, you are prompted to enter the passwords for the SYSTEM and SYS accounts of the Oracle database. Press Enter after entering each password.

The script stops if any information is incorrect. Verify the information before rerunning the script.

7.7.2.2 Step 2: Check for Existing Workbench Repository

The script checks for an existing Visual Workbench repository and for the data dictionary. If neither is found, then the script proceeds to Step 3 below.

If the data dictionary exists, then the script stops. Choose another Oracle database and rerun the script, starting at "Step 1: Choose a Repository Server".

If a Visual Workbench repository exists, then the script gives you the following options:

• Upgrade the existing private repository to public status and proceed to Step 3.

• Replace the existing repository with the new private repository and proceed to Step 3.

• Stop the script.

7.7.2.3 Step 3: Check for The Required PL/SQL Packages

The script checks for the existence of UTL_RAW, DBMS_OUTPUT, and DBMS_PIPE in the Oracle database. If this software exists, then the script proceeds to Step 4.

The script stops if this software does not exist. Refer to Oracle Database Administrator's Guide about the missing software. After the software is installed, rerun the script.

7.7.2.4 Step 4: Install the UTL_PG Package

The script checks for the existence of the UTL_PG package. If it does not exist, then the UTL_PG package is installed. The script then proceeds to Step 5.

If UTL_PG exists, then you are prompted to reinstall it. Press Return to reinstall UTL_PG.

7.7.2.5 Step 5: Create the Administrative User and All Repository Tables

This step creates the administrative user for the Visual Workbench repository as PGMADMIN with the initial password of PGMADMIN. This user owns all objects in the repository.

After this step, a private Visual Workbench repository that includes the PGM_SUP,  PGM_BQM, and PGM_UTL8 packages, is created in the Oracle database, which only the PGMADMIN user can access.

7.7.2.6 Step 6: Create Public Synonyms and Development Roles

This is an optional step to change the private access privileges of the Visual Workbench repository. The private status enables only the PGMADMIN user to have access to the repository. If you enter N and press Enter, then the repository retains its private status.

A public status enables the granting of access privileges to other users besides PGMADMIN. If you want to give the repository public status, then enter Y and press Enter.

7.7.4.1 Step 1: Enter the Database Connection Information

Use the default value of LOCAL by pressing Enter.

Next, you are prompted to enter the passwords for the SYSTEM, SYS, and PGMADMIN accounts of the Oracle database. Press Enter after entering each password.

The script stops if any of the information is incorrect. Verify the information before rerunning the script.

7.7.4.2 Step 2: Check for the Existing Workbench Repository

Enter Y and press Enter for the prompt to remove public synonyms and development roles. This returns the repository to private status. You can exit the script now by entering N and pressing Enter, or you can proceed to the next prompt.

If you are certain you want to remove the private repository, then enter Y and press Enter. The script removes all repository tables and related packages.