9 Configuring the Gateway Using SNA Communication Protocol

There are three ways to establish the gateway-Oracle database relationship when you are installing or upgrading or migrating the gateway:

• When Oracle Database and the Gateway Are Installed in the Same ORACLE_HOME

• When Oracle Database and the Gateway Are Installed on Separate Systems

• When Oracle Database and the Gateway Are on the Same System but in Different Directories

Depending on the location of the gateway and the Oracle database, you might need to transfer some of the gateway administrative files to the location where Oracle database is installed.

Follow the instructions corresponding to your combination of the gateway-Oracle database locations listed below.

When Oracle Database and the Gateway Are Installed in the Same ORACLE_HOME

You do not need to transfer files. Proceed to Oracle Database Configuration: First-Time Gateway Installations.

When Oracle Database and the Gateway Are Installed on Separate Systems

You need to perform the following tasks if Oracle database and the gateway are installed on separate systems:

1. Locate the gateway administrative files in the gateway $ORACLE_HOME/dg4appc/admin directory. All files in this directory that have the .sql, .pkh, or .pkb suffixes must be copied into a similarly-named directory in the Oracle database Oracle home directory.

2. Now locate the gateway demo files and subdirectories in the $ORACLE_HOME/dg4appc/demo directory of the gateway. Copy the pgavsn.sql and pgaecho.sql files into a similarly named directory in Oracle database.

3. Copy the other subdirectories and files related to your installed OLTP on your remote host. For example, if you have CICS as your only OLTP, then copy the $ORACLE_HOME/dg4appc/demo/CICS gateway files into a similarly named directory in the Oracle database.

   Note:

   Before transferring the files from the $ORACLE_HOME/dg4appc/demo directory, ensure that you have generated your required TIPs. You need to transfer the TIPs as well.

   Refer to the Oracle Database Gateway for APPC User's Guide for information about generating TIPs using Procedural Gateway Administrative Utility (PGAU).

When Oracle Database and the Gateway Are on the Same System but in Different Directories

You must change your gateway Oracle home to the Oracle home directory of Oracle database.

If this is a first-time installation, proceed with Oracle Database Configuration: First-Time Gateway Installations.

If this is an upgrade, proceed with Upgrading or Migrating the Oracle Database from Previous Gateways.

Following those steps, you might want to perform the Optional Configuration Steps to Permit Multiple Users.

Follow these steps to configure your Oracle database if you have installed Oracle Database Gateway for APPC for the first time:

1. Ensure that the UTL_RAW PL/SQL package has been installed on your Oracle database. All PGAU-generated TIP specifications use UTL_RAW, which provides routines for manipulating raw data.

   1. Use SQL*Plus to connect to the Oracle database as the SYS user.

   2. From SQL*Plus, enter the following command:

      SQL> DESCRIBE UTL_RAW
      

      The DESCRIBE statement produces output on your screen. If you browse through the output, you should see some functions, including a compare function. If you do not see this output, then continue the UTL_RAW installation by performing step 1.d below.

      If the DESCRIBE statement indicates success, then your Oracle database has UTL_RAW installed and you can proceed to Step 2.

   3. Use SQL*Plus to connect to the Oracle database as the SYS user.

   4. From SQL*Plus, run the utlraw.sql and prvtrawb.plb scripts in the Oracle database $ORACLE_HOME/rdbms/admin directory, in the following order:

      SQL> @$ORACLE_HOME/rdbms/admin/utlraw.sql
      SQL> @$ORACLE_HOME/rdbms/admin/prvtrawb.plb
      

2. Ensure that the DBMS_OUTPUT standard PL/SQL package is enabled on Oracle database. The sample programs and installation verification programs on the distribution media use this standard package.

   1. If necessary, use SQL*Plus to connect to the Oracle database as the SYS user.

   2. From SQL*Plus, enter the following command:

      SQL> DESCRIBE DBMS_OUTPUT
      

   The DESCRIBE statement produces output on your screen. If you browse through that output, you should see some functions, including a put_line function.

   If you do not see this output, then you must create the DBMS_OUTPUT package. Refer to the Oracle Database PL/SQL Packages and Types Reference for more information about the DBMS_OUTPUT package. After successful installation of the DBMS_OUTPUT package, issue the DESCRIBE statement.

   If the DESCRIBE statement indicates success, then your Oracle database has DBMS_OUTPUT created, and you can proceed to Step 3.

