6
Configuring the Gateway

After installing the gateway, you can run gateway commands and change gateway parameters.

This chapter contains the following sections:

Gateway Commands

All gateway parameters are changed with gateway commands, which are accessed through a menu system. These commands and their menus are described in this section.

The gateway comes with commands to do these tasks:

copy (clone) the gateway
change the most common gateway parameters
change the level of tracing and debugging

All commands can be used after a gateway is installed. The following table summarizes each command and its purpose. The Menu Choice is used when executing the CMDORAGTW menu.

Table 6-1 Command Summary

Menu Choice	Command Name	Purpose
1	CRTORAGTWI	creates a copy of an installed gateway version. You can copy a version as many times as needed. After making a copy, use the other gateway commands to change the parameters of the new copy.
2	CHGORANET	changes the values of network parameters.
3	CHGORAPJE	changes the parameter values of prestarted jobs.
4	CHGORATUN	changes the gateway initialization parameters.
5	CHGGTWDBG	sets or changes values for debugging parameters. (Use only under the guidance of a representative from Oracle Support Services.)
6	CRTORADDB	submits a batch job to create Oracle data dictionary views based on the DB2/400 system catalog.
7	CHGRECOPRF	changes the User Profile name, or the User Profile password, or both, for the User Profile that is associated with transaction recovery. For password precautions, refer to "CHGRECOPRF, Change Recovery Profile Parameters".

Running the Commands

All gateway commands are accessed through a main menu. To invoke the main menu, enter:

ADDLIBLE  instance_name
GO CMDORAGTW

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

The main menu panel illustrated in Figure 6-1 is displayed:

Figure 6-1 CMDORAGTW Oracle Commands Panel

________________________________________________________________________________
 CMDORAGTW                   Oracle Commands
                                                            System:  AS400A
 Select one of the following:

  1. Create instance
  2. Change network settings
  3. Change prestart job settings
  4. Change gateway initialization settings
  5. Change debugging options
  6. Change Oracle Data Dictionary objects (batch)
  7. Change Recovery Profile Parameters


 Selection or command
 ===>

 F3=Exit  F4=Prompt  F9=Retrieve  F12=Cancel
 (C) COPYRIGHT ORACLE CORPORATION, 1994, 2002

________________________________________________________________________________

Enter the choice number (1, 2, 3, 4, 5, 6, or 7) or the corresponding command name:

CRTORAGTWI
CHGORANET
CHGORAPJE
CHGORATUN
CHGGTWDBG
CRTORADDB
CHGRECOPRF

After you have made your selection, press [Enter] to continue. The panel for the command displays. Command panels are described under the individual commands.

Note:

For more information about these choices, move the cursor to the value on the panel and press [PF1].

CRTORAGTWI, Copy the Gateway

You can have as many copies of a gateway instance on your system as you want. Once you have installed a version 9 gateway, if you need another instance of the gateway, before you issue the CRTORAGTWI command you must:

Shut down the instance you are about to copy. Use the ENDSBS command with the instance name as the operand to perform the shutdown.
Ensure you log on with a user profile that has *SECADM special authority; the user profile QSECOFR has this authority.
Ensure the instance name to be created does not exist.

After entering 1 at the main menu panel or CRTORAGTWI, the panel in Figure 6-2 displays.

Figure 6-2 Create Oracle Transparent Gateway Panel

________________________________________________________________________________
                  Create Oracle Transparent Gateway  9.2.0.1.0
                                                              System:  AS400A
 Type choices, press Enter.

  Existing instance name ....        ORACLE        Name (up to six characters)
  Instance name to create ....                     Name (up to six characters)





 ===>

 F1=Help  F4=Prompt  F9=Retrieve  F12=Cancel
 (C) COPYRIGHT ORACLE CORPORATION, 1994, 2002

________________________________________________________________________________

Enter the name of the new instance to be created and press [Enter] to continue. The panel in Figure 6-3 appears.

Figure 6-3 Create Oracle Transparent Gateway Panel, with new values

________________________________________________________________________________
                  Create Oracle Transparent Gateway  9.2.0.1.0
                                                            System:  AS400A
 Type choices, press Enter.

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


 ===>

 F1=Help  F4=Prompt  F9=Retrieve  F12=Cancel
 (C) COPYRIGHT ORACLE CORPORATION, 1994, 2002

________________________________________________________________________________

