4 Configuring Oracle Database Gateway for VSAM

Choose a System Identifier for the Gateway

Choosing a system identifier is the first task in configuring Oracle Database Gateway for VSAM. The gateway system identifier (SID) is an alphanumeric character string that identifies a gateway instance. If you want to connect simultaneously to different VSAM data sources, you need one gateway instance, and therefore one gateway SID, for each VSAM data source. The SID name is used as part of the file name for the initialization parameter file. The default SID is dg4vsam.

The HS_FDS_DATASOURCE_NAME parameter takes on the gateway SID as the default value. This means that the data source matches the gateway SID by default. However, you can specify the value of the HS_FDS_DATASOURCE_NAME parameter to match the data source that you want to access. See "HS_FDS_DATASOURCE_NAME" for more information.

If you have one VSAM data source and want to access it sometimes with one set of gateway parameter settings, and other times with a different set of gateway parameter settings, you can define two different data sources and define the matching SIDs accordingly.

Customize the Initialization Parameter File

Customizing the initialization parameter file is the second task in configuring Oracle Database Gateway for VSAM. The initialization parameter file must be available when the gateway is started. During installation, the following default initialization parameter file is created:

%ORACLE_HOME%\dg4vsam\admin\initdg4vsam.ora

Where %ORACLE_HOME% is the directory in which the gateway is installed.

The name of the parameter file is init[SID].ora, where SID is the gateway SID. If you are not using dg4vsam as the gateway SID, you must rename the initialization parameter file using the SID you chose in Task 1. This default initialization parameter file is sufficient for starting the gateway, verifying a successful installation, and running the demonstration scripts.

In the initialization parameter file, specify the VSAM connection as follows:

• If you use the default workspace:

  HS_FDS_CONNECT_INFO="address:port"
  

• If you use a workspace other than the default:

  HS_FDS_CONNECT_INFO="address='host' port='port' workspace='workspace'"
  

Where:

• host: The name of the mainframe computer on which Oracle Connect for IMS, VSAM, and Adabas Gateways runs.

• port: The port number to listen to.

• workspace: The VSAM data source workspace name (optional).

HS_FDS_ACCESS_METHOD must be set to VSAM-CICS for VSAM (CICS) data sources. See Appendix A, "Initialization Parameters" for details.

A number of initialization parameters can be used to modify the gateway behavior. You may want to change the initialization parameter file later to meet system requirements.

Configure Oracle Net Services Oracle Net Listener for the Gateway

Oracle Net Services uses the Oracle Net Listener to receive incoming connections from a Oracle Net Services client. The Oracle Net Listener and the gateway must reside on the same system.

The Oracle Net Listener listens for incoming requests from the Oracle Database. For the Oracle Net Listener to listen for the gateway, information about the gateway must be added to the Oracle Net Listener configuration file, listener.ora. This file is located in the following directory:

%ORACLE_HOME%\network\admin

where %ORACLE_HOME% is the directory in which the gateway is installed.

The following entries must be added to the listener.ora file:

• A list of Oracle Net Services addresses on which the Oracle Net Listener listens.

• The gateway that the Oracle Net Listener starts in response to incoming connection requests.

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = host_name)(PORT = 11201))
    )
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
    )
  )

SID_LIST_LISTENER =
  (SID_LIST =
  (SID_DESC=
     (ORACLE_HOME= D:\ORACLE_HOME\product\12.1\Db_1)
     (SID_NAME=vsam)     (PROGRAM=D:\ORACLE_HOME\product\12.1\Db_1\bin\dg4vsam.exe)
  )
)

Stop and Start the Oracle Net Listener for the Gateway

The Oracle Net Listener must be started to initiate the new settings, as follows:

1. Set the PATH environment variable to access the commands in the following directory:

   %ORACLE_HOME%\bin
   

   Run the following command:

   set PATH=%ORACLE_HOME%\bin
   

   where %ORACLE_HOME% is the directory where the gateway is installed.

2. If the listener is already running, use the lsnrctl command to stop the listener and then start it with the new settings, as follows:

   C:\ lsnrctl stop
   C:\ lsnrctl start
   

3. Check the status of the listener with the new settings, as follows:

   C:\ lsnrctl status
   

   The following is an example of output from a lsnrctl status check:

