Gateway Coexistence

Oracle Database Gateway for DB2/400 Release 10.2.0.1.0 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".

Checklists

The following sections list the various checklists required while installing the gateway.

Preinstallation 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:

WRKSYSVAL QCRTAUT

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:

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.

Step 3: Set Required Parameters

Panel

Install Oracle Database Gateway.

Action

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.

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.

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.

Panel

Verify Oracle Database Installation.

Action

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

Example 4-3 Verify Oracle Install Panel

___________________________________________________________________________________________________
Verify Oracle Install at V10.2.0.1.0
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

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

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.

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.

Note:

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

where:

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

GTWis 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.

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

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

fileis the name of the file that you are journaling.

Note:

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)

where:

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

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:

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.

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.

Note:

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.)

Scripting on this page enhances content navigation, but does not change the content in any way.