8 Administering the Gateway

Overview of the Oracle Gateway Monitor

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

Starting the Oracle Gateway Monitor

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

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

Opt

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.

Name

displays the names of Oracle subsystems and jobs.

Type

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

Status

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.

Job Number

is the OS/400 job number for job-related lines. It is blank for subsystem lines.

Aux I/O

is the total number of auxiliary storage I/O operations that were performed.

Aux KB

is the current amount of auxiliary storage occupied by the job, in kilobytes (units of 1024 bytes).

CPU Secs

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.

Starting and Stopping the Gateway

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.

STRSBS ORACLE/ORACLE

ENDSBS ORACLE OPTION(*IMMED)

Starting the TCP/IP Listener

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

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.

Working with Gateway Job Locks

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.

Displaying Open Files

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.

Gateway Security

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.

Streams Replication

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.

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.