LSNRCTL for 32-bit Windows: Version 11.2.0 - Production on 05-JUL-2009 11:47
:55
Copyright (c) 1991, 2009, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=oracledev1.attunity.loca
l)(PORT=11201)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for 64-bit Windows: Version 11.2.0 - Production
Start Date 05-JUL-2009 11:47:50
Uptime 0 days 0 hr. 0 min. 8 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File C:\oracle\product\11.2.0\NETWORK\ADMIN\listener.ora
Listener Log File C:\oracle\product\11.2.0
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=204.179.99.15)(PORT=11201)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\.\pipe\EXTPROC0ipc)))
Services Summary...
Service "dg4vsam" has 1 instance(s).
Instance "dg4vsam", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

In this example, dg4vsam is the default SID value assigned during installation. You can use any valid ID for the SID, or keep the default.

Configuring Oracle Net Services for the Oracle Database

To configure the server you add connect descriptors to the tnsnames.ora file. You cannot use the Oracle Net Services Assistant or the Oracle Net Services Easy Config tools to configure the tnsnames.ora file. You must edit the file manually.

For the Oracle Database to access the gateway, it needs a service name entry or a connect descriptor name entry in the tnsnames.ora file to tell the Oracle Database where to make connections.

By default, this file is located in the directory %ORACLE_HOME%/network/admin, where %ORACLE_HOME% is the directory in which the Oracle Database is installed. The tnsnames.ora file is required by the Oracle Database accessing the gateway, but not by the gateway.

connect_descriptor =
   (DESCRIPTION =
   (ADDRESS_LIST =
   (ADDRESS = (PROTOCOL = TCP)(HOST = host_name)(PORT = port_number))
   )
   (CONNECT_DATA =
   (SID=gateway_sid)
   )
   (HS = OK)
   )

connect_descriptor=
vsam =
  (DESCRIPTION =
  (ADDRESS_LIST =
  (ADDRESS = (PROTOCOL = TCP)(HOST = host_name)(PORT = port_number))
  )
  (CONNECT_DATA =
  (SID = gateway_sid)
  )
  (HS = OK)
  )

Multiple Databases Example: Configuring the Gateway

Choose Two System IDs for Each VSAM Database

A separate instance of the gateway accesses the different VSAM data sources. Each instance needs its own gateway System ID (SID). For this example, the gateway SIDs for the instances that access the VSAM data sources are named as follows:

• dg4vsam2 for the gateway accessing data source dg4vsam2.

• dg4vsam3 for the gateway accessing data source dg4vsam3.

Create Two Initialization Parameter Files

Create an initialization parameter file for each instance of the gateway by copying the original initialization parameter file twice, naming one with the gateway SID for dg4vsam2 and the other with the gateway SID for dg4vsam3:

$ cd %ORACLE_HOME%\dg4vsam\admin
$ copy initdg4vsam.ora initdg4vsam2.ora
$ copy initdg4vsam.ora initdg4vsam3.ora

Change the value of the HS_FDS_CONNECT_INFO parameter in the new files.

For initdg4vsam2.ora, enter the following:

HS_FDS_CONNECT_INFO="mvs5:2551"

For initdg4vsam3.ora, enter the following:

HS_FDS_CONNECT_INFO="mvs5:2551"

Multiple Databases Example: Configuring Oracle Net Services Listener

Add Entries to listener.ora

Add two new entries to the Oracle Net Listener configuration file, listener.ora. You must have an entry for each gateway instance, even when multiple gateway instances access the same database.

The following example shows the entry for the original installed gateway first, followed by the new entries:

SID_LIST_LISTENER =
  (SID_LIST =
  (SID_DESC=
     (ORACLE_HOME=ORACLE_HOME_directory\product\12.1\Db_1)
     (SID_NAME=dg4vsam)
     (PROGRAM=ORACLE_HOME_directory\product\12.1\Db_1\bin\dg4vsam.exe)
  )
  (SID_DESC=
     (ORACLE_HOME=ORACLE_HOME_directory\product\12.1\Db_1)
     (SID_NAME=dg4vsam2)
     (PROGRAM=ORACLE_HOME_directory\product\12.1\Db_1\bin\dg4vsam.exe)
  )
  (SID_DESC=
     (ORACLE_HOME=ORACLE_HOME_directory\product\12.1\Db_1)
     (SID_NAME=dg4vsam3)
     (PROGRAM=ORACLE_HOME_directory\product\12.1\Db_1\bin\dg4vsam.exe)
  )
)