The first two entries are set from the previous screen and cannot be changed:

Existing Instance name

is a name from one to six characters long. The default is whatever is in the ORA_HOME data area. If you ADDLIBLE instance_name, then ORA_HOME will be the instance name.

Instance name to create

is a name from one to six characters long.

You must fill in the following choices:

Instance password

is the password for the user ID that was created during installation. The user ID has the same name as the newly-created instance name. You should change this for security reasons.

Recovery user profile

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

Recovery user password

is a password for the recovery user profile. The default is the newly-created gateway instance name. You should change this for security reasons. Use the OS/400 CHGUSRPRF command to make the change. Then, use the CHGRECOPRF command (refer to "CHGRECOPRF, Change Recovery Profile Parameters").

Prestart jobs

prestarts the TCP/IP jobs. By default, two TCP/IP jobs are prestarted. Use *TCPIP to prestart the TCP/IP jobs. Use *NONE to prestart no jobs.

You can change how many jobs are prestarted by changing the value of the initial number of TCP/IP jobs parameter with 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, enter a different port number. Each instance requires a unique port number.

Auxiliary storage pool id

uses the default of 1, or you can enter another ID if you have additional auxiliary storage pools defined.

Install Data Dictionary Support

turns data dictionary view support on or off. The default is *NO. Installation of the data dictionary view support lengthens the installation process. If you are copying a gateway, the Data Dictionary was most likely installed when the copied gateway itself was installed. In that case, you do not need to reinstall it.

CHGORANET, Change Network Parameters

After entering 2 at the main menu panel or CHGORANET, enter the appropriate instance name and press [Enter]. The panel in Figure 6-4 appears. Except when you are changing the value for the TCP/IP port number, you should use CHGORANET only under the guidance of a representative from Oracle Support Services. Enter the new values and press [Enter] to continue. The new values do not take effect until you shut down and restart the gateway that was specified in the Existing instance name parameter of the command.

Figure 6-4 Change Oracle Network Parameters Panel

________________________________________________________________________________
                      Change Oracle Transparent Gateway
                                                            System:  AS400A
 Type choices, press Enter.

  Existing instance name .......    ORACLE        Name
  TCP/IP port number ...........    1521          1024-65534
  Client trace level ...........    *OFF          *OFF, *USER, *ADMIN, 16
  Listener trace level .........    *OFF          *OFF, *USER, *ADMIN, 16
  Server trace level ...........    *OFF          *OFF, *USER, *ADMIN, 16
  Regenerate files .............    *NO           *YES, *NO



 ===>

 F1=Help  F4=Prompt  F9=Retrieve  F12=Cancel
 (C) COPYRIGHT ORACLE CORPORATION, 1994, 2002

________________________________________________________________________________

Changing the ORA(SQLNET_CHG) File

Some network parameters are documented in the ORA(SQLNET) file but are not displayed on the CHGORANET panel. These parameters cannot be changed directly by editing the ORA(SQLNET) file. They must be changed in the ORA(SQLNET_CHG) file:

Use an OS/400 editor to change parameter values in ORA(SQLNET_CHG).
On the OS/400 command line, use the CHGORANET command, or use the GO CMDORAGTW command and specify option 2.
Enter the instance name of the gateway, and press Enter.
Specify *YES on the "Regenerate Files" line, and press Enter.

The ORA(SQLNET) file now reflects the values specified in the ORA(SQLNET_CHG) file.

Changing the ORA(LISTEN_CHG) File

There are network parameters documented in the ORA(LISTENER) file that are not displayed on the CHGORANET panel. These parameters cannot be changed directly by editing the ORA(LISTENER) file. They must be changed using the ORA(LISTEN_CHG) file:

Use an OS/400 editor to change parameter values in ORA(LISTEN_CHG).
On the OS/400 command line, use the CHGORANET command, or use the GO CMDORAGTW command and specify option 2.
Enter the instance name of the gateway, and press Enter.
Specify *YES on the "Regenerate Files" line, and press Enter.

The ORA(LISTENER) file now reflects the values specified in the ORA(LISTEN_CHG) file.

CHGORAPJE, Change Prestarted Job Parameters

On the OS/400 command line, use the CHGORAPJE command, or use the GO CMDORAGTW command, and specify option 3, and press Enter. The panel in Figure 6-5 appears. Enter the new values and press [Enter] to continue.

