Interface to Informix DBMS

Interface to Sybase DBMS

Chapter 28: Interface to Oracle DBMS

This chapter describes how to use Oracle in this COBOL system and how to compile and link COBOL programs containing their code. It also describes the COBOL to Oracle integration package, explaining how to use it, as well as giving solutions to some common problems.

28.1 Overview

You can access the SQL functions offered by the database system from your COBOL program by embedding SQL statements in your COBOL programs using the following format:

EXEC SQL SQL_statement END-EXEC.

A preprocess step then replaces statements of the above form with the relevant calls to database services. Other additions are made to the source code to bind host COBOL variables to the SQL variable names known to the database system.

The advantage of embedding SQL in this way is that the programmer need not know the format of individual database routines. The disadvantage is that the resulting source code must be linked to the database routines, and therefore Animator using .int and .gnt code variants are unavailable. The COBOL to Oracle integration package described later in this chapter overcomes this limitation, but the source code used by Animator is the output from the precompiler rather than the original Embedded SQL written by the programmer. This disadvantage can be overcome by using the COBSQL integrated preprocessor (see the chapter Using COBSQL).

28.1.1 Software Requirements

Please consult on disk documentation for information about the compatibility of this COBOL System with the various database management systems and their precompilers.

DOS:
There is a special version of Pro*COBOL available from Oracle which is XM aware for running on DOS. It is V1.3.1.08.

28.1.2 Working with .int/.gnt Files and Oracle

You can run your COBOL program containing Oracle code in .int or .gnt code format. To make Oracle calls accessible to COBOL programs in .int or .gnt code format, you must use the oralib interface module to resolve the calls from COBOL to Oracle.

The COBOL to Oracle integration package consists of:

Windows and OS/2:
For Windows and OS/2 oralib simply loads the appropriate Oracle DLL. Depending on the version of Oracle in use, you may have to edit and recompile oralib to make it load the correct DLL:

• Windows:
  For Windows, the Oracle DLL is sql16win.dll.

• OS/2 16-bit:
  For 16 bit OS/2 applications the names are of the form sqlxx.dll, where xx is the Pro*COBOL version number, and is probably sql13.dll.

• OS/2 32-bit:
  For 32 bit OS/2 applications the name is sqlxxo32, where xx is the Pro*COBOL version number. xx is likely to be 15, 16 or 17.

• Windows 32-bit:
  For 32-bit Windows applications the name is sqllib17 or sqlcob17.

• DOS:
  For DOS, oralib loads oralib.dle. You must make cbl2ora.dle by linking cbl2orad.obj via the command line:

  link @dos.lnk

  The file oralib.dle contains all the Oracle runtime routines for DOS.

You also need the following COBOL system files to use the Oracle database support:

* applic.cfg, applic.exe and applic.lbr are user-defined names for the user
application configuration, executable and library files.

You also need the PRO*Cobol support files and SQL*Net or local database. See the documentation supplied with your Oracle product for details on the support files required.

UNIX:
On UNIX, you may animate and run .int and .gnt code by using a COBOL Run-time System that has been linked with the SQL run-time routines supplied by Oracle.

The Oracle installer builds a suitable RTS, called rtsora. The RTS may also be rebuilt by the command:

make -f $ORACLE_HOME/procob/lib/procob.mk rtsora

28.2 Linking Oracle Programs

Linking COBOL programs containing Oracle code is described in the manuals supplied with Pro*COBOL by Oracle.

28.2.1 Loading a COBOL to Oracle Interface Module

You can load a COBOL to Oracle Interface Module in any of the following ways:

• Add an extra line to your COBOL code to call your oralib module as follows:

       call "oralib"

  This is the simplest way. This call must be made before any database access is attempted.

• Compile your program with the INITCALL directive, specifying your oralib module:

  INITCALL(oralib)

  This Compiler directive forces the main module to automatically load the specified module.

• Specify your oralib module in your application configuration file (if you are using the Build utility available with the add-on product Toolset).

  With a built system, the application has a configuration (.cfg) file that specifies the programs that makeup the system. You can modify this .cfg file to preload the oralib module as the system is started.

  See the section Using COBOL Configuration Files for further details on this method.

Note: If you use either of the first two methods, ensure that only the top-level program calls the oralib interface module. Do not compile a subprogram with the INITCALL directive.

28.2.1.1 Using COBOL Configuration Files

If you are using the Build utility, you can load your oralib file from your application configuration file. To do so, you must add the following information to your .cfg file:

[APPLIC-STARTUP]
APPLICATION-LIBRARY:applic.lbr
INITIALIZATION-LIBRARY:TOOLS.LBR

[APPLIC-TRACE]
OFF

[APPLIC-INSTALL]
ORALIB

[APPLIC-PROGRAMS]
applic.lbr
UTILS.LBR

[APPLIC-SWITCHES]
(+a1+a0+f0+k3+l6+l2+n0+o0+p0+v0)

Note: applic.lbr is a user-defined name for the user application library file.

28.3 COBOL-to-Oracle Interfacing Considerations

To avoid potential problems when interfacing between COBOL and Oracle, you must take the following areas into consideration:

• Reserve enough space for the COBOL run-time system to create the program stack.

  You do this by setting the /S RTS switch in the COBSW environment variable. There is no absolute value to use to resolve this problem. To determine the best value for your circumstances, we recommend you try a value of 20000 first and adjust this up or down as required. For details on the /S run-time switch, see your Object COBOL User Guide.

  You can set this environment variable in your autoexec.bat file (on DOS or Windows) or your config.sys file (on OS/2) as follows:

  set cobsw=/S20000.

  DOS:
  The latest DOS precompiler for COBOL released by Oracle is 1.3.08. This is a special release which works with XM. Because Oracle programs can be memory-intensive, it is not practical to use the real mode support files.

  To ensure that your program picks up the correct version of the Oracle support files, the oracle\pbin directory must appear on the path before the oracle\bin directory.

16-bit Windows:
The first version of Pro*COBOL supported on 16-bit Windows is 1.6. This is actually a 32-bit application and so WIN32S must be installed on the machine where it is running. However, Pro*COBOL 1.6 thunks to 16-bit and will only run with the 16-bit version of COBOL.

16-bit OS/2:
The last version of Pro*COBOL supported on 16-bit OS/2 is 1.3. Later versions of Pro*COBOL only support 32-bit COBOL.

32-bit Windows:
Pro*COBOL Version 1.7 for Windows NT is a 32-bit application and will only work with 32-bit COBOL.

32-bit OS/2:
Versions of Pro*COBOL later than 1.5 for OS/2 are 32-bit applications. These versions will only work with 32-bit COBOL.

UNIX:
On UNIX, all versions of Pro*COBOL and COBOL are 32-bit. Please refer to your Oracle documentation for the recommended version of COBOL to use with your version of Pro*COBOL.

28.3.1 DOS Considerations

This section describes issues you must consider when interfacing between COBOL and Oracle on DOS.

If you want to run your COBOL/Oracle application against a remote database, you should ensure that at least 300Kb of base memory is free. If you want to run the application against a local database, the amount of memory required to run correctly is likely to be more than that available in base memory.

COBOL programs on DOS need to be run under the memory manager XM. Similarly, when running under DOS, Oracle uses its own memory manager, SQLPME.

In older versions of this COBOL system, there was no way for Oracle to route a call out of XM. Current versions of Micro Focus COBOL and Oracle provide this support. You must use the versions of COBOL and Oracle, including a special version of Pro*COBOL, given in the section Software Requirements earlier in this chapter to obtain this support.

The special version of Pro*COBOL enables a user program running under XM to talk to SQLPME. By default both XM and SQLPME try to use all available memory. Therefore, to enable this facility, you must restrict the memory for both memory managers.

To allocate the amount of extended memory to be used by XM, you set the /Z XM switch. We recommend you set /Z to a value of 4096Kb. You can set this switch in your autoexec.bat file as follows:

set xm=/Z4096

To allocate the amount of extended memory to be used by SQLPME, you set the DYNAMIC_MEMORY environment variable We recommend you set this environment variable to a value of 6020Kb. You set this in the Oracle configuration file, config.ora, as follows:

DYNAMIC_MEMORY=6020

28.3.1.1 Restrictions

The following restrictions apply when interfacing between COBOL and Oracle on DOS:

• Problems have been noted using COMP-3 data items containing decimal places in .int or .gnt code. The problem is not apparent when linked using LCOBOL or COBLIB. Oracle are investigating the cause of this problem.

• Remember when linking for DOS, you must link in the Micro Focus C6 support libraries. See the section Linking Oracle Programs for details.

28.3.2 Windows Considerations

This section describes issues you must consider when interfacing between COBOL and Oracle on Windows.

You must install the Oracle Required Support Files for Windows on your machine. You must also ensure that these files are made available on the PATH.

It is important to ensure that the COBOL stack has been set up, or the call to allocate memory (usually the last C call of the expanded connect statement) will fail. This takes the form of a Protection Violation caused by Initapp in coblib.dlw.

There must sufficient system resources for Oracle to make its connection; otherwise, you could receive a 9241 error when trying to connect to the database. Specifically, you must take the following two areas into consideration:

• If the user requires to run against a remote database, then the following points apply.
  • You must ensure that the system connects to SQL*Net when running in Windows enhanced mode.

  • You specify the server by using the "at remote_dbname" extension to Oracle SQL syntax, or by performing one of the two following actions:
    1. Add the following line to the config.ora file:

           LOCAL=Remote_Dbname

       and ensure that no environment variables start with "LOC".

    2. Set the LOCAL environment variable to point to the remote database. Remote_Dbname is the name of the Remote Database SQL*Net tries to connect to by default. For example:

           SET LOCAL=Remote_Dbname

       See the documentation supplied with your Oracle system for details on setting this environment variable.

28.3.3 OS/2 Considerations

OS/2:
This section describes issues you must consider when interfacing between COBOL and Oracle on OS/2.

The Oracle Required Support Files must be installed

You must ensure that the Oracle .dll files are made available on the LIBPATH.

If you are running with a local database, you need to be aware of the following issues:

• Oracle uses a large amount of memory. If errors are encountered when trying to startup the database, the following errors are displayed;

  LCC-00100: internal error, argument [124]
  ORA-01078: failure in processing system parameters

  Try closing down some applications to get Oracle to startup. If this is not possible, then reduce the amount of memory required by Oracle by setting the required parameters in the config.ora file. See your Oracle DBA Guide for details on these parameters.

• There is a bug in the installation procedure for Oracle V6.0.a. The name of the database file is not set up correctly; that is, the drive letter is omitted, so the full path name is not written into the Oracle system tables. This means that all your Pro*COBOL applications must be run in the database directory.

  To overcome this problem, log in as the sqldba before running the install.sql program. This .sql file is held in the oracle database directory, oracle6\dbs.

28.4 Filename Extensions

Oracle uses .pco and .cob filename extensions. For the Micro Focus Copybook Preprocessor (CP) to resolve copybooks and include statements correctly, use the following COBOL Compiler directives:

osext (pco) copyext (cob,cpy,cbl)

28.5 Project Definition

You can use Workbench Organizer to create an Oracle project (see the chapter Configuring Your Organizer Desktop in your Workbench User Guide). Typical values are:

28.6 COBSQL Directives

If you are using COBSQL you can use the cobsql.dir file to specify COBSQL directives. Typical values are shown below:

confirm
cbl2ora
cobsqltype=oracle-win
cobsqlinclude=e:\ora7win\pro16\cob
end-c
anim=no
comp5=yes

28.7 COBSQL and Oracle Considerations

If you are using COBSQL in conjunction with the Oracle precompiler, you should bear in mind the following points.

• The following ORACLE precompiler options can have a marked effect on the behaviour and the memory requirements of an ORACLE application. We recommend that you take time to review the ORACLE documentation before changing the setting of these directives:

  DBMS
  HOLD_CURSOR
  MAXOPENCURSORS
  MODE
  RELEASE_CURSOR

• Within a COBOL program it is possible to use the ALTER SESSION command to change the OPTIMIZER_GOAL. This can have a profound effect on the performance of the application. Before using this syntax, we recommend that you consider what is the best optimization for an application in the general case rather than for a particular SQL statement. If you need to change the performance of one particular statement, it is probably better to use hints.

• If the application needs to access a remote ORACLE Database using SQL*NET version 2.x, the connection string must be prefixed by "TNS:". This tells SQL*NET that the connection string is a TNS alias. This only applies to Windows V3.x.

• If the original source has host variables defined as COMP, the Pro*Cobol option of comp5=no should be used. If comp5=yes is used when host variables are defined as COMP, COBSQL cannot process the program correctly.

• The copybooks supplied by ORACLE may contain lines longer than 80 characters. This can result in Pro*Cobol giving "a line too long" error. The simplest way to stop this is to edit the copybooks to reduce the line length to less than 80 characters.

• For some Pro*Cobol installations, the SQLCA5 file may not be present. Copy the sqlca.cob file to sqlca5.cob. Ensure that the sqlca.cob file has items defined as COMP and the sqlca5.cob file has items defined as COMP-5.

• Under Windows, the Pro*Cobol command line is limited to 128 characters. COBSQL needs to put on the command line the name of the input file, the name of the output file, the name of the list file and any precompiler options that are required for the program. If a program that is not in the current directory, needs to be compiled the command line can become too long and Pro*Cobol will report command line errors.

  If errors still occur when compiling, the Pro*Cobol directives need to be put into the ORACLE configuration file. By default, this resides in the %ORACLE_HOME%\%Pro*Compilers% directory where %ORACLE_HOME% is the ORACLE root directory, and %Pro*Compilers% is the directory the precompilers are installed into (for example pro16). This file is normally called pcccob.cfg and Pro*COBOL directives can be placed in here. This file would have the following directives:

  include=e:\ora7win\pro16\cob
  comp5=yes

  The COBSQLINCLUDE directive would no longer be used.

• Pro*Cobol always needs to be supplied with an include directive (unless the sqlca copybook is in the current directory), as it needs to know where the sqlca copybook is. If this is not supplied, Pro*Cobol gives an error on filename.pco, where filename can be any string of eight characters.

• It is possible to get General Protection Faults under Windows V3.x if ORACLE sub-programs are cancelled. The General Protection Fault can occur when either of the following SQL statements are executed:

  COMMIT (WORK) RELEASE
  ROLLBACK (WORK) RELEASE

  When cancelling ORACLE sub programs, make sure that you either perform a COMMIT/ROLLBACK RELEASE before you cancel the sub-module (a CONNECT would have to be performed after the sub-module was cancelled), or use the following SQL statements in place of the above statements:

  COMMIT (WORK)
  ROLLBACK (WORK)

Note: The oraprot module has a ROLLBACK WORK RELEASE statement in it. This would have to be changed to a ROLLBACK WORK statement.

• When Pro*Cobol runs, it can generate useful information. When the COBSQL VERBOSE directive is used, COBSQL will display as much of the information as it can.

  To get the maximum information from Pro*Cobol, set the Pro*Cobol directive xref=yes. This can be added to the Pro*Cobol configuration file, pcccob.cfg.

• By using the COBSQL DISPLAY directive, the current Pro*Cobol directives can be seen on the screen. This will be followed by the statistical information created by Pro*Cobol.

• If the COBOL list directive is used, COBSQL will pass any information, it has collected from the precompiler to the COBOL checker for inclusion at the bottom of the COBOL listing.

Interface to Informix DBMS

Interface to Sybase DBMS