Multiple Databases Example: Stopping and Starting the Oracle Net Listener

If the listener is already running, use the lsnrctl command to stop the listener and then start it with the new settings, as follows:

C:\ lsnrctl stop
C:\ lsnrctl start

Multiple Databases Example: Configuring the Oracle Database for Gateway Access

This section describes the required procedures to configure the Oracle Database for the gateway access.

old_db_using=(DESCRIPTION=
              (ADDRESS=
                (PROTOCOL=TCP)
                (PORT=1541)
                (HOST=gtwhost))
                (CONNECT_DATA=
                    (SID=dg4vsam))
               (HS=OK))
new_vsam2_using=(DESCRIPTION=
              (ADDRESS=
                (PROTOCOL=TCP)
                (PORT=1541)
                (HOST=gtwhost))
                (CONNECT_DATA=
                    (SID=dg4vsam2))
                (HS=OK))
new_vsam3_using=(DESCRIPTION=
              (ADDRESS=
                (PROTOCOL=TCP)
                (PORT=1541)
                (HOST=gtwhost))
                (CONNECT_DATA=
                    (SID=dg4vsam3))
                (HS=OK)) 

Multiple Databases Example: Accessing VSAM Data

Enter the following to create a database link for the dg4vsam2 gateway:

SQL> CREATE PUBLIC DATABASE LINK VSAM2 CONNECT TO
  2  user2 IDENTIFIED BY password2 USING 'new_vsam2_using';

Enter the following to create a database link for the dg4vsam3 gateway:

SQL> CREATE PUBLIC DATABASE LINK VSAM3 CONNECT TO
  2  user3 IDENTIFIED BY password3 USING 'new_vsam3_using';

After the database links are established you can query the new VSAM data sources, as in the following:

SQL> SELECT * FROM ALL_TABLES@VSAM2;

or:

SQL> SELECT * FROM ALL_TABLES@VSAM3;

Configuring for Global Transactions

The VSAM/Batch data source supports auto-commit mode only. This means that you cannot roll back, or undo, any changes that you made.

The gateway supports the following transaction capabilities:

• READ_ONLY

• SINGLE_SITE (default)

See the Oracle Database Heterogeneous Connectivity User's Guide for configuration information.

By default, the VSAM (CICS) data source supports global transactions.

If the VSAM (CICS) data source is configured differently in Oracle Studio for IMS, VSAM, and Adabas Gateways, see the Oracle Database Heterogeneous Connectivity User's Guide for information about configuring for global transactions.

If the VSAM (CICS) data source is configured for global transactions, the gateway becomes the commit point site when the VSAM (CICS) data source is updated by a transaction. The Oracle database commits the unit of work in the VSAM (CICS) data source after verifying that all Oracle databases in the transaction have successfully prepared the transaction. Only one gateway can participate in an Oracle global transaction as the commit point site.

Enabling Automatic Recovery

For the gateway to recover distributed transactions, a recovery account and password must be provided. By default, both the user name of the account and the password are RECOVER. The name of the account can be changed with the gateway initialization parameter HS_FDS_RECOVERY_ACCOUNT. The account password can be changed with the gateway initialization parameter HS_FDS_RECOVERY_PWD.

To change the default user name or the password, in the initialization parameter file, set the following gateway initialization parameters:

• HS_FDS_RECOVERY_ACCOUNT to the mainframe account name.

  This account can be the same account that is used to access the VSAM data source. If you use another account, then it must have the same permissions as the account that is used to access the VSAM data source.

• HS_FDS_RECOVERY_PWD to the password of the account.

  See Also:

  "Customize the Initialization Parameter File" for information about editing the initialization parameter file and Appendix A, "Initialization Parameters" for information about the HS_FDS_RECOVERY_ACCOUNT and HS_FDS_RECOVERY_PWD initialization parameters.

Sample Log File

Log files are used for troubleshooting and error handling. The log file is generated when the driverTrace debug binding parameter is set to True.

The log file includes various information concerning the functions used or called by the driver, queries executed, data sources accessed, etc.