Figure 6-5 Create Oracle Prestart Parameters Panel

________________________________________________________________________________
                      Create Oracle Prestart Parameters
                                                            System:  AS400A
 Type choices for prestart jobs, press Enter.

  Existing instance name ............     ORACLE         Name
  Start TCP/IP jobs .................     *YES           *SAME, *YES, *NO
  Initial number of TCP/IP jobs......     2              1-1000, *SAME
  TCP/IP threshold ..................     1              1-1000, *SAME
  Additional number of TCP/IP jobs ..     2              0-999, *SAME
  Maximum number of TCP/IP jobs .....     *NOMAX         *SAME, *NOMAX
  Start TCP/IP listener .............     *YES           *SAME, *YES, *NO


 ===>

 F1=Help  F4=Prompt  F9=Retrieve  F12=Cancel
 (C) COPYRIGHT ORACLE CORPORATION, 1994, 2002

________________________________________________________________________________

The new values do not take effect until you shut down and restart the gateway specified in the Existing instance name parameter of the command.

CHGORATUN, Change Initialization Parameters

On the OS/400 command line, use the CHGORATUN command, or use the GO CMDORAGTW command and specify option 4. Then press Enter. The panel in Figure 6-6 appears. CHGORATUN uses two panels. Refer to Figure 6-6 and Figure 6-7.

Note:

CHGORATUN can be run only while signed on as the user profile corresponding to the instance ID, or as a user profile that has *SECADM special authority (QSECOFR has such authority). Furthermore, the value specified for "User Profile CCSID" can be changed only by the user profile corresponding to the instance ID (the instance ID is the name given to the library in which the gateway was installed).

For information on what values you should enter for these parameters, move the cursor to the value on the panel and press [PF1].

Enter the new values on the panels. Before entering values for the DATABASE DOMAIN, GATEWAY NATIONAL LANGUAGE, RPC FETCH REBLOCKING, and RPC FETCH SIZE gateway initialization parameters, read the following notes:

Table 6-2 Initialization Parameter Notes

Parameter	Notes	Default
DATABASE DOMAIN	If the value for the GLOBAL_NAME parameter is set to TRUE in the Oracle9i database server INIT.ORA file, then the value you enter in this field must match that specified for the DB_DOMAIN parameter in the Oracle9i database server INIT.ORA file.	WORLD
Gateway Language	The GATEWAY NATIONAL LANGUAGE line no longer appears in the CHGORATUN screen, but NLS_NCHAR actually still does exist for other reasons.	AMERICAN_AMERICA. WE8EBCDIC37
`RPC FETCH REBLOCKING` and `RPC FETCH SIZE`	If the RPC FETCH REBLOCKING parameter is set to YES (the default), then the block size of the buffer for SELECT statements is determined by the value of the RPC FETCH SIZE parameter. The recommended value for Oracle Transparent Gateway for DB2/400 is 40,000. The RPC FETCH SIZE parameter defines the maximum number of bytes sent with each fetch between the gateway and the Oracle9i database server. Each fetch between the gateway and the Oracle9i database server can contain multiple rows from DB2/400.	YES 40 000
V4 GRAPHIC compatibility mode and UCS-2 support	Before deciding on a value for this parameter, refer to "DB2/400 GRAPHIC Support" for more information.	NO

Figure 6-6 Change Oracle Gateway Initialization Parameters (first page of panel)

________________________________________________________________________________
               Change Oracle Gateway Initialization Parameters
                                                        System:  AS400A
 Type choices, press Enter.

 Existing instance name .........   ORACLE           Name
 Database Domain ................   WORLD
 Database Name...................   ORACLE
 Array block size................   100              0-32767
 Gateway language ...............   american_america.we8ebcdic37
                                                     Language ID (NLS_LANG)
 V4 Graphic and UCS-2
   Compatibility mode ...........  *NO               V4 Graphic and UCS-2
                                                       Compatibility mode
                                                       (ORAGRAPH4)
 Maximum Date ...................                    ORA_MAX_DATE
 Option for CCSID=65535 fields     *BITDATA          *BITDATA, *CHARDATA
 User Profile CCSID .............  *SYSVAL           *SAME, *SYSVAL, *HEX, CCSID
                                                       value

 ===>                                                                    More...

 F1=Help  F4=Prompt  F9=Retrieve  F12=Cancel
     (C) COPYRIGHT ORACLE CORPORATION, 1994, 2002