3. Install the UTL_PG PL/SQL package. All PGAU-generated TIP specifications use UTL_PG, which provides routines for performing numeric conversions to and from raw data.

   1. If necessary, use SQL*Plus to connect to the Oracle database as the SYS user.

   2. From SQL*Plus, run the utlpg.sql and prvtpgb.plb scripts in the Oracle database $ORACLE_HOME/rdbms/admin directory, in the following order:

      SQL> @$ORACLE_HOME/rdbms/admin/utlpg.sql
      SQL> @$ORACLE_HOME/rdbms/admin/prvtpgb.plb
      

4. Install the Heterogeneous Services (HS) catalogs.

   1. If necessary, use SQL*Plus to connect to the Oracle database as the SYS user.

   2. Enter the following command:

      SQL> DESCRIBE HS_FDS_CLASS
      

      The DESCRIBE statement produces output on your screen. If the DESCRIBE statement indicates success, then heterogeneous services catalogs have been created on your Oracle database and you can proceed to Step 5, otherwise follow the next step only if the DESCRIBE statement does not indicate success. Step c creates the Heterogeneous Services catalog.

   3. If it is necessary to create the Heterogeneous Services catalog, enter the following command:

      SQL> $ORACLE_HOME/rdbms/admin/caths.sql
      

5. Create a public database link to access Oracle Database Gateway for APPC:

   Use SQL*Plus to connect to the Oracle database as the SYSTEM user. You can use the following SQL*Plus sample whether the Oracle database and the gateway are on the same system or on different systems. In the following sample, pgasrv is the tns_name_entry that will be assigned to the gateway when you modify the tnsnames.ora file later.

   SQL> CREATE PUBLIC DATABASE LINK PGA USING 'PGASRV'
   

6. Create the gateway administrator user PGAADMIN and install the PG DD.

   1. Use SQL*Plus to connect to the Oracle database as the SYSTEM user.

   2. From SQL*Plus, run the pgacr8au.sql script in the $ORACLE_HOME/dg4appc/admin directory. This script creates the PGAADMIN user ID.

      The initial password defined for PGAADMIN is PGAADMIN. Use the ALTER USER command to change the password. For more information about password issues, refer to the Oracle Database SQL Language Reference.

      SQL> @$ORACLE_HOME/dg4appc/admin/pgacr8au.sql
      

   3. Use SQL*Plus to connect to the Oracle database as user PGAADMIN.

   4. From SQL*Plus, run the pgddcr8.sql script in the $ORACLE_HOME/dg4appc/admin directory. This script installs the PG DD.

      SQL> @$ORACLE_HOME/dg4appc/admin/pgddcr8.sql
      

   5. From SQL*Plus, connect to the Oracle database as the SYS user.

   6. Grant execution privileges on DBMS_PIPE to PGAADMIN:

      SQL> GRANT EXECUTE ON DBMS_PIPE TO PGAADMIN
      

7. Install the TIP trace access PL/SQL routines. These routines require that the DBMS_PIPES standard PL/SQL package is installed and that PGAADMIN has execute privileges on it. For more information on DBMS_PIPES, refer to the Oracle Database PL/SQL Packages and Types Reference.

   1. If necessary, use SQL*Plus to connect to the Oracle database as user PGAADMIN.

   2. From SQL*Plus, run the pgatiptr.sql script in the $ORACLE_HOME/dg4appc/admin directory. This script creates PL/SQL routines that can be called to read and purge trace information created by PGAU-generated TIP specifications. It also creates public synonyms for these routines. The script prompts you for the necessary user IDs and passwords.

      SQL> @$ORACLE_HOME/dg4appc/admin/pgatiptr.sql
      

