4 Installing the Gateway

Before you begin installation, ensure that you have the required hardware and software described in Chapter 3, "System Requirements". This chapter contains the following sections:

Gateway Coexistence

Oracle Database Gateway for DB2/400 Release can coexist with any previous version of the product. There are no known conflicts. Oracle Corporation does not support upgrading a previous version gateway to a version 10 gateway. You must reinstall the gateway. The various instances of the gateway on a computer have no knowledge of each other, other than through the DB2/400 files against which the gateways are executed, and through the data dictionary tables.

Caution — Potential Loss of Data:

After the data dictionary tables are installed by a release 10.2 Gateway, do not reinstall the data dictionary tables from an earlier release of the gateway. If this is done by mistake, then you can reinstall the version 10.2 data dictionary table by using the CRTORADDB command that is described in Chapter 6, "Configuring the Gateway".

Preinstallation Steps

Before installing the gateway, you must perform the following steps:

Step 1: Log On

Log on with a user profile that has the following special authorities: *SECADM, *JOBCTL, *ALLOBJ, and *SYSCFG. User profile QSECOFR (as distributed by IBM) has these authorities.

Step 2: Verify System Value for the QCRTAUT Parameter

Ensure that the system value for the QCRTAUT parameter is not *EXCLUDE. To find the current system value, enter the following statement from the command line:


If the system value is *EXCLUDE, then change it to any other less restrictive value in order to allow installation of the gateway, and remember to change QRCTAUT back to *EXCLUDE after installing the gateway.

Installation Steps

The panels below (beginning with Example 4-1, "Install Oracle Database Gateway Panel") are shown as they appear when they are first displayed. Any values on the panel are default values that are provided by Oracle. Default values are used unless you change them before going on to the next panel.

The installation procedure defines an OS/400 user ID that is identical to the name of the install library. This user ID is known as the gateway ID. The installation procedure also defines that ID with a user class of *USER, which has *JOBCTL authority. This ID is given authority over the entire library. To do that, the commands executed during installation are:

GRTOBJAUT OBJ(gateway-id/*ALL) OBJTYPE(*ALL) USER(gateway_id) AUT(*ALL)

where gateway-id is both the gateway user ID and the library name.

The security changes that are in the following six lines of example code may be made to allow the gateway to adopt the security authority of the "connect to" user ID. The GRTOBJAUT should be done only if the referenced user (in this case, *PUBLIC) does not already have the indicated authority to the object.


You can use the EDTOBJAUT command to check the authorities of the three objects. For example, to check the current authority of the QWTSETP program in the QSYS library, use the following command:


If the *PUBLIC line shows *USE, then no change is needed.

Step 1: Mount the CD-ROM

Insert the CD-ROM into the AS/400 CD-ROM drive.

Step 2: Start the Installation Process

Enter the following command:

LODRUN drive

where drive is the name of the CD-ROM drive where you mounted the product CD-ROM. The panel in Example 4-1, "Install Oracle Database Gateway Panel" is displayed.

Step 3: Set Required Parameters

The following parameter must be set: Instance Name


Install Oracle Database Gateway.


You must fill in the Instance name. Use the default name of ORACLE or enter a name from one to six characters long.

The installation procedure either creates a new library, uses an existing (but empty) library, or uses an existing library that has been created using the DB2/400 SQL command CREATE COLLECTION. If the library does not fit into this scenario, then it will not be used. Refer to Example 4-1, "Install Oracle Database Gateway Panel". The library must contain no objects other than those objects that were created by the CREATE COLLECTION command.

The preferred method is to use STRSQL to create a DB2/400 collection and then to install the gateway into the library that is associated with that collection. Objects that are created later in the collection will be automatically journaled.

Example 4-1 Install Oracle Database Gateway Panel

                 Install Oracle Database Gateway at V10.

 Type Choices, press Enter.

 Instance name.............  ORACLE                Name (up to six characters)

 F1=Help   F4=Prompt   F9=Retrieve   F10=Additional parameters   F12=Cancel


After entering a name, press Enter. The panel in Example 4-2, "Install Oracle Database Gateway Panel, With Name Choices" appears.

Example 4-2 Install Oracle Database Gateway Panel, With Name Choices

               Install Oracle Database Gateway at V10.
                                                                 System: AS400A
 Type Choices, press Enter.

 Instance name.....................  ORACLE         Name (up to six characters)
 Instance password.................  ORACLE         1-10 characters
 Prestart jobs.....................  *YES           *YES, *NO
 TCP/IP port number................  1521           1024-65534
 Recovery user profile.............  ORACLE         Name
 Recovery user password............  ORACLE         1-10 characters
 Auxiliary storage pool id.........  1              1-16
 Install Data Dictionary Support...  *YES           *YES, *NO


 F1=Help   F4=Prompt   F9=Retrieve   F12=Cancel

You may change the choices:

Table 4-1 Name Choices on Install Panel

Choice Name Description

Instance name

is a name from one to six characters long. The default is ORACLE.

Instance password

is the password for the user ID, the Instance User Profile, (same as OS/400 User Profile) that is created during installation. The Instance User Profile (user ID) has the same value as the instance name.

Prestart jobs

prestarts the server jobs. Use *YES to prestart the server jobs, or *NO to prestart NO jobs. The server jobs run the Gateway executable.

You can change how many jobs are prestarted by changing the value of the initial number of TCP/IP jobs parameter by using the CHGORAPJE command. For more information, refer to "CHGORAPJE, Change Prestarted Job Parameters".

TCP/IP port number

is 1521, which is the default. If port 1521 is unavailable, then enter a different port number. Each instance requires a unique port number. Each instance has its own LISTENER.

Recovery user profile

is a profile name that you enter, or use the default of the gateway instance name.

Recovery user password

is a password for the recovery user profile. The default is the gateway instance name. Following installation, this password must be kept in synchronization with the password for the Recovery User Profile, which is changed by using the AS/400 CHGUSRPRF command. Refer to "CHGRECOPRF, Change Recovery Profile Parameters"

Auxiliary storage pool id

uses the default of 1, or you can enter another numeric ID.

Install Data Dictionary Support

turns on data dictionary installation. The default is *YES. Installation of the data dictionary view support lengthens the installation process. Only one Database Gateway data dictionary is created for the entire AS/400 computer. All Database Gateway instances use the same data dictionary. The data dictionary is created in the library OTGDB2.

After filling in the choices, press Enter to continue.

Step 4: Verify Installation

The following steps verify installation of the gateway.


Verify Oracle Database Installation.


Enter *YES and press Enter to continue the installation process.

Example 4-3 Verify Oracle Install Panel

                    Verify Oracle Install at V10.
                                                               System: AS400A

 Verify that you want to install the product.  Once this process has started, it 
 will create a library and other objects with the name of the instance 
 specified below.  Enter *YES to verify this operation before it is started.

 New instance name .........   ORACLE
 enter *YES to verify.......   *NO     *NO, *YES

 ===>                                                                 Bottom

 F1=Help   F4=Prompt   F9=Retrieve   F12=Cancel

Step 5: Finish the Installation

If you entered *YES, then a message is displayed on the screen approximately 5 minutes to 15 minutes later to inform you that the installation is finished. Check the job log for error messages by using the following command:


Step 6: Start the Gateway Subsystem

Enter the following command:

STRSBS instance_name/instance_name

where instance_name is the name that is used in "Step 3: Set Required Parameters".

Installation of the gateway is complete.

Postinstallation Steps

If you have more than one instance of the gateway, then repeat postinstallation steps for each instance. Note that you need to install data dictionary support only once. Refer to the table of installation choices in "Step 3: Set Required Parameters" for more information on data dictionary support. Oracle recommends performing the following postinstallation steps:


Journaling is the process of recording changes that are made to files on the AS/400 in order to ensure transactional consistency. Every table on the AS/400 that is going to be changed by the gateway must be journaled. Changes to a file are recorded by a JOURNAL in a JOURNAL RECEIVER. Journaling is not required for gateways that are used for read-only purposes.

By default, the gateway runs under commitment control change (*CHG). This means that:

  • AS/400 files that are not changed by SQL operations do not need to be journaled.

  • Users can see pending changes that have been input by others before the changes have been committed.

You can change the isolation level from the default setting, *CHG, to cursor stability, *CS. With cursor stability:

  • Users cannot see current changes until the changes have been committed.

  • All files that are changed by SQL operations must be journaled.

For additional information about changing the isolation level, refer to "CHGORATUN, Change Initialization Parameters" .

For information about the *RR isolation level setting and for more information about *CS and *CHG isolation level settings, refer to the Isolation Level section of the IBM reference for DB2/400.


When creating a SQL collection, DB2/400 creates a journal and a journal receiver within that collection. Any files (tables) that are created within that collection are automatically journaled. For this reason, it is strongly recommended that you create a SQL collection before installing the Gateway and then install the Gateway into that SQL collection.

Step 1: Verify the Gateway Version

To verify installation and find out what version of the gateway is running, use the following command:

DSPPGM <instance_name>/GTW


instance_name is the name that is given to the gateway when it is installed.

GTW is the name of the gateway program object (the gateway executable program).

The Text Description line resulting from the DSPPGM command informs you of the gateway version.

You can also find out what version of the gateway is running by looking in the job logs for a RUNORAGxxx job. Usually the job name is RUNORAGTTP.

Step 2: Verify Journaling

Journal all AS/400 files that are changed through the gateway. To start journaling for a file, use the STRJRNPF command. To use the STRJRNPF command, you must have previously created a journal and a journal receiver, as illustrated in the following example. Note that if you installed the gateway into a library that was created as a SQL collection, then the journal and journal receiver already exist. You do not need to explicitly journal files that are created in a library that is part of a DB2/400 SQL collection. You can journal many files to the same pair of journal and journal receiver.

STRJRNPF FILE(library/file) JRN(instance/QSQJRN)


instance is the name that is given to the gateway when it is installed.

library is the name of the library where the journal is located.

file is the name of the file that you are journaling.


If you attempt to change a file that is not journaled, then you may receive an ORA-28500 error (connection from ORACLE to a non-Oracle system returned this message), followed by a second error message that is generated by the target system or gateway (usually a SQL 7008:filename in library not valid for this operation). In this case, enter CRTJRNRCV to create the journal receiver, and then enter CRTJRN to create the journal. Then, start journaling the specified file by using the STRJRNPF command.

Refer to the Isolation Level section of the IBM reference for DB2/400 for more information about journaling and its parameters.

Step 3: Journal the ORACLE2PC File

This step is required only if:

  • you are planning to issue a distributed update between the Oracle Database 10g server and the gateway

  • any OS/400 stored procedures are executed through the gateway

  • any OS/400 User Defined Function executed anywhere other than in a SQL SELECT statement. User Defined Functions executed in a SQL SELECT statement cannot cause any changes to the DB2/400 data.

Journal the ORACLE2PC physical file, which records two-phase commit transactions. You must journal this file before issuing a distributed transaction to the AS/400. To journal the file, enter:

STRJRNPF FILE(instance_name/ORACLE2PC) JRN(library/journal)


instance_name is the name that is given to the gateway when it is installed.

library is the name of the library where the journal is located.

journal is the name of the journal.

Step 4: Configure Oracle Net

Configure Oracle Net on the Oracle Database 10g server. Refer to Chapter 5, "Oracle Net" for more information.

Step 5: Remove Observability

The gateway is shipped with IBM observability. Removing observability reduces gateway program object size by approximately 60 percent. If disk space is a concern and if you have no plans to upgrade to a higher version of OS/400, then Oracle recommends that you remove observability from the gateway.

To remove observability, perform the following steps:

  1. Stop the gateway by entering:

    ENDSBS instance_name *IMMED

    where instance_name is the name that is given to the gateway when it is installed.

  2. Use the SBMJOB command to submit a batch job using the QSECOFR ID. The SBMJOB syntax is:

    SBMJOB CMD(CALL instance_name/REMOVEOBS PARM(instance_name))

Step 6: Raise the Gateway Priority Level

The gateway subsystem and listener default to an AS/400 priority level of 50 during the installation process. In many cases, a priority level of 50 is conducive to performing the functions that are provided by the gateway. However, if the AS/400 is running at a high CPU capacity with several jobs competing for CPU allocation, then you might need to raise the priority of the gateway subsystem and listener to meet your performance requirements.


The gateway runs as a batch job. Raising the priority can impact the performance of other processes. Raising the priority of the gateway to a priority higher than 20 is not recommended. (Lower numbers represent higher priority levels.)