________________________________________________________________________________

Figure 6-7 Change Oracle Gateway Initialization Parameters (second page of panel)

________________________________________________________________________________
               Change Oracle Gateway Initialization Parameters

                                                               System:  AS400A
 Type choices, press Enter.

 Change Isolation Level........  *CHG             *CHG, *CS, *RR
 Set gateway for READ-ONLY.....  *NO              *YES, *NO
 Maximum Number Cursors........  200              50-200
 RPC Fetch Reblocking .........  *YES             *YES, *NO
 RPC Fetch Size ...............  40000            4000-50000





 ====>                                                                  Bottom

 F1=Help  F4=Prompt  F9=Retrieve  F12=Cancel
 (C) COPYRIGHT ORACLE CORPORATION, 1994, 2002

________________________________________________________________________________

The new values do not take effect until you shut down and restart the gateway specified in the Existing instance name parameter of the command.

CHGGTWDBG, Change Debugging Parameters

This command is used for isolating the cause of a suspected gateway problem. Only use CHGGTWDBG under the guidance of a representative from Oracle Support Services.

On the OS/400 command line, use the CHGGTWDBG command, or use the GO CMDORAGTW command and specify option 5. then press [Enter]. Enter the appropriate instance name and press [Enter]. The panel in Figure 6-8 appears. For more information about the value choices, move the cursor to the value on the panel and press [PF1].

Figure 6-8 Change Oracle Gateway Debugging Option Panel

________________________________________________________________________________
               Change Oracle Gateway Debugging Options
                                                        System:  AS400A
 Type choices for debugging options, press Enter.

 Existing instance name ...........   ORACLE        Name
 Use gateway debugging version ....   *NORMAL       *NORMAL, *DEBUG
 Gateway pause during job start ...   *NORMAL       *NORMAL, *PAUSE
 Gateway show GETENV messages .....   *NORMAL       *NORMAL, *YES
 Gateway continue after error .....   *NORMAL       *NORMAL, *CONTINUE
 Gateway hang on error ............   *NORMAL       *NORMAL, *HANG
 Gateway hang time in minutes .....   *DAY          *NORMAL, 1-10080, *HOUR
                                                    *DAY, *WEEK
 Gateway trace level ..............   0             0-255
 Listener pause during job start ..   *NORMAL       *NORMAL, *PAUSE
 Listener GETENV messages .........   *NORMAL       *NORMAL, *YES
 Listener continue after error ....   *NORMAL       *NORMAL, *CONTINUE
 Listener hang on error ...........   *NORMAL       *NORMAL, *HANG
 Listener hang time in minutes ....   *DAY          *NORMAL, 1-10080, *HOUR
                                                    *DAY, *WEEK


 ===>                                                                Bottom

 F1=Help  F4=Prompt  F9=Retrieve  F12=Cancel
 (C) COPYRIGHT ORACLE CORPORATION, 1994, 2002
________________________________________________________________________________

After entering the new values, press Enter to continue. The new values do not take effect until you shut down and restart the gateway specified in the Existing instance name parameter of the command.

CRTORADDB, Create Data Dictionary Views

This command submits a batch job to create Oracle data dictionary views of the DB2/400 system catalog. If you are going to run an application such as Developer 2000, you need to create data dictionary views. Therefore, if you create these views when originally installing the gateway, you should not need to create them again.

On the OS/400 command line, use the CRTORADDB command, or use the GO CMDORAGTW command and specify option 6. Then press [Enter]. Enter the appropriate instance name and press [Enter]. A batch job is submitted. No additional panel appears.

The Oracle Data Dictionary views of the DB2/400 system catalog are used by all Oracle Gateway instances on that AS/400. A single copy of these views is on each AS/400.

CHGRECOPRF, Change Recovery Profile Parameters

This command changes the OS/400 User Profile name or password, or both, for the User Profile name or password that the gateway uses when directed to perform transaction recovery by the Oracle server. Transaction recovery is necessary when any failure occurs during a distributed transaction. The User Profile name and password are created by the OS/400 CRTUSRPRF command or are changed by the OS/400 CHGUSRPRF command.

Figure 6-9 Change Recovery Profile Parameters Panel