First, you need to create the log file. Perform the following procedure.

1. Open Oracle Studio for IMS, VSAM, and Adabas Gateways.

2. From the main menu, click Windows, Preferences.

   The Preferences screen is displayed.

   Figure 4-2 Studio Preferences

   
   Description of "Figure 4-2 Studio Preferences"
   
   

3. In the left pane, click the Studio node.

4. Click the Advanced tab.

5. Select the Show advanced environment parameters check box.

6. Click OK.

7. In the Design Perspective Configuration view, right-click the binding under which the Oracle data source is located and select Edit Binding.

8. From the Category list, select Debug.

9. Set the driverTrace and generalTrace parameters to TRUE.

   Figure 4-3 Binding Properties

   
   Description of "Figure 4-3 Binding Properties"
   
   

10. Execute the following query: Select * from upd_dt limit to 3 rows

The following is a sample log file output:

ORACLE CONNECT Log (V10.1.3.1, MVS) Started at 2006-11-21T17:25:13 
Licensed by Oracle Corporation on 28-SEP-2006 (001001610) 
Licensed to ORACLE for <all providers> on <all machines> (<all platforms>
o: 586 9862924 fopen >>> filename=TEST.OR5000A.DEF.NOSBB.SYS,mode=rb, typ
e=record, lrecl=8208 
o: 275 0 flocate >>> stream=9862924,key=98A9830,key_len=4,options=3 
o: 292 8208 fread >>> buffer=98AA310, size=1, count=8208,stream=9862924 
o: 424 0 fclose >>> stream=9862924 
o: 275 0 flocate >>> stream=98613F4,key=984FFD0,key_len=100,options=5 
o: 292 2048 fread >>> buffer=984F7C8, size=1, count=2048,stream=98613F4 
o: 275 0 flocate >>> stream=9861024,key=984FFD0,key_len=4,options=5 
o: 292 2048 fread >>> buffer=984F7C8, size=1, count=2048,stream=9861024 
o: 275 0 flocate >>> stream=98613F4,key=984FFD0,key_len=100,options=5 
o: 292 2048 fread >>> buffer=984F7C8, size=1, count=2048,stream=98613F4 
o: 275 0 flocate >>> stream=9861024,key=984FFD0,key_len=4,options=5 
o: 292 2048 fread >>> buffer=984F7C8, size=1, count=2048,stream=9861024 
o: 275 0 flocate >>> stream=98613F4,key=984FFD0,key_len=100,options=5 
o: 292 2048 fread >>> buffer=984F7C8, size=1, count=2048,stream=98613F4 
o: 275 0 flocate >>> stream=9861024,key=984FFD0,key_len=4,options=5 
o: 292 2048 fread >>> buffer=984F7C8, size=1, count=2048,stream=9861024 
o: 586 98B7424 fopen >>> filename=TEST.OR5000A.DEF.NOSBB.SYS,mode=rb, typ
e=record, lrecl=8208 
o: 275 0 flocate >>> stream=98B7424,key=98C2820,key_len=4,options=3 
o: 292 8208 fread >>> buffer=98C3300, size=1, count=8208,stream=98B7424 
o: 424 0 fclose >>> stream=98B7424 
o: 275 0 flocate >>> stream=98613F4,key=984FFD0,key_len=100,options=5 
o: 292 2048 fread >>> buffer=984F7C8, size=1, count=2048,stream=98613F4 
o: 275 0 flocate >>> stream=9861024,key=984FFD0,key_len=4,options=5 
o: 292 2048 fread >>> buffer=984F7C8, size=1, count=2048,stream=9861024 
IRprc (6695): ; Client 194.90.22.131:3130 (No. 1, NAVXML CLIENT) has connected
o: 275 0 flocate >>> stream=98613F4,key=984FFD0,key_len=100,options=5 
o: 292 2048 fread >>> buffer=984F7C8, size=1, count=2048,stream=98613F4 
o: 275 0 flocate >>> stream=9861024,key=984FFD0,key_len=4,options=5 
o: 292 2048 fread >>> buffer=984F7C8, size=1, count=2048,stream=9861024 
o: 586 98B7424 fopen >>> filename=TEST.OR5000A.DEF.NOSBB.SYS,mode=rb, typ 
e=record, lrecl=8208 
o: 275 0 flocate >>> stream=98B7424,key=98FEA80,key_len=4,options=3 
o: 292 8208 fread >>> buffer=9901040, size=1, count=8208,stream=98B7424 
o: 424 0 fclose >>> stream=98B7424 
o: 275 0 flocate >>> stream=98613F4,key=984FFD0,key_len=100,options=5 
o: 292 2048 fread >>> buffer=984F7C8, size=1, count=2048,stream=98613F4 
o: 275 0 flocate >>> stream=9861024,key=984FFD0,key_len=4,options=5 
o: 292 2048 fread >>> buffer=984F7C8, size=1, count=2048,stream=9861024 
nvOUT (PROD.AC10131.NAV(QP#SQTXT) 89): SELECT * FROM VSAM:UPD_DT LIMIT TO 100 R
nvRETURN (PROD.AC10131.NAV(QPSYNON) 1140): -1 
o: 586 98B7424 fopen >>> filename=TEST.OR5000A.DEF.NOS.VSAM,mode=rb, type 
=record, lrecl=2048 
o: 393 0 fclose >>> stream=98613F4 
o: 424 0 fclose >>> stream=9861024 
o: 586 Calling ENQ for TEST.OR5000A.DEF.NOS.SYS . 
o: 586 Successful exclusive enqueue retCode=0 
o: 586 98B77F4 fopen >>> filename=TEST.OR5000A.DEF.NOS.SYS,mode=rb+, type 
=record 
o: 627 98B7BC4 fopen >>> filename=TEST.OR5000A.DEF.NOS.SYS.I2.PATH,mode=r 
b, type=record, lrecl=2048 
o: 275 0 flocate >>> stream=98B7BC4,key=9981F00,key_len=100,options=5 
o: 292 2048 fread >>> buffer=99816F8, size=1, count=2048,stream=98B7BC4 
o: 393 0 fclose >>> stream=98B7BC4 
o: 424 0 fclose >>> stream=98B77F4 
o: 424 Calling DEQ for TEST.OR5000A.DEF.NOS.SYS 
o: 424 Successful dequeue retCode=0 
o: 627 98B77F4 fopen >>> filename=TEST.OR5000A.DEF.NOS.VSAM.I2.PATH,mode= 
rb, type=record, lrecl=2048 
o: 275 0 flocate >>> stream=98B77F4,key=98BF0C0,key_len=100,options=5 
o: 292 2048 fread >>> buffer=98BE8B8, size=1, count=2048,stream=98B77F4 
o: 275 0 flocate >>> stream=98B7424,key=98BF0C0,key_len=4,options=5 
o: 292 2048 fread >>> buffer=98BE8B8, size=1, count=2048,stream=98B7424 
o: 586 98B8BF4 fopen >>> filename=TEST.OR5000A.DEF.NOSBB.VSAM,mode=rb, ty 
pe=record, lrecl=8208 
o: 275 0 flocate >>> stream=98B8BF4,key=9982850,key_len=4,options=3 
o: 292 8208 fread >>> buffer=9983330, size=1, count=8208,stream=98B8BF4 
o: 424 0 fclose >>> stream=98B8BF4 
<<<<<<<<<<<<<<<<<<< Execution Strategy Begin <<<<<<<<<<<<<<<<<<<<<<<<<<<< 
Original SQL: 
SELECT * FROM VSAM : UPD_DT LIMIT TO 100 ROWS 
Accessing file 'UPD_DT' from 'VSAM' Database 
Fetch strategy: scan 
>>>>>>>>>>>>>>>>>>>> Execution Strategy End >>>>>>>>>>>>>>>>>>>>>>>>>>>> 
FilePool Init(DB=VSAM) 
FilePool Get(DB=VSAM, File=TEST.VSAM.DATA.UPDDT015, Mode=0, FilePool Size=0)
o: 586 98B8BF4 fopen >>> filename=TEST.VSAM.DATA.UPDDT015,mode=rb, type=r 
ecord, lrecl=116 
o: 292 116 fread >>> buffer=9984628, size=1, count=116,stream=98B8BF4 
o: 292 116 fread >>> buffer=9984628, size=1, count=116,stream=98B8BF4 
o: 292 0 fread >>> buffer=9984628, size=1, count=116,stream=98B8BF4