Read this chapter to understand how to administer your gateway either by using the Oracle Gateway Monitor or by entering commands at the command line prompt. The following sections are included:
The Oracle Gateway Monitor is a facility that is provided with the Oracle Database Gateway for DB2/400. This facility permits a single interface to perform the following functions:
start and stop an Oracle gateway instance
work with Oracle gateway jobs
start the TCP/IP listener job
display the gateway call stack
work with the gateway job locks
display the open files of the gateway
To execute the Oracle gateway monitor, you must log on to the gateway instance user ID and have the gateway instance library included in your library list. To start the Oracle gateway monitor, enter the following command:
ORAMON
The main menu for the Oracle Gateway Monitor is displayed as shown in Example 8-1, "Monitor Oracle Subsystems".
Example 8-1 Monitor Oracle Subsystems
___________________________________________________________________________________________________
Monitor Oracle Subsystems System: AS400A
Position to _____________ Update frequency *OFF
Type Options, press Enter.
2=Start 4=End 5=Work with 7=Listen 10=Display call stack
11=Work with locks 14=Display open files
Job Aux Aux CPU
Opt Name Type Status Number I/O KB Secs
- ORACLE SBS ACTV 0 0 .0
- LISTENER LIS SELW 015826 564 3386 2.8
- RUNORAGTTP PJ PSRW 015823 124 3725 1.6
- RUNORAGTTP PJ PSRW 015822 138 3737 1.5
F1=Help F2=Change list F3=Exit F5=Refresh F8=Start auto update
___________________________________________________________________________________________________
The columns in the panel in Example 8-1, "Monitor Oracle Subsystems" represent the following:
is used to perform various functions on subsystems and jobs. For example, option 2 starts a gateway subsystem. Enter an option number next to the subsystem or job name, and then press Enter. You can select options for more than one subsystem or job at a time. The option numbers are listed in the panel of Example 8-1.
displays the names of Oracle subsystems and jobs.
is the type of entry for this line:
ASJ -
automatically started job
LIS -
listener
PJ -
pre started job waiting for start request
SBS -
subsystem
SRV -
server job
UNK -
unknown
is the status of the subsystem or job that is associated with the line.
For subsystems (type SBS
):
ACTV -
active subsystem
ERROR -
error while determining subsystem status
INACTV -
inactive subsystem
NOAUTH -
user has no authorization to start or stop subsystem
For jobs, refer to the Help information for the Status column of the WRKACTJOB panel.
is the OS/400 job number for job-related lines. It is blank for subsystem lines.
is the total number of auxiliary storage I/O operations that were performed.
is the current amount of auxiliary storage occupied by the job, in kilobytes (units of 1024 bytes).
is the total CPU time used, in seconds.
To display specific gateway subsystems and jobs, you must enter the subsystem into the Oracle gateway monitor subsystem by pressing PF2. This displays a panel where you can enter the name of the Oracle gateway subsystems. The panel in Example 8-1, "Monitor Oracle Subsystems" shows a single gateway subsystem with a single listener and two prestarted gateway jobs.
To work with a specific gateway subsystem or job, you must enter an option number in the Opt
field next to the subsystem or job that is displayed on the main panel.
In Example 8-1, "Monitor Oracle Subsystems", the Update frequency
field is set to *OFF
. This means that no updates of the information are displayed until you press PF5. If you want the screen to be updated periodically, then you need to press PF8. The default update refresh period is 30 seconds, but you can set it to a value between 30 seconds and 600 seconds.
If help is needed at any time, then pressing PF1 on any of the panels will display more information about a specific panel.
Entering a 2 in the Option field on the main panel allows you to start a gateway subsystem. ACTV
is displayed in the STATUS
field of a subsystem (SBS) when the gateway subsystem is started. To start the gateway subsystem, you must be authorized to use the STRSBS
command.
To stop the gateway subsystem or a job in a gateway subsystem, move the cursor to the Opt field that is next to the subsystem or job that you want to shut down, and enter a 4
. This ends the specified gateway subsystem or job. When the gateway subsystem has shut down, INACTV
is displayed in the field next to the subsystem or job that ended. To shut down the gateway, you must be authorized to use the ENDSBS
command.
To work with gateway jobs, specify option 5 next to the job. This displays the OS/400 Work with Job menu to work with a specific gateway job.
An instance of the Oracle Database Gateway for DB2/400 runs within an OS/400 subsystem. All jobs relating to that instance run within the subsystem. You use the STRSBS
command to start a subsystem. For example, to start an instance of a gateway called ORACLE
, you enter the following command:
STRSBS ORACLE/ORACLE
where the first ORACLE
is a library name, and the second ORACLE
is the name of a subsystem.
To stop a gateway, you must end the subsystem by using the ENDSBS
command. For example, to stop a gateway called ORACLE
, to immediately end all jobs within the ORACLE
subsystem, and to stop the subsystem itself, you enter the following command:
ENDSBS ORACLE OPTION(*IMMED)
Specify option 7 in the Opt field that is next to the gateway subsystem name if you want to start the gateway listener. The gateway listener is identified by LIS
in the TYPE
field that is displayed on the main menu.
Displaying the gateway call stack is not needed for most installations but might be requested by Oracle Support Services during problem isolation and determination. Entering option 10 in the Opt field displays a call stack for a specific job.
Specify option 11 in the Opt field next to a specific job in order to display and work with object locks that are held by the gateway. This option displays the OS/400 Work with Job Locks panel.
Specify option 14 in the Opt field next to a specific job to show the files that are open for that specific job. This option displays the OS/400 Display Open Files panel.
The Oracle user ID and password are passed over the database link to the gateway to authorize gateway users to DB2/400 objects. If the CONNECT TO
clause is specified when creating the database link, then the user ID and password that are sent to the gateway are those that are specified in this clause. If the CONNECT TO
clause is omitted from the database link specification, then the Oracle user ID and password of the task that is using the database link are passed to the gateway for authorization.
Caution:
You must keep the gateway recovery user ID and password synchronized with the OS/400 User Profile and password. The gateway recovery user ID and password must be valid to OS/400 at all times. If, when transaction recovery is being attempted by Oracle, the password for the recovery user ID is not valid or if the recovery user ID itself is invalid (it may have expired, for example), an ORA-1017 error (invalid user ID or password) will be inserted into the Oracle Database alert log. TheCHGRECOPRF
command is used to change the recovery profile parameters (including recovery user ID and password).
For more information about using CHGRECOPRF
, refer to "CHGRECOPRF, Change Recovery Profile Parameters".
The Oracle Database Gateway for DB2/400 and Heterogeneous Services now support replication to DB2/400 by using Oracle Streams. Oracle Streams is a rule-based process that allows changes to an Oracle Database table to be captured and applied to an equivalent DB2/400 table, based on user-written rules.
Note:
Replication from an Oracle Database to DB2/400 is supported. Replication from DB2/400 to an Oracle Database is not supported at this time.Before setting up your Streams replication environment, ensure that archivelog is enabled.
An example of a simple table replication follows:
First you should grant the necessary authorizations to your Streams admin user ID.
CONNECT SYS/SYS_PASSWORD AS SYSDBA GRANT CONNECT, RESOURCE, SELECT_CATALOG_ROLE TO strmadmin IDENTIFIED BY strmadminpw; GRANT EXECUTE ON DBMS_APPLY_ADM TO strmadmin; GRANT EXECUTE ON DBMS_AQADM TO strmadmin; GRANT EXECUTE ON DBMS_CAPTURE_ADM TO strmadmin; GRANT EXECUTE ON DBMS_FLASHBACK TO strmadmin; GRANT EXECUTE ON DBMS_PROPAGATION_ADM TO strmadmin; GRANT EXECUTE ON DBMS_STREAMS_ADM TO strmadmin; BEGIN DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE( privilege => DBMS_RULE_ADM.CREATE_RULE_SET_OBJ, grantee => 'strmadmin', grant_option => FALSE); END; / BEGIN DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE( privilege => DBMS_RULE_ADM.CREATE_RULE_OBJ, grantee => 'strmadmin', grant_option => FALSE); END; /
Then, set up the Streams queue and the database link that the apply process will use.
CONNECT strmadmin/strmadminpw EXEC DBMS_STREAMS_ADM.SET_UP_QUEUE(); DROP DATABASE LINK strmdblink.your.domain.com; CREATE DATABASE LINK strmdblink.your.domain.com CONNECT TO userid IDENTIFIED BY password USING 'tnsnames_entry';
Next, create the capture and apply processes and define the replication rules.
CONNECT SYS/SYS_PASSWORD AS SYSDBA ALTER SYSTEM ARCHIVE LOG CURRENT; CONNECT strmadmin/strmadminpw --- --------------------------------------------------------------- --- Stop the capture process if it's already active. --- --------------------------------------------------------------- BEGIN DBMS_CAPTURE_ADM.STOP_CAPTURE( capture_name => 'db2400_capture'); END; / --- --------------------------------------------------------------- --- Stop the apply process if it's already active. --- --------------------------------------------------------------- BEGIN DBMS_APPLY_ADM.STOP_APPLY( apply_name => 'apply_2_db2400'); END; / --- --------------------------------------------------------------- --- Define the capture rule, this one captures changes to scott.emp --- --------------------------------------------------------------- BEGIN DBMS_STREAMS_ADM.ADD_SCHEMA_RULES( schema_name => 'scott', streams_type => 'capture', streams_name => 'db2400_capture', queue_name => 'strmadmin.streams_queue', include_dml => true, include_ddl => true); END; / --- --------------------------------------------------------------- --- Set the capture instantiation level --- --------------------------------------------------------------- DECLARE iscn NUMBER; -- Variable to hold instantiation SCN value BEGIN iscn := DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER(); DBMS_APPLY_ADM.SET_TABLE_INSTANTIATION_SCN( source_object_name => 'scott.emp', source_database_name => 'ORAv92', instantiation_scn => iscn, apply_database_link => 'strmdblink.your.domain.com'); END; / --- --------------------------------------------------------------- --- Drop the apply process if it already exists. --- --------------------------------------------------------------- BEGIN DBMS_APPLY_ADM.DROP_APPLY( apply_name => 'apply_2_db2400'); END; / --- --------------------------------------------------------------- --- Create the apply process --- --------------------------------------------------------------- BEGIN DBMS_APPLY_ADM.CREATE_APPLY( queue_name => 'strmadmin.streams_queue', apply_name => 'apply_2_db2400', apply_database_link => 'strmdblink.your.domain.com', apply_captured => true); END; / --- --------------------------------------------------------------- --- Create the apply rule --- --------------------------------------------------------------- BEGIN DBMS_STREAMS_ADM.ADD_TABLE_RULES( table_name => 'scott.emp', streams_type => 'apply', streams_name => 'apply_2_db2400', queue_name => 'strmadmin.streams_queue', include_dml => true, include_ddl => false, source_database => 'ORAv92'); END; / --- --------------------------------------------------------------- --- Turn on tracing for the apply process (be careful, this --- generates alot of output). --- --------------------------------------------------------------- BEGIN DBMS_APPLY_ADM.SET_PARAMETER( apply_name => 'apply_2_db2400', parameter => 'trace_level', value => 127 ); END; / --- --------------------------------------------------------------- --- Turn off disable_on_error for the apply process --- --------------------------------------------------------------- BEGIN DBMS_APPLY_ADM.SET_PARAMETER( apply_name => 'apply_2_db2400', parameter => 'disable_on_error', value => 'n'); END; / --- --------------------------------------------------------------- --- Start the apply process. --- --------------------------------------------------------------- BEGIN DBMS_APPLY_ADM.START_APPLY( apply_name => 'apply_2_db2400'); END; / --- --------------------------------------------------------------- --- Start the capture process. --- --------------------------------------------------------------- BEGIN DBMS_CAPTURE_ADM.START_CAPTURE( capture_name => 'db2_capture'); END; /
For detailed information about Oracle streams replication, refer to Oracle Streams Concepts and Administration.