| Oracle® Database Gateway for DB2/400 Installation and User's Guide 10g Release 2 (10.2) for IBM iSeries OS/400 Part Number B16222-02 |
|
|
View PDF |
| Oracle® Database Gateway for DB2/400 Installation and User's Guide 10g Release 2 (10.2) for IBM iSeries OS/400 Part Number B16222-02 |
|
|
View PDF |
After installing the gateway, you can run gateway commands and change gateway parameters.
This chapter contains the following sections:
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 the following 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.
| Menu Choice | Command Name | Purpose |
|---|---|---|
|
|
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. |
|
|
|
changes the values of network parameters. |
|
|
|
changes the parameter values of prestarted jobs. |
|
|
|
changes the gateway initialization parameters. |
|
|
|
sets or changes values for debugging parameters(Use only under the guidance of a representative from Oracle Support Services.) |
|
|
|
submits a batch job to create Oracle data dictionary views based on the DB2/400 system catalog. |
|
|
|
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". |
All gateway commands can be 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 that is illustrated in Example 6-1, "CMDORAGTW Oracle Commands Panel" is displayed:
Example 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
________________________________________________________________________________
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 is displayed. 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.You can have as many copies of a gateway instance on your system as you want. After you have installed a release 10 gateway, if you need another instance of the gateway, before you issue the CRTORAGTWI command, then 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 operation.
Ensure that you log on with a user profile that has the *SECADM,*JOBCTL, *ALLOBJ, and *SYSCFG special authorities. The user profile QSECOFR as distributed by IBM has these authorities.
Ensure that a library or collection with the same name as the instance name to be created does not already exist. If it does exist, then it must be empty or must have only those objects that would result from a SQL CREATE COLLECTION command.
After entering 1 at the main menu panel, or CRTORAGTWI, the panel in Example 6-2, "Create Oracle Database Gateway Panel" is displayed.
Example 6-2 Create Oracle Database Gateway Panel
_________________________________________________________________________________________________
Create Oracle Database Gateway V10.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
_________________________________________________________________________________________________
Enter the name of the new instance to be created and press Enter to continue. The panel in Example 6-3, "Create Oracle Database Gateway Panel, with New Values" appears.
Example 6-3 Create Oracle Database Gateway Panel, with New Values
___________________________________________________________________________________________________
Create Oracle Database Gateway V10.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 ................ *YES *YES, *NO
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
___________________________________________________________________________________________________
The first two entries are set from the previous screen and cannot be changed:
Existing instance name is a name from one character to six characters long. The default is whatever is in the ORA_HOME data area. If you ADDLIBLE instance_name, then the value in the ORA_HOME data area will be the existing instance name.
Instance name to create is a name from one character to six characters long.
You must fill in the following choices:
Instance password is the password for the user ID that will be created for the new user profile. The user ID has the same name as the newly-created instance name. You should change this password 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 *YES to prestart the TCP/IP server jobs. Use *NO to prestart NO TCP/IP server 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 will be the same as the port number of the cloned instance. You should enter a different port number because 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 Enter *YES if you wish to reinstall the Data Dictionary support. The default is *NO. If you are copying (cloning) a gateway, then the Data Dictionary was most likely installed when the copied (cloned) gateway itself was installed. In that case, you do not need to reinstall the Data Dictionary.
After entering 2 at the main menu panel or by entering the CHGORANET command, enter the appropriate instance name and press Enter. The panel in Example 6-4, "Change Oracle Network Parameters Panel" appears. Except when you are changing the value of 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.
Example 6-4 Change Oracle Network Parameters Panel
___________________________________________________________________________________________________
Change Oracle Database 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
___________________________________________________________________________________________________
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 (e.g., SEU) 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 that are specified in the ORA(SQLNET_CHG) file.
Some network parameters are documented in the ORA(LISTENER) file but 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 (e.g., SEU) 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 that are specified in the ORA(LISTEN_CHG) file.
On the OS/400 command line, use the CHGORAPJE command, or use the GO CMDORAGTW command, specify option 3, and press Enter. The panel in Example 6-5, "Change Oracle Prestart Parameters Panel" appears. Enter the new values and press Enter to continue.
Example 6-5 Change Oracle Prestart Parameters Panel
___________________________________________________________________________________________________
Change 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
___________________________________________________________________________________________________
For information about what values you should enter for these parameters, move the cursor to the value on the panel and press PF1.
The new values do not take effect until you shut down and restart the gateway that is specified in the Existing instance name parameter of the command.
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 Example 6-6, "Change Oracle Gateway Initialization Parameters (first page of panel)" appears. CHGORATUN uses two panels. Refer to Example 6-6, "Change Oracle Gateway Initialization Parameters (first page of panel)" and Example 6-7, "Change Oracle Gateway Initialization Parameters (second page of panel)".
Note:
CHGORATUN can be run only while you are signed on as the user profile corresponding to the instance ID, or as a user profile that has *SECADM and *ALLOBJ special authorities. (QSECOFR as distributed by IBM has such authorities).For information about 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 |
|---|---|---|
|
If the value for the |
|
|
|
|
The |
|
|
If the |
|
|
|
|
Before deciding on a value for this parameter, refer to "DB2/400 GRAPHIC Support" for more information. |
|
Example 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
___________________________________________________________________________________________________
Example 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
__________________________________________________________________________________________________
The new values do not take effect until you shut down and restart the gateway that is specified in the Existing instance name parameter of the command.
This command is used for isolating the cause of a suspected gateway problem. Use CHGGTWDBG only 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 Example 6-8, "Change Oracle Gateway Debugging Option Panel" appears. For more information about the value choices, move the cursor to the value on the panel and press PF1.
Example 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 show 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
___________________________________________________________________________________________________
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.
This command submits a batch job to create Oracle data dictionary views of the system catalog. If you are going to run an application such as Oracle Developer, then you need to create data dictionary views. Therefore, if you create these views when originally installing the gateway, then 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 system catalog are used by all Oracle Gateway instances on that AS/400. One copy of these views is on each AS/400.
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.
Example 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
__________________________________________________________________________________________
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 as 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 values for recovery user profile and password synchronized with the OS/400 values for recovery user profile and password. 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 when an in-doubt transaction occurs, the Oracle Database alert log will most likely show an ORA-1017 (invalid user ID or password) error. Use theCHGRECOPRF command to change the recovery profile parameters (including recovery user ID and password).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.
The gateway can retrieve multiple rows from a 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, then no block retrieval is performed. This is similar to setting BLOCKSIZE to 1.
If the BLOCKSIZE data area is set to n, then the gateway retrieves n rows from DB2/400 in a single fetch, where n is a value from 1 to 32767.
If the BLOCKSIZE data area does not exist, then the gateway will retrieve one row per fetch from DB2/400.
Note:
For performance reasons, Oracle recommends that theBLOCKSIZE 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.
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 data area is used to hold the data conversion specification.
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, then only queries (SELECT statements) are allowed to DB2/400. The capabilities which control whether updates are allowed through the gateway are not enabled. These capabilities include insert, update, delete, and stored procedure support (pass through SQL, DB2/400 stored procedures, but not DB2/400 User Defined Functions). Statements attempting to modify records at the gateway site are rejected.
Oracle recommends that you do not routinely switch between settings of the read-only parameter. If you need both update and read-only functionality, then 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.