8. Install the GPGLOCAL package. This package is required for compilation and execution of all PGAU-generated TIP specifications. TIP developers should be granted execute privileges on GPGLOCAL (refer to Step configuring-appc-gateway-sna.htm#GUID-0DE22027-F985-4E80-982C-F5521271D288__I1012843 of "Optional Configuration Steps to Permit Multiple Users").

   1. Use SQL*Plus to connect to the Oracle database as user PGAADMIN.

   2. From SQL*Plus, run the gpglocal.pkh script in the $ORACLE_HOME/dg4appc/admin directory. This script compiles the GPGLOCAL package specification.

      SQL> @$ORACLE_HOME/dg4appc/admin/gpglocal.pkh
      

   3. From SQL*Plus, run the gpglocal.pkb script in the $ORACLE_HOME/dg4appc/admin directory. This script compiles the GPGLOCAL package body.

      SQL> @$ORACLE_HOME/dg4appc/admin/gpglocal.pkb
      

Follow these instructions only if you have a previous version of the Oracle Database Gateway for APPC installed on your system and need to configure it for 12c Release 2 (12.2) of the gateway.

Upgrade your Oracle Database Gateway for APPC to current version levels as follows:

You might have an older version of the gateway already installed. Be aware that although a version 10 gateway can communicate with a version 9 data dictionary (PGDD), a version 9 gateway cannot communicate with a version 10 data dictionary. Thus, if you upgrade your data dictionary to a version 10, no gateways which were configured with a version 9 data dictionary will be able to communicate with it.

The following configuration steps are optional. Perform these steps if you want to allow users other than PGAADMIN to perform PG DD operations using PGAU.

1. Create public synonyms for the PG DD to allow other users to access the tables:

   1. Use SQL*Plus to connect to the Oracle database as the SYSTEM user.

   2. From SQL*Plus, run the pgddcr8s.sql script in the $ORACLE_HOME/dg4appc/admin directory. This script creates public synonyms for the PG DD.

      SQL> @$ORACLE_HOME/dg4appc/admin/pgddcr8s.sql
      

2. Create roles for accessing the PG DD, performing definitions of transactions, and generating TIP specifications. The PGAADMIN user can grant these roles to other users as necessary.

   1. Use SQL*Plus to connect to the Oracle database as user PGAADMIN.

   2. From SQL*Plus, run the pgddcr8r.sql script in the $ORACLE_HOME/dg4appc/admin directory. This script creates two roles, PGDDDEF and PGDDGEN. The PGDDDEF role provides SELECT, INSERT, UPDATE, and DELETE privileges against some of the PG DD tables, and SELECT privileges against others, and allows execution of the PGAU DEFINE, GENERATE, REDEFINE, REPORT, and UNDEFINE statements. The PGDDGEN role provides select privileges against the PG DD tables, and allows execution of the PGAU GENERATE and REPORT statements only.

      SQL> @$ORACLE_HOME/dg4appc/admin/pgddcr8r.sql
      

3. Grant access to PGA required packages.

   TIP developers require access to the following PL/SQL packages, which are shipped with the Oracle database:

   • DBMS_PIPE in the $ORACLE_HOME/rdbms/admin directory

   • UTL_RAW in the $ORACLE_HOME/rdbms/admin directory

   • UTL_PG in the $ORACLE_HOME/rdbms/admin directory

   Explicit grants to execute these packages must be made to TIP developers.

   These grants can be private as in the following example:

   $ sqlplus SYS/pw@database_specification_string 
   SQL> GRANT EXECUTE ON UTL_RAW TO tip_developer; 
   SQL> GRANT EXECUTE ON UTL_PG TO tip_developer; 
   SQL> GRANT EXECUTE ON DBMS_PIPE TO tip_developer; 
   SQL> CONNECT PGAADMIN/pw@database_specification_string 
   SQL> GRANT EXECUTE ON PGAADMIN.PURGE_TRACE TO tip_developer; 
   SQL> GRANT EXECUTE ON PGAADMIN.READ_TRACE TO tip_developer; 
   SQL> GRANT EXECUTE ON PGAADMIN.GPGLOCAL TO tip_developer; 
   SQL> exit
   

   Alternatively, these grants can be public as in the following example:

   $ sqlplus SYS/pw@database_specification_string 
   SQL> GRANT EXECUTE ON UTL_RAW TO PUBLIC;
   SQL> GRANT EXECUTE ON UTL_PG TO PUBLIC;
   SQL> GRANT EXECUTE ON DBMS_PIPE to PUBLIC;
   SQL> CONNECT PGAADMIN/pw@database_specification_string 
   SQL> GRANT EXECUTE ON PGAADMIN.PURGE_TRACE TO PUBLIC;
   SQL> GRANT EXECUTE ON PGAADMIN.READ_TRACE TO PUBLIC;
   SQL> GRANT EXECUTE ON PGAADMIN.GPGLOCAL TO PUBLIC;
   SQL> EXIT
   

   You can use either private or public grants. Both are sufficient for using PGA. Public grants are easier and can be performed now. If you use private grants, then they must be issued each time a new TIP developer user ID is created.

   SQL scripts for performing these grants are provided in the $ORACLE_HOME/dg4appc/admin directory. The pgddapub.sql script performs these grants for public access to the packages. The pgddadev.sql script performs the grants for private access to the packages by a single TIP developer. If you are going to use private grants, then you must run the pgddadev.sql script once for each TIP developer user ID:

   1. Use SQL*Plus to connect to the Oracle database as user PGAADMIN.

   2. From SQL*Plus, run the appropriate script (pgddapub.sql or pgddadev.sql) from the $ORACLE_HOME/dg4appc/admin directory. The script performs the necessary grants as described earlier. You are prompted for the required user IDs, passwords, and database specification strings.

      If you are using private grants, then repeat this step for each user ID requiring access to the packages.

      SQL> @$ORACLE_HOME/dg4appc/admin/pgddapub.sql
      SQL> @$ORACLE_HOME/dg4appc/admin/pgddadev.sql
      

4. If you are upgrading from a previous release of the gateway, and if you want to upgrade your existing TIPs with new function and maintenance, then regenerate existing TIP specifications using the PGAU GENERATE statement.

   Note:

   The PGAU has been enhanced to automatically upgrade existing PG DD entries with a new attribute when a PGAU GENERATE command is executed. To support this enhancement, add a new privilege to the PGDDGEN role. To do this, as the PGAADMIN user, use SQL*Plus to connect to the Oracle database where the PG DD is stored. Then issue the following SQL command:

   SQL> GRANT INSERT ON PGA_DATA_VALUES TO PGDDGEN
   

   1. Invoke PGAU in the directory path where the PGAU control files are generated and where TIPs are stored:

      $ pgau
      PGAU> CONNECT PGAADMIN/pgaadmin@database_specification_string 
      PGAU> GENERATE tranname 
      PGAU> EXIT
      

   For more information about the GENERATE command, refer to the PGAU GENERATE command section in Chapter 2, of the Oracle Database Gateway for APPC User's Guide.

   Note that it is not necessary to define the PG DD entries again.

5. Invoke SQL*Plus in the same directory path where the newly-generated TIP specifications are stored.

   $ sqlplus tip_owner/pw@database_specification_string 
   SQL> @tipname.pkh 
   SQL> @tipname.pkb
   SQL> exit
   

   PGAU GENERATE produces the TIP in two output files: a specification and a body. You must compile both, first the specification and then the body.

   For more information about the GENERATE command, refer to the PGAU GENERATE command section in Chapter 2, of the Oracle Database Gateway for APPC User's Guide.

To configure the gateway, perform the following steps:

Follow these steps to configure the commit-confirm components. The steps for configuring commit-confirm include:

• Configuring the Oracle database where the gateway server will store its transaction log information

• Configuring the gateway initialization parameters

• Configuring the OLTP

All of these steps must be performed before attempting to use any applications that use commit-confirm.

To verify the gateway installation and the OLTP configuration, perform the following procedures after installing Oracle Database Gateway for APPC. In addition, if you chose to configure commit-confirm, follow the steps to verify the OLTP configuration for commit-confirm.

The following are optional steps that you can perform as necessary. Installation of the sample applications for your OLTP is recommended to help you to fully understand how the gateway works and how it interfaces with your OLTP.