________________________________________________________________________________
                   Change Oracle Recovery Profile Parameters
                                                             System:   AS400A
 Type choices, press Enter.

 Existing instance name . . . .   ORACLE       Name
 Recovery Profile Name  . . . .   ORACLE       Profile Name
 Recovery Profile Password  . .                Password
 Confirmation of Recovery
   Profile Password . . . . . .                Password



 F1=Help  F4=Prompt  F9=Retrieve  F12=Cancel
 (C) COPYRIGHT ORACLE CORPORATION, 1994, 2002

________________________________________________________________________________

Enter the User Profile name in the "Recovery Profile Name" field, or leave it as it is. Enter the password for the User Profile name on the next two lines. The password is stored in an encoded form. Because this password is no longer visible in plain text, extra care may be required to keep the recovery password synchronized between the AS/400 computer and the gateway. Refer to the following note.

Caution:

You must keep the gateway recovery password synchronized with the AS/400. The gateway recovery user ID and password must be valid to the AS/400 at all times. If the recovery password is not valid (if it expires, for example), then an in-doubt transaction occurs, and the Oracle database alert log receives error-1017 (invalid user ID or password). Use the CHGRECOPRF command to change the recovery profile parameters (including recovery user ID and password).

Setting Optional Parameters

You can change the values of optional gateway parameters after the product is installed by using the gateway commands. Three commonly changed parameters are:

Array block size in the BLOCKSIZE data area. Refer to "Retrieving Data" for more information.
Default character conversion in the ORARAW data area. Refer to "Data Conversion" for more information.
Set gateway for READ-ONLY for configuring the gateway with read-only capabilities. Refer to "Read-Only Gateway" for more information.

Retrieving Data

The gateway can retrieve multiple rows from a DB2/400 table or view with a single fetch. The gateway uses the BLOCKSIZE data area to determine the number of rows to retrieve. These conditions apply for the BLOCKSIZE data area:

If the BLOCKSIZE data area is set to 0, no block retrieval is performed. This is the same as setting BLOCKSIZE to 1.
If the BLOCKSIZE data area is set to n, the gateway retrieves n rows in a single fetch, where n is a value from 1 to 32767.
If the BLOCKSIZE data area does not exist, the gateway will retrieve one row per fetch to DB2/400.

Note:
For performance reasons, Oracle Corporation recommends the BLOCKSIZE data area be set between 10 and 100.

The gateway uses a default value of 100 for the BLOCKSIZE data area. The BLOCKSIZE value is used for substitution for host variable :N in a SQL FETCH statement such as:

FETCH CN for :N ROWS...

To change this default value, use the gateway command CHGORATUN. After displaying the panel for CHGORATUN, enter a new value for Array block size. Refer to "CHGORATUN, Change Initialization Parameters" for information about changing the setting for Array block size.

Data Conversion

The default coded character set identifier (system value QCCSID) for the AS/400 system is 65535. This CCSID value indicates to the gateway that character data in a column with such a CCSID is not to be converted and is to be treated as bit data.

The line entitled "Option for CCSID=65535 fields" on the "Change Oracle Gateways parameters" panel (use the CHGORATUN command) specifies how the gateway is to handle the "For Bit Data" and "CCSID=65535" fields. If the specification is *BITDATA, then the fields are treated as binary data and no translation occurs. If *CHARDATA is specified then the fields are treated as if they were in the character set ID in which the gateway runs. When using the CHGORATUN command, the "User Profile CCSID" line specifies the character set ID in which the gateway runs. The ORARAW date area is used to hold the data conversion specification.

Read-Only Gateway

The gateway can be configured with read-only capabilities. The read-only option may provide improved performance and security based on your configuration and parameter selections. The READONLY data area controls whether the gateway is enabled in this mode. The default setting for the read-only feature is NO. You can change the value of this environment parameter using the CHGORATUN command.

If you enable the read-only feature by changing the setting to YES, only queries (SELECT statements) are allowed to DB2/400. The capabilities which control whether updates are allowed through the gateway are disabled. These capabilities include insert, update, delete, and stored procedure support (pass-through SQL, DB2/400 stored procedures). Statements attempting to modify records at DB2/400 are rejected.

Oracle Corporation recommends that you do not routinely switch between settings of the read-only parameter. If you need both update and read-only functionality, you should install two separate instances of the gateway with different read-only settings.

Refer to "CHGORATUN, Change Initialization Parameters" for information about changing the READONLY setting.

6 Configuring the Gateway