7 Configuring Oracle Database Gateway for IMS

After installing the gateway, perform the following tasks to configure the gateway for IMS:

Configuring the Gateway
Configuring Oracle Net Services Listener for the Gateway
Configuring the Oracle Database for Gateway Access
Creating Database Links
Gateway Password Encryption Tool
Configuring the Gateway for Multiple IMS Databases
Performing Configuration Tasks
Testing the IMS Data Source

Configuring the Gateway

Perform the following tasks to configure Oracle Database Gateway for IMS:

Choose a System Identifier for the Gateway
Customize the Initialization Parameter File

Choose a System Identifier for the Gateway

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

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 IMS data source and want to access it sometimes with one set of gateway parameter settings, and other times with different gateway parameter settings, you can do that by defining two different data sources and defining the matching SID s accordingly.

Customize the Initialization Parameter File

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

%ORACLE_HOME%\dg4ims\admin\initdg4ims.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 dg4ims 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 IMS connection as follows:

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 IMS data source workspace name. (optional except for the IMS/DB DLI data source)

HS_FDS_ACCESS_METHOD must be set to IMS-DBCTL for IMS/DB DBCTL data sources and to IMS/DBDC for the IMS/DB DBDC 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.

See Also:

The Oracle Database Heterogeneous Connectivity Administrator's Guide for more information about customizing the initialization parameter file.

Configuring Oracle Net Services Listener for the Gateway

The gateway requires Oracle Net Services to provide transparent data access. After configuring the gateway, configure Oracle Net Services to work with the gateway.

Configure Oracle Net Services Oracle Net Listener for the Gateway
Stop and Start the Oracle Net Listener for the Gateway

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

Note:

If Oracle Net Services is reinstalled, the original listener.ora file is renamed and a new listener.ora file is put into the same directory.

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.

Example of Address to Listen On in listener.ora File

The Oracle Database accesses the gateway using Oracle Net Services and the TCP/IP protocol adapter. The following is the syntax of the connect descriptor entry in the listener.ora file:

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

Where:

Table 7-1 Parameters for listener.ora File

Variable	Description
`host_name`	is the name of the system on which the gateway is installed.
`port`	specifies the port number used by the Oracle Net Listener. If you have other listeners running on `host_name`, the value of `port_number` must be different from the other listeners' port numbers.

Example of Gateway to Start in listener.ora File

To direct the Oracle Net Listener to start the gateway in response to incoming connection requests, add an entry to the listener.ora file as follows.

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

Note:

The executable dg4ims.exe is valid for IMS, IMS CICS, and IMS/TM.

Where:

Table 7-2 Parameters for listener.ora File

Variable	Description
`sid_name`	The SID of the gateway and matches the gateway SID specified in the connect descriptor entry in the `tnsnames.ora` file. The Oracle Net Listener uses the SID name to locate the relevant `<sid_name>.ora` file. It needs to access this file to check that access method is used (IMS-BATCH, IMS-DBCTL, or IMS-DBDC).
`ORACLE_HOME`	The Oracle home directory where the gateway is located.

See Also:

Oracle Net Services Administrator's Guide for information about changing the listener.ora file.

Stop and Start the Oracle Net Listener for the Gateway

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

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.
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 
```
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 32-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 "dg4ims" has 1 instance(s).
Instance "dg4ims", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

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

Note:

You must use the same SID value in the tnsnames.ora file, and the listener.ora file.

Configuring the Oracle Database for Gateway Access

Before you can use the gateway to access IMS data, you must configure the Oracle Database to enable communication with the gateway over Oracle Net Services.

Perform the following tasks to configure the Oracle Database accordingly:

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.

See Also:

Oracle Database Administrator's Guide for information about editing the tnsnames.ora file.

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 in %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.

Configuring tnsnames.ora

Edit the tnsnames.ora file to add a connect descriptor for the gateway. The following is an example of the Oracle Net Services entries using TCP/IP protocol needed for the Oracle Database to access the gateway:

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

Where:

Table 7-3 Parameters for tnsnames.ora File

Variable	Description
`connect_descriptor`	The description of the object to connect to as specified when creating the database link, such as dg4ims. Check the `sqlnet.ora` file for the following parameter setting: `names.directory_path = (TNSNAMES)` Note: The `sqlnet.ora` file is typically stored in `%ORACLE_HOME%\network\admin`.
`host_name`	Specifies the system where the gateway is running.
`port_number`	Matches the port number used by the Oracle Net Services Oracle Net Listener that is listening for the gateway. The Oracle Net Listener's port number can be found in the `listener.ora` file used by the Oracle Net Listener. See "Example of Address to Listen On in listener.ora File".
`gateway_sid`	Specifies the SID of the gateway and matches the SID specified in the `listener.ora` file of the Oracle Net Listener that is listening for the gateway. See "Configure Oracle Net Services Oracle Net Listener for the Gateway" for more information.

For example:

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

Creating Database Links

Any Oracle client connected to the Oracle Database can access IMS data through the gateway. The Oracle client and the Oracle Database can reside on different systems. The gateway accepts connections only from the Oracle Database.

A connection to the gateway is established through a database link when it is first used in an Oracle session. In this context, a connection refers to the connection between the Oracle Database and the gateway. The connection remains established until the Oracle session ends. Another session or user can access the same database link and get a distinct connection to the gateway and IMS database.

Database links are active for the duration of a gateway session. If you want to close a database link during a session, you can do so with the ALTER SESSION CLOSE DATABASE LINK dblink statement. The database and application administrators of a distributed database system are responsible for managing the necessary database links that define paths to the IMS database.

See Also:

Oracle Database Administrator's Guide and Oracle Database Heterogeneous Connectivity User's Guide for more information about using database links.

Gateway Password Encryption Tool

The gateway uses userids and passwords to access the information in the remote database. Some userids and passwords must be defined in the Gateway Initialization File to handle functions such as resource recovery. In the current security conscious environment, having plain-text passwords that are accessible in the Initialization File is considered insecure. The dg4pwd encryption utility has been added as part of Heterogeneous Services' generic connectivity to help make this more secure. This utility is accessible by this gateway. The initialization parameters that contain sensitive values can be stored in an encrypted form.

See Also:

Oracle Database Heterogeneous Connectivity User's Guide for more information about using this utility.

Configuring the Gateway for Multiple IMS Databases

The tasks for configuring the gateway to access multiple IMS databases are similar to the tasks for configuring the gateway for a single database. The configuration example assumes the following:

The gateway is installed and configured with the default SID of dg4ims.
The ORACLE_HOME environment variable is set to the directory where the gateway is installed.
The gateway is configured for one IMS database named dg4ims.
Two IMS databases named dg4ims2 and dg4ims3 on a server named mvs5 with port number 2551 are added.

Configuring the gateway for additional IMS databases is similar to configuring it for one database, and involves the following:

Configuring the gateway.
Configuring Oracle Net Services for the gateway and the Oracle Database.

Multiple Databases Example: Configuring the Gateway

Choose Two System IDs for Each IMS Database

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

dg4ims2 for the gateway accessing data source dg4ims2.
dg4ims3 for the gateway accessing data source dg4ims3.

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 dg4ims2 and the other with the gateway SID for dg4ims3:

$ cd %ORACLE_HOME%\dg4ims\admin
$ copy initdg4vsam.ora initdg4ims2.ora
$ copy initdg4vsam.ora initdg4ims3.ora

Note:

The library %ORACLE_HOME%\dg4ims\admin is valid both for IMS, IMS CICS, and IMS/TM.

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

For initdg4ims2.ora, enter the following:

HS_FDS_CONNECT_INFO="mvs5:2551"

For initdg4ims3.ora, enter the following:

HS_FDS_CONNECT_INFO="mvs5:2551"

Note:

If you have multiple gateway SIDs for the same IMS database because you want to use different gateway parameter settings at different times, follow the same procedure. You create several initialization parameter files, each with different SIDs and different parameter settings.

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=dg4ims)
     (PROGRAM=ORACLE_HOME_directory\product\12.1\Db_1\bin\dg4ims.exe)
  )
  (SID_DESC=
     (ORACLE_HOME=ORACLE_HOME_directory\product\12.1\Db_1)
     (SID_NAME=dg4ims2)
     (PROGRAM=ORACLE_HOME_directory\product\12.1\Db_1\bin\dg4ims.exe)
  )
  (SID_DESC=
     (ORACLE_HOME=ORACLE_HOME_directory\product\12.1\Db_1)
     (SID_NAME=dg4ims3)
     (PROGRAM=ORACLE_HOME_directory\product\12.1\Db_1\bin\dg4ims.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.

Configuring Oracle Net Services on the Oracle Database for Multiple Gateway Instances

Add two connect descriptor entries to the tnsnames.ora file. You must have an entry for each gateway instance, even if the gateway instances access the same database.

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

old_db_using=(DESCRIPTION=
              (ADDRESS=
                (PROTOCOL=TCP)
                (PORT=1541)
                (HOST=gtwhost))
                (CONNECT_DATA=
                    (SID=dg4ims))
               (HS=OK))
new_IMS2_using=(DESCRIPTION=
              (ADDRESS=
                (PROTOCOL=TCP)
                (PORT=1541)
                (HOST=gtwhost))
                (CONNECT_DATA=
                    (SID=dg4ims2))
                (HS=OK))
new_IMS3_using=(DESCRIPTION=
              (ADDRESS=
                (PROTOCOL=TCP)
                (PORT=1541)
                (HOST=gtwhost))
                (CONNECT_DATA=
                    (SID=dg4ims3))
                (HS=OK))

The value for PORT is the TCP/IP port number of the Oracle Net Listener that is listening for the gateway. The number can be found in the listener.ora file used by the Oracle Net Listener. The value for HOST is the name of the system on which the gateway is running. The name also can be found in the listener.ora file used by the Oracle Net Listener.

Multiple Databases Example: Accessing IMS Data

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

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

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

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

Note:

To encrypt the initialization parameters that would normally be stored in the initialization file in plain text, you must use the dg4pwd utility, as described in Oracle Database Heterogeneous Connectivity User's Guide.

After the database links are established you can query the new IMS databases, as in the following:

SQL> SELECT * FROM ALL_TABLES@IMS2;

SQL> SELECT * FROM ALL_TABLES@IMS3;

Performing Configuration Tasks

You can perform the following configuration tasks:

Configuring for Global Transactions
Enabling Automatic Recovery

Configuring for Global Transactions

The IMS/DLI and IMS/DBDC data sources support 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 IMS/DBCTL data source supports global transactions. If the IMS/DBCTL 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 IMS/DBCTL data source is configured for global transactions, the gateway becomes the commit point site when the IMS/DBCTL data source is updated by a transaction. The Oracle database commits the unit of work in the IMS/DBCTL 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.

See Also:

Oracle Database Heterogeneous Connectivity User's Guide for information about the global transaction process.

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.

Note:

Oracle Corporation recommends that you do not use the default value RECOVER for the user name and password. Moreover, storing plain text as user name and password in the initialization file is not a good security policy. There is now a utility called dg4pwd that should be used for encryption. Refer to Chapter 4, "Encrypting Initialization parameters" in Oracle Heterogeneous Connectivity Administration Guide for details.

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 IMS/DB data source. If you use another account, then it must have the same permissions as the account that is used to access the IMS/DB 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.

Testing the IMS Data Source

You can perform the following tests on the IMS data source:

Connection test: This tests the physical connection to the data source.
Query test: This test runs an SQL SELECT query against the data source.

These tests are described in the following procedures.

See Also:

Setting Up an IMS Data Source for information on how to add a data source.

Open Oracle Studio for IMS, VSAM, and Adabas Gateways.
Expand the node of the required computer.
Expand the node of the required binding.
Expand the Data sources node.
Right-click the required data source entity, and select Test.

The Test Wizard screen opens.
Select Navigator from the Active Workspace Name list, and click Next.

The system now tests the connection to the data source, and returns the test result status.
Click Finish to exit the Test wizard.

Perform the following procedure to test the IMS data source by query:

Open Oracle Studio for IMS, VSAM, and Adabas Gateways.
Expand the node of the required computer.
Expand the node of the required binding.
Expand the Data sources node.
Right-click the required data source entity, and select Query Tool.

The Select Workspace screen opens.
Select Navigator and click OK.

The Query Tool opens in the Editor pane, with the Build Query tab displayed (see Figure 7-1).
Select the required query type from the Query Type list. The default is a SELECT-type query.
Locate and expand the node of the required data source entity.

The data source tables are listed.
Select the required table and move it to the Table column by using the arrow button.

Figure 7-1 The Query Tool screen

Description of "Figure 7-1 The Query Tool screen"
Click Execute query.

The Query Result tab opens, displaying the results of the query.
Close the Query Tool in the Editor pane.

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.

Open Oracle Studio for IMS, VSAM, and Adabas Gateways.
From the main menu, click Windows, Preferences.

The Preferences screen is displayed.

Figure 7-2 Studio Preferences

Description of "Figure 7-2 Studio Preferences"
In the left pane, click the Studio node.
Click the Advanced tab.
Click the Show advanced environment parameters check box.
Click OK.
In the Design Perspective Configuration view, right-click the binding under which the data source is located, and select Edit Binding.
In the Category combo box, select Debug.
Set the driverTrace and generalTrace parameters to TRUE.

Figure 7-3 Binding Properties

Description of "Figure 7-3 Binding Properties"
Execute the following query: Select * from nation limit to 3 rows.

The following is a sample log file output:

Example 7-1 Sample IMS Log File

ORACLE CONNECT Log (V10.1.3.1, MVS) Started at 2006-12-04T17:57:33
Licensed by Oracle Corporation on 28-SEP-2006 (001001610)
Licensed to ORACLE for <all providers> on <all machines> (<all platforms>)
 
o: 586 9991834 fopen >>> filename=TEST.OR5000A.DEF.NOSBB.SYS,mode=rb, typ
                e=record, lrecl=8208
o: 275 0 flocate >>> stream=9991834,key=99CA6C8,key_len=4,options=3
o: 292 8208 fread >>> buffer=99CB1A8, size=1, count=8208,stream=9991834
o: 424 0 fclose >>> stream=9991834
o: 275 0 flocate >>> stream=997C3F4,key=9974ED0,key_len=100,options=5
o: 292 2048 fread >>> buffer=99746C8, size=1, count=2048,stream=997C3F4
o: 275 0 flocate >>> stream=997C024,key=9974ED0,key_len=4,options=5
o: 292 2048 fread >>> buffer=99746C8, size=1, count=2048,stream=997C024
o: 275 0 flocate >>> stream=997C3F4,key=9974ED0,key_len=100,options=5
o: 292 2048 fread >>> buffer=99746C8, size=1, count=2048,stream=997C3F4
o: 275 0 flocate >>> stream=997C024,key=9974ED0,key_len=4,options=5
o: 292 2048 fread >>> buffer=99746C8, size=1, count=2048,stream=997C024
SYSBASE (699): ; REGENTP(db_ims_d) returned 0
SYSBASE (640): ; dllload(NVDBIMS) returned 8ED5A67
SYSBASE (650): ; dllqueryfn(8ED5A67, db_ims_dli_get_functions) returned 8ED5A90
<IMSDB DP> >>> CONNECT                 connect=''
<IMSDB DP> <<< CONNECT                 0
<IMSDB DP> >>> SET CONNECT  transaction type ==  '0'
<IMSDB DP> <<< SET CONNECT                 0
nvOUT (PROD.AC10131.NAV(QP#SQTXT) 89): SELECT TABLE_NAME, TABLE_TYPE FROM NAV_PROC:SP_TABLES('ims', '%', '%', 'TABLE,SYSTEM TABLE,
VIEW,SYNONYM', 0)
nvRETURN (PROD.AC10131.NAV(QPSYNON) 1140): -1
<<<<<<<<<<<<<<<<<<<  Execution Strategy Begin <<<<<<<<<<<<<<<<<<<<<<<<<<<<
 
Original SQL: 
SELECT TABLE_NAME , TABLE_TYPE FROM NAV_PROC : SP_TABLES ( 'ims' , '%' , '%' , 'TABLE,SYSTEM TABLE,VIEW,SYNONYM' , 0 )  
 
Accessing saved query spec SP_TABLES()
 from NAV_PROC DB 
 
>>>>>>>>>>>>>>>>>>>> Execution Strategy End >>>>>>>>>>>>>>>>>>>>>>>>>>>>
 
o: 586 9991834 fopen >>> filename=TEST.OR5000A.DEF.NOS.IMS,mode=rb, type=
                record, lrecl=2048
o: 393 0 fclose >>> stream=997C3F4
o: 424 0 fclose >>> stream=997C024
o: 586 Calling ENQ for TEST.OR5000A.DEF.NOS.SYS . 
o: 586 Successful exclusive enqueue retCode=0 
o: 586 9991C04 fopen >>> filename=TEST.OR5000A.DEF.NOS.SYS,mode=rb+, type
                =record
o: 627 997C024 fopen >>> filename=TEST.OR5000A.DEF.NOS.SYS.I2.PATH,mode=r
                b, type=record, lrecl=2048
o: 275 0 flocate >>> stream=997C024,key=9C13F50,key_len=100,options=5
o: 292 2048 fread >>> buffer=9C13748, size=1, count=2048,stream=997C024
o: 393 0 fclose >>> stream=997C024
o: 424 0 fclose >>> stream=9991C04
o: 424 Calling DEQ for TEST.OR5000A.DEF.NOS.SYS 
o: 424 Successful dequeue retCode=0 
o: 586 997C024 fopen >>> filename=TEST.OR5000A.DEF.NOS.IMS,mode=rb, type=
                record, lrecl=2048
o: 586 Calling ENQ for TEST.OR5000A.DEF.NOS.SYS . 
o: 586 Successful exclusive enqueue retCode=0 
o: 586 997C3F4 fopen >>> filename=TEST.OR5000A.DEF.NOS.SYS,mode=rb+, type
                =record
o: 627 997C7C4 fopen >>> filename=TEST.OR5000A.DEF.NOS.SYS.I2.PATH,mode=r
                b, type=record, lrecl=2048
o: 275 0 flocate >>> stream=997C7C4,key=9C08F30,key_len=100,options=5
o: 292 2048 fread >>> buffer=9C08728, size=1, count=2048,stream=997C7C4
o: 393 0 fclose >>> stream=997C7C4
o: 424 0 fclose >>> stream=997C3F4
o: 424 Calling DEQ for TEST.OR5000A.DEF.NOS.SYS 
o: 424 Successful dequeue retCode=0 
o: 627 997C3F4 fopen >>> filename=TEST.OR5000A.DEF.NOS.IMS.I2.PATH,mode=r
                b, type=record, lrecl=2048
o: 275 0 flocate >>> stream=997C3F4,key=9C04ED0,key_len=100,options=5
o: 292 2048 fread >>> buffer=9C046C8, size=1, count=2048,stream=997C3F4
o: 292 2048 fread >>> buffer=9C046C8, size=1, count=2048,stream=997C3F4
o: 292 2048 fread >>> buffer=9C046C8, size=1, count=2048,stream=997C3F4
o: 292 2048 fread >>> buffer=9C046C8, size=1, count=2048,stream=997C3F4
o: 292 2048 fread >>> buffer=9C046C8, size=1, count=2048,stream=997C3F4
o: 292 2048 fread >>> buffer=9C046C8, size=1, count=2048,stream=997C3F4
o: 292 2048 fread >>> buffer=9C046C8, size=1, count=2048,stream=997C3F4
o: 292 0 fread >>> buffer=9C046C8, size=1, count=2048,stream=997C3F4
o (292): ; amrc: Error = 81704
  AbendSyscode = 8
 AbendRc = 1704
 FdbkRc = 8
 Ftncd = 17
 Fdbk = 4
 svc99_info = 8
 svc99_error = 1704
 RBA = 0
 last_op = 72
o (292): ; amrc: msg=NULL
DB_VSAM_IO (302): ; amrc: Error = 81704
  AbendSyscode = 8
 AbendRc = 1704
 FdbkRc = 8
 Ftncd = 17
 Fdbk = 4
 svc99_info = 8
 svc99_error = 1704
 RBA = 0
 last_op = 6
DB_VSAM_IO (302): ; amrc: msg=NULL
DB_VSAM_IO (302): ; 
DB_VSAM_IO(302) io_vsam_get
 End of file
 
o: 586 997CFDC fopen >>> filename=TEST.OR5000A.DEF.NOS.IMS,mode=rb, type=
                record, lrecl=2048
o: 586 Calling ENQ for TEST.OR5000A.DEF.NOS.SYS . 
o: 586 Successful exclusive enqueue retCode=0 
o: 586 997D3AC fopen >>> filename=TEST.OR5000A.DEF.NOS.SYS,mode=rb+, type
                =record
o: 627 997DAAC fopen >>> filename=TEST.OR5000A.DEF.NOS.SYS.I2.PATH,mode=r
                b, type=record, lrecl=2048
o: 275 0 flocate >>> stream=997DAAC,key=9BFEF10,key_len=100,options=5
o: 292 2048 fread >>> buffer=9BFE708, size=1, count=2048,stream=997DAAC
o: 393 0 fclose >>> stream=997DAAC
o: 424 0 fclose >>> stream=997D3AC
o: 424 Calling DEQ for TEST.OR5000A.DEF.NOS.SYS 
o: 424 Successful dequeue retCode=0 
o: 627 997D3AC fopen >>> filename=TEST.OR5000A.DEF.NOS.IMS.I2.PATH,mode=r
                b, type=record, lrecl=2048
o: 275 -1 flocate >>> stream=997D3AC,key=9C0AF50,key_len=100,options=5
o (275): ; amrc: Error = 81704
  AbendSyscode = 8
 AbendRc = 1704
 FdbkRc = 8
 Ftncd = 17
 Fdbk = 4
 svc99_info = 8
 svc99_error = 1704
 RBA = 0
 last_op = 74
o (275): ; amrc: msg=NULL
nvOUT (PROD.AC10131.NAV(QP#SQTXT) 89): select * from doctor
nvRETURN (PROD.AC10131.NAV(QPSYNON) 1140): -1
o: 275 0 flocate >>> stream=997C3F4,key=9C04ED0,key_len=100,options=5
o: 292 2048 fread >>> buffer=9C046C8, size=1, count=2048,stream=997C3F4
o: 275 0 flocate >>> stream=997C024,key=9C04ED0,key_len=4,options=5
o: 292 2048 fread >>> buffer=9C046C8, size=1, count=2048,stream=997C024
o: 586 997EADC fopen >>> filename=TEST.OR5000A.DEF.NOSBB.IMS,mode=rb, typ
                e=record, lrecl=8208
o: 275 0 flocate >>> stream=997EADC,key=9C58818,key_len=4,options=3
o: 292 8208 fread >>> buffer=9C591B0, size=1, count=8208,stream=997EADC
o: 424 0 fclose >>> stream=997EADC
<<<<<<<<<<<<<<<<<<<  Execution Strategy Begin <<<<<<<<<<<<<<<<<<<<<<<<<<<<
 
Original SQL: 
select * from doctor  
 
Accessing file 'doctor' from 'ims' Database 
Fetch strategy: scan
 
>>>>>>>>>>>>>>>>>>>> Execution Strategy End >>>>>>>>>>>>>>>>>>>>>>>>>>>>
 
<IMSDB DP> >>> OPEN_STREAM       DOCTOR  
<IMSDB DP> <<< OPEN_STREAM       status=0, stream=1
<IMSDB DP> >>> SEND_RECEIVE DOCTOR   stream=1, keyLen=26, recLen=80
<IMSDB DP> >>>                 lastPCB=-1, cPCBs=1, PCBs=0
<IMSDB DP> >>>                 readahead=282, cSSAs=2
<IMSDB DP> >>>> READ HOSPITAL
<IMSDB DP> >>>>      DOCTOR  
DLIPLUG Request:
09C90410 | 00000000C4D6C3E3D6D9404000010000 | ....DOCTOR  ....
09C90420 | 0000000000000000000000000000FF00 | ................
09C90430 | 0000001A000000500000000000000000 | .......&........
09C90440 | 0000000000000000000000000000011A | ................
09C90450 | 02000000000000000000000000000000 | ................
09C90460 | 000000000000001A0000000000000000 | ................
09C90470 | 00000000000000000000000000000000 | ................
09C90480 | 0000C8D6E2D7C9E3C1D3000000000000 | ..HOSPITAL......
09C90490 | 0000C4D6C3E3D6D94040000000000000 | ..DOCTOR  ......
09C904A0 | 0000                             | ..              
 
CTDLI(2,GU  ,HOSPITAL ,DOCTOR   )
 
 GU  , DBD(HOSPDBD ), Segment(DOCTOR  ), SSA1(HOSPITAL ), SSA2(DOCTOR   ), 
Buffer(Jane Doe            Behind the fireplace          392-600359Urology            
 ), Status(  ), RC(0), KFB(Spalding 
Rehabilitat2 017 )
CTDLI(2,GN  ,HOSPITAL ,DOCTOR   )
 
 GN  , DBD(HOSPDBD ), Segment(DOCTOR  ), SSA1(HOSPITAL ), SSA2(DOCTOR   ), 
Buffer(Miki Mouse          Next to fireplace             424-337760Trauma & o         
 ), Status(  ), RC(0), KFB(Swedish Medical   
  1 041 )
CTDLI(2,GN  ,HOSPITAL ,DOCTOR   )
 
 GN  , DBD(HOSPDBD ), Segment(DOCTOR  ), SSA1(HOSPITAL ), SSA2(DOCTOR   ), 
Buffer(Yetti Bigfoot       Next to fireplace             392-693961Ear, nose          
 ), Status(  ), RC(0), KFB(Presbyterian/St.
Luke1 059 )
CTDLI(2,GN  ,HOSPITAL ,DOCTOR   )
 
 GN  , DBD(HOSPDBD ), Segment(DOCTOR  ), SSA1(HOSPITAL ), SSA2(DOCTOR   ), 
Buffer(Blonde Genius       Next to fireplace             723-135562Ophthalmol         
 ), Status(  ), RC(0), KFB(Presbyterian/St.
Luke2 060 )
CTDLI(2,GN  ,HOSPITAL ,DOCTOR   )
 
 GN  , DBD(HOSPDBD ), Segment(DOCTOR  ), SSA1(HOSPITAL ), SSA2(DOCTOR   ), 
Buffer(Gamma Tester        Next to fireplace             974-800964Oral surge         
 ), Status(  ), RC(0), KFB(Good Samaritan    
  1 061 )
CTDLI(2,GN  ,HOSPITAL ,DOCTOR   )
 
 GN  , DBD(HOSPDBD ), Segment(DOCTOR  ), SSA1(HOSPITAL ), SSA2(DOCTOR   ), 
Buffer(Dr. Verter          Next to fireplace             297-497895Restorativ         
 ), Status(  ), RC(0), KFB(West Hills 
Hospital 1 062 )
CTDLI(2,GN  ,HOSPITAL ,DOCTOR   )
 
 GN  , DBD(HOSPDBD ), Segment(DOCTOR  ), SSA1(HOSPITAL ), SSA2(DOCTOR   ), 
Buffer(Art                 Next to fireplace             594-056510Paediatric         
 ), Status(  ), RC(0), KFB(Los Gatos 
Surgical  1 064 )
CTDLI(2,GN  ,HOSPITAL ,DOCTOR   )
 
 GN  , DBD(HOSPDBD ), Segment(DOCTOR  ), SSA1(HOSPITAL ), SSA2(DOCTOR   ), 
Buffer(Joe Canabis         Next to fireplace             24-3377605Orthodonti         
 ), Status(  ), RC(0), KFB(Los Robles 
Regional 1 095 )
CTDLI(2,GN  ,HOSPITAL ,DOCTOR   )
 
 GN  , DBD(HOSPDBD ), Segment(DOCTOR  ), SSA1(HOSPITAL ), SSA2(DOCTOR   ), 
Buffer(Yoske Black         Next to fireplace             92-6939616Neurosurge         
 ), Status(  ), RC(0), KFB(Los Robles 
Regional 1 100 )
CTDLI(2,GN  ,HOSPITAL ,DOCTOR   )
 
 GN  , DBD(HOSPDBD ), Segment(        ), SSA1(HOSPITAL ), SSA2(DOCTOR   ), 
Buffer(                                                                               
 ), Status(GB), RC(51138), KFB()
DLIPLUG Response:
09C97960 | 40400000000000000000000900000001 |   ..............
09C97970 | 00000000D181958540C4968540404040 | ....Jane Doe    
09C97980 | 4040404040404040C2858889958440A3 |         Behind t
09C97990 | 88854086899985979381838540404040 | he fireplace    
09C979A0 | 404040404040F3F9F260F6F0F0F3F5F9 |       392-600359
09C979B0 | E49996939687A8404040404040404040 | Urology         
09C979C0 | 40404040E29781938489958740D98588 |     Spalding Reh
09C979D0 | 8182899389A381A3F240F0F1F740D489 | abilitat2 017 Mi
09C979E0 | 928940D496A4A2854040404040404040 | ki Mouse        
09C979F0 | 4040D585A7A340A39640868999859793 |   Next to firepl
09C97A00 | 81838540404040404040404040404040 | ace             
09C97A10 | F4F2F460F3F3F7F7F6F0E39981A49481 | 424-337760Trauma
09C97A20 | 4050409640404040404040404040E2A6 |  & o          Sw
09C97A30 | 858489A28840D4858489838193404040 | edish Medical   
09C97A40 | 4040F140F0F4F140E885A3A38940C289 |   1 041 Yetti Bi
09C97A50 | 87869696A340404040404040D585A7A3 | gfoot       Next
09C97A60 | 40A39640868999859793818385404040 |  to fireplace   
09C97A70 | 40404040404040404040F3F9F260F6F9 |           392-69
09C97A80 | F3F9F6F1C581996B409596A285404040 | 3961Ear, nose   
09C97A90 | 4040404040404040D79985A282A8A385 |         Presbyte
09C97AA0 | 9989819561E2A34BD3A49285F140F0F5 | rian/St.Luke1 05
09C97AB0 | F940C2939695848540C7859589A4A240 | 9 Blonde Genius 
09C97AC0 | 404040404040D585A7A340A396408689 |       Next to fi
09C97AD0 | 99859793818385404040404040404040 | replace         
09C97AE0 | 40404040F7F2F360F1F3F5F5F6F2D697 |     723-135562Op
09C97AF0 | 88A38881939496934040404040404040 | hthalmol        
09C97B00 | 4040D79985A282A8A3859989819561E2 |   Presbyterian/S
09C97B10 | A34BD3A49285F240F0F6F040C7819494 | t.Luke2 060 Gamm
09C97B20 | 8140E385A2A385994040404040404040 | a Tester        
09C97B30 | D585A7A340A396408689998597938183 | Next to fireplac
09C97B40 | 8540404040404040404040404040F9F7 | e             97
09C97B50 | F460F8F0F0F9F6F4D699819340A2A499 | 4-800964Oral sur
09C97B60 | 878540404040404040404040C7969684 | ge          Good
09C97B70 | 40E28194819989A38195404040404040 |  Samaritan      
09C97B80 | F140F0F6F140C4994B40E58599A38599 | 1 061 Dr. Verter
09C97B90 | 40404040404040404040D585A7A340A3 |           Next t
09C97BA0 | 96408689998597938183854040404040 | o fireplace     
09C97BB0 | 4040404040404040F2F9F760F4F9F7F8 |         297-4978
09C97BC0 | F9F5D985A2A3969981A389A540404040 | 95Restorativ    
09C97BD0 | 404040404040E685A2A340C8899393A2 |       West Hills
09C97BE0 | 40C896A29789A3819340F140F0F6F240 |  Hospital 1 062 
09C97BF0 | C199A340404040404040404040404040 | Art             
09C97C00 | 40404040D585A7A340A3964086899985 |     Next to fire
09C97C10 | 97938183854040404040404040404040 | place           
09C97C20 | 4040F5F9F460F0F5F6F5F1F0D7818584 |   594-056510Paed
09C97C30 | 8981A399898340404040404040404040 | iatric          
09C97C40 | D396A240C781A396A240E2A499878983 | Los Gatos Surgic
09C97C50 | 81934040F140F0F6F440D1968540C381 | al  1 064 Joe Ca
09C97C60 | 95818289A2404040404040404040D585 | nabis         Ne
09C97C70 | A7A340A3964086899985979381838540 | xt to fireplace 
09C97C80 | 404040404040404040404040F2F460F3 |             24-3
09C97C90 | F3F7F7F6F0F5D699A38896849695A389 | 377605Orthodonti
09C97CA0 | 40404040404040404040D396A240D996 |           Los Ro
09C97CB0 | 829385A240D98587899695819340F140 | bles Regional 1 
09C97CC0 | F0F9F540E896A2928540C29381839240 | 095 Yoske Black 
09C97CD0 | 4040404040404040D585A7A340A39640 |         Next to 
09C97CE0 | 86899985979381838540404040404040 | fireplace       
09C97CF0 | 404040404040F9F260F6F9F3F9F6F1F6 |       92-6939616
09C97D00 | D585A49996A2A4998785404040404040 | Neurosurge      
09C97D10 | 40404040D396A240D996829385A240D9 |     Los Robles R
09C97D20 | 8587899695819340F140F1F0F040     | egional 1 100   
 
<IMSDB DP> <<< SEND_RECEIVE DOCTOR   stream=1, status='  ', readahead=9, PCB=0,
 flags=1
<IMSDB DP> >>> CLOSE_STREAM    DOCTOR, stream=1
<IMSDB DP> <<< CLOSE_STREAM    0
nvRETURN (PROD.AC10131.NAV(DRVIUNWN) 804): -1210
(Last message occurred 3 times)
Disabled FilePool Cleanup(DB=___sys, FilePool Size=0)
 
Disabled FilePool Cleanup(DB=IMS, FilePool Size=0)
 
<IMSDB DP> >>> DISCONNECT
<IMSDB DP> <<< DISCONNECT
o: 393 0 fclose >>> stream=997C3F4
o: 424 0 fclose >>> stream=997C024
o: 424 0 fclose >>> stream=9991834
o: 393 0 fclose >>> stream=997D3AC
o: 424 0 fclose >>> stream=997CFDC
FilePool Shutdown(DB=___SYS, FilePool Size=0)
 
Closing log file at MON DEC  4 17:57:49 2006