SQL Option for DB2

Chapter 11: COBSQL

COBSQL is an integrated preprocessor designed to work with COBOL precompilers supplied by relational database vendors. It is intended for use with:

• Oracle Pro*COBOL Version 1.8

• Oracle Pro*COBOL Version 8.04

• Informix Embedded SQL/COBOL Version 9.x

• Sybase Open Client Embedded SQL/COBOL Version 11.5

You should use COBSQL if you are already using either of these precompilers with an earlier version of a Micro Focus COBOL product and want to migrate your application(s) to Net Express, or if you are creating applications that will be deployed on UNIX platforms and need to access either Oracle or Sybase relational databases.

For any other type of embedded SQL application development, we recommend that you use OpenESQL.

Note: The Oracle Version 1.8 precompiler does not support nested programs. COBSQL does not support Object Oriented COBOL syntax (OO COBOL). If you want to use OO COBOL, therefore, you must use OpenESQL.

11.1 Overview

You can access the SQL functions offered by the Oracle, Sybase or Informix Database Management System (DBMS) by embedding SQL statements within your COBOL program in the form:

EXEC SQL
   SQL statement
END-EXEC

and then using the Oracle, Sybase or Informix precompiler to process the embedded SQL before passing the program to the COBOL Compiler. The database precompiler replaces embedded SQL statements with the appropriate calls to database services. Other additions are made to the source code to bind COBOL host variables to the SQL variable names known to the database system.

The advantage of embedding SQL in this way is that you do not need to know the format of individual database routine calls. The disadvantage is that the source code that you see when you animate your program is that output by the precompiler and not the original embedded SQL. You can overcome this disadvantage by using COBSQL.

COBSQL provides an integrated interface between Micro Focus COBOL and the third-party standalone precompiler, enabling you to animate a program containing EXEC SQL statements and display your original source code rather than the code produced by the precompiler.

This chapter shows you how you can use COBSQL in conjunction with either the Oracle, Sybase or Informix precompiler to compile and animate your programs.

11.2 Operation

To use COBSQL, specify the PREPROCESS"COBSQL" Compiler directive when you compile your program. All directives following it are passed from the Compiler to COBSQL. You can specify Compiler directives by using $SET statements in your program or via the Net Express Build Settings screen.

To terminate the directives to be passed to COBSQL, you must use the ENDP COBOL directive. You can do this by adding the following line either to the project settings or to the end of the Net Express Build settings:

Preprocess(Cobsql) csqltype=database_product end-c comp5=yes endp;

Preprocess(Cobsql) csqltype=oracle end-c comp5=yes endp;

Note: Net Express ignores any directives placed after the semi-colon (;) in the Build settings. Therefore, if you add the above line to the Build settings, you must position the line at the end of the settings.

Since the system sets extra default Cobol directives, the above line is required when putting directives into the Build Settings dialog from within Net Express.

For both project settings and Net Express Build settings, END-C and ENDP have the following effect:

• Directives placed before END-C pass to COBSQL

• Directives placed between END-C and ENDP pass via COBSQL to the precompiler

• Directives placed after ENDP pass to the COBOL compiler. Therefore, without the ENDP directive, compiler directives continue to pass to COBSQL rather than to the COBOL compiler.

11.2.1 Specifying Directives

You specify directives to COBSQL as if they were Compiler directives, but you must put them after the directive PREPROCESS"COBSQL".

It is also possible to add the Cobsql directives to the standard Net Express directives file cobol.dir.

Notes:

• Each line in the cobol.dir file may contain one or more compiler directives

• Avoid splitting a compiler directive across multiple lines in the cobol.dir file

• When the compiler encounters either P(COBSQL) or PREPROCESS(COBSQL) in the cobol.dir file, the compiler passes the rest of the line to the pre-processor until it reaches an ENDP

• The compiler treats the pre-process statement and all the options that follow, up to the ENDP, as a single compiler directive. Therefore, the pre-process statement and all the options must all appear on a single line in the cobol.dir file.

Alternatively, you can put COBSQL and precompiler directives in a file, cobsql.dir. This file should reside either in the current directory or in a directory specified in $COBDIR. COBSQL searches the current directory and then along the COBDIR path for a cobsql.dir file. Once COBSQL finds a cobsql.dir file, it stops searching. So, if you have a cobsql.dir file in the current directory, the COBDIR path is not searched.

Notes:

• Each line in the file cobsql.dir can contain one or more COBSQL directives

• Since COBOL does not read the file cobsql.dir, avoid putting COBOL compiler directives into the file

• Avoid splitting a COBSQL directive across multiple lines in the file cobsql.dir

• When COBSQL encounters either END-C, END or END-COBSQL in the cobsql.dir file, the rest of the line passes to the database precompiler

• The options to be passed to the database precompiler must all appear on a single line in the file cobsql.dir

COBSQL processes cobsql.dir first and then any directives specified via the Build Settings screen.

A number of the directives can be reversed by placing NO in front of them, for example, DISPLAY can be reversed using NODISPLAY. All the directives in the lists below that can have NO placed in front of them are marked with an asterisk. By default, the NO version of a directive is set.

You can specify shortened versions of some of the directives. If applicable, the shortened version of a directive is shown in the lists below, immediately after the full length version.

Some directives can be passed to COBSQL by the COBOL Compiler (see the section COBOL Directives), removing the need to specify common directives more than once. Directives that can be retrieved from the COBOL Compiler are processed before COBSQL directives.

For example, in the following command line:

cobol testprog p(cobsql) csqlt=ora makesyn end-c comp5=yes 
   mode=ansi endp omf(gnt) list();

• The COBSQL directives, terminated by end-c, are csqlt=ora and makesyn

• The precompiler directives (in this case, Pro*COBOL), terminated by endp, are comp5=yes and mode=ansi

• The Compiler directives are omf(gnt) and list()

11.2.2 COBSQL Directives

The following is a list of the COBSQL directives:

11.2.3 COBOL Directives

The following is a list of the COBOL directives:

11.3 Building COBSQL Applications

It is beyond the scope of this document to list the database support modules that are required when shipping on a COBSQL application. It is assumed that the end-user machine already has all the required support modules installed and that it is correctly configured to communicate with the database server.

When linking COBSQL applications, use the import library csqlsupp.lib. This resolves the calls inserted by COBSQL to the COBSQL init and stop run modules. The calls are actually handled by the module csqlsupp.dll which needs to be shipped with the application. This general support module is required for both Oracle and Sybase applications.

Specify the library csqlsupp.lib as one of the libraries to use when linking the application. The library csqlsupp.lib resides in the directory Net Express\base\lib. The module csqlsupp.dll resides in the directory Net Express\base\bin.

If you split the application into a main exe and a number of sub DLL's, then you only need to link csqlsupp.lib into the modules that you have compiled with the COBSQL directives CSTART or CSTOP.

If you compile all programs with CSTART or CSTOP, then you must link csqlsupp.lib into all the modules. Linking csqlsupp.lib to each module causes each module to be slightly larger than required. Only one version of csqlsupp.dll is loaded when the application runs.

If you compile the main program only with CSTART and CSTOP, then you need to link in the library csqlsupp.lib with the main program only. Any program that you compile with either the CSTART or the CSTOP COBSQL directive, you then need to link csqlsupp.lib with the module for that program.

11.4 Using the CP Preprocessor to Expand Copyfiles

The complete set of methods used within COBOL to manipulate copyfiles is not available with database precompilers and COBSQL itself cannot handle included copyfiles. These problems can be overcome, however, by using the Micro Focus Copyfile Preprocessor (CP).

CP is a preprocessor that has been written to provide other preprocessors, such as COBSQL, with a mechanism for handling copyfiles. CP follows the same rules as the COBOL Compiler for handling copyfiles so any copyfile-related Compiler directives are automatically picked up and copyfiles are searched for using the COBCPY environment variable. CP will also expand:

EXEC SQL
   INCLUDE ...
END-EXEC

statements. For more information on CP, refer to the Net Express online help (look under "CP" in the help file index).

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

copyext (eco,mf2,cbl,cpy,cob) osext(eco)

COBSQL can call CP to expand copyfiles before the database precompiler is invoked. This means that all the copy-related commands are already resolved so that it appears to the database precompiler that a single source file is being used.

The other advantage of using CP is that it makes copyfiles visible when animating.

When CP sees an INCLUDE SQLCA statement, it does the following:

• Searches for a file called sqlca.ext in the current directory where ext is any copyfile extension as set up by the OSEXT and COPYEXT Compiler directives. By default these are .cbl and .cpy.

• Searches for sqlca.ext along the COBCPY path.

• An example sqlca.cpy file is provided in the source directory under your Net Express base installation directory. If the SQLCA file supplied by the database vendor is not located on the COBCPY path, this Micro Focus demonstration version of the SQLCA will be used. Note that if this file is used, it could lead to errors when the program is run.

You can specify the CP preprocessor's SY directive to prevent CP expanding the SQLCA include file, for example:

preprocess"cobsql" preprocess"cp" sy endp

You should always use CP's SY directive when processing Sybase code because Sybase expects to expand the SQLCA itself.

As Oracle can produce code with either COMP or COMP-5 variables, it has two sets of copyfiles. The standard sqlca.cob, oraca.cob and sqlda.cob all have COMP data items. The sqlca5.cob, oraca5.cob and sqlda5.cob files have COMP-5 data items. If you are using the comp5=yes Oracle directive, you must set the COBSQL directive MAKESYN to convert the COMP items in the SQLCA to COMP-5.

If CP produces errors when attempting to locate copyfiles, check to make sure that the OSEXT and COPYEXT Compiler directives are set up correctly. COPYEXT should be set first and should include as its first entry the extension used for source files (.pco or .eco, for example).

If these are set correctly, ensure that the copyfile is either in the current directory or in a directory on the COBCPY path.

When using CP in conjunction with COBSQL, SQL errors inside included copyfiles will be reported correctly. Without CP, the line counts will be wrong, and the error will either go unreported or will appear on the wrong line.

11.5 National Language Support (NLS)

COBSQL error messages can be displayed in different languages depending on the setting of the LANG environment variable. For full details on NLS and how to set the LANG environment variable, look up NLS and LANG in the help file index.

Most database clients include some NLS capability but their requirements for the setting of the LANG environment variable differ from those of this COBOL system. We recommend, therefore, that you use the alternative environment variable, COBLANG.

The setting of COBLANG only affects this COBOL system, allowing the LANG environment variable to be used by the database client. Note that for COBLANG to work correctly, mflangnn.lbr, where nn is the setting of COBLANG, must be available in the Net Express \bin directory. So if COBLANG=05 (UK NLS messages), the file mflang05.lbr must be present in Net Express\Base\Bin.

The setting of COBLANG only affects the COBSQL error messages; error messages produced by the database precompiler are not translated by COBSQL.

11.6 Examples

The following examples show, for the Oracle, Sybase and Informix precompilers, command lines which can be entered at the Net Express Command Prompt to compile a program using COBSQL.

11.6.1 Oracle

cobol sample.pco anim nognt preprocess(cobsql)
   cstart cstop CSQLT=ORA end-c comp5=yes endp;

For UNIX:

cob -a -v -k sample.pco -C "p(cobsql) cstop cobsqltype==ORACLE"

11.6.2 Sybase

cobol example1.pco confirm preprocess(cobsql)
   cstop csp cobsqltype=sybase preprocess(cp) sy endp;

For UNIX:

cob -a -v -P -k example1.pco -C"p(cobsql) csp CSQLT==syb"

11.6.3 Informix

For UNIX:

cob -a -k demo1.eco -C "p(cobsql) cobsqltype==informix-new"

11.7 Troubleshooting

• Basic network connectivity

  Forget SQL, and determine whether the client and server are communicating. For TCP/IP, check whether you can ping the server from the workstation and vice versa. If host names don't work, try raw IP addresses. For PC protocols, try mounting a network drive or sending messages.

• SQL networking software

  Check that the SQL networking software is "talking" correctly to the network software. Many SQL vendors supply a ping utility which will show whether the SQL network is set up correctly.

• Interactive SQL

  If the SQL network is okay, try some interactive SQL. Most vendors supply a simple utility that allows you to enter SQL from the keyboard and view the results. Most vendors also supply a sample database that is useful for this purpose.

• Standalone Precompiler

  Verify that the standalone precompiler works. There may be an icon or a command line for the precompiler. Verify that it can produce COBOL code correctly. It is normal for some sample applications to be supplied with the precompiler.

• Preprocessed application

  Check that a preprocessed application runs okay. Pass the expanded program through the COBOL Compiler and then try to run it.

• COBSQL with minimal directives

  Try COBSQL with minimal directives. Set up a project in Net Express, place the SQLCA copyfile into the directory with the sample program (prior to running the precompiler), and see if this works.

11.7.1 Common Problem Areas

If you cannot locate the source of the problem, check each of the following:

• Latest versions

  Ensure that you are using the latest version of all the products involved.

• COMP/COMP-5 Conflicts

  Check the vendor's documentation and example applications.

• Configuration

  Check that environment variables, PATH and configuration file settings are set up correctly.

• Directives

  By default, COBSQL does not display the command line it passes to the database precompiler. Setting the SQLDEBUG directive enables the command line to be displayed (you will need to do this if the precompiler gives command line errors). Possible causes of command line errors are that the directives to be passed to the precompiler are incorrect or that the length of the precompiler command line has been exceeded.

• Memory

  COBSQL may display the following error because the database precompiler has terminated unexpectedly:

  * CSQL-F-021: Precompiler did not complete -- Terminating

  This may be because the Operating System has run out of memory attempting to execute the database precompiler.

• Missing output files

  COBSQL may display the following errors because it cannot find the precompiler's output file. This may be because the precompiler did not produce an output file. The normal reason for this is that the precompiler hit a fatal error which meant it could not create the output file.

  * CSQL-E-024: Encountered an I/O on file filename
  * CSQL-E-023: File Status  3 / 5

  where filename is the name of the file produced by the database precompiler.

• Premature end of expanded source

  If COBSQL reports the error "Premature end of expanded source" and the precompiler runs correctly, this indicates that COBSQL has not been able to match the original source lines with the lines produced by the database precompiler.

  Another possible reason for COBSQL reporting this error is that the program does not contain any SQL. Generally, if the database precompiler does not come across any SQL it will abort the creation of its output file part way through, causing this error to be displayed.

11.7.2 Oracle Considerations

• 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 your Oracle documentation before changing the setting of these directives.

  DBMS
  HOLD_CURSOR
  MAXOPENCURSORS
  MODE
  RELEASE_CURSOR

• Within an Oracle 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 optimisation 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. Consult your Oracle documentation for further information on ALTER SESSION, OPTIMIZER_GOAL and HINTS.

• Oracle provides a method of reducing network access by using arrays within Embedded SQL statements. This enables the application to perform 'batch' SQL commands, in that more than one row can be acted upon in a single SQL statement. See the section Host Arrays in the chapter Host Variables for more details.

  The use of arrays enables an application, for example, to fetch ten rows at a time instead of one at a time. Oracle supply an example program (normally called sample3.pco) that uses an array to fetch multiple rows. Arrays are documented in the Pro*Cobol Supplement to the ORACLE Precompilers Guide.

• Pro*Cobol treats BINARY host variables as though they had been defined with a picture clause of COMP. This means that BINARY items will also be converted to COMP-5 when the Pro*Cobol comp5=yes option is used.

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

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

  To get the maximum information from Pro*Cobol, set the Pro*Cobol directive xref=yes. You can add this directive to the Pro*Cobol configuration file $ORACLE_HOME\PROxx\pcbcfg.cfg where:

  xx	is the Pro*COBOL version (for example, for Oracle 8.0 this is PRO80)
  $ORACLE_HOME	is the root directory for the Oracle installation on your machine

• 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 passes any information it has collected from the precompiler to the COBOL checker for inclusion at the the bottom of the COBOL listing.

11.7.4 Sybase Considerations

• If CP is not used with COBSQL to locate copyfiles, the copyfile to be brought in by the Sybase precompiler needs to be fully qualified. The Sybase precompiler does not search for any extensions.

• If the Sybase precompiler has problems locating the correct language to use to report messages on the client, check that the Sybase file, locales.dat is configured correctly.

  If the default setting for the client Operating System has been configured, but Sybase still reports national language support errors, use the LANG environment variable to override the setting in the locales.dat file.

  For example if the Windows NT client was causing problems and the locales.dat file contained the following setting for Windows NT:

  [NT]
  locale = default, us_english, iso_1
  locale = enu, us_english, iso_1
  locale = fra, french, iso_1
  locale = deu, german, iso_1

  then the LANG setting for English would be:

  LANG=enu

• It can be difficult to identify Sybase error messages. To help COBSQL identify them, you can modify the esql.loc file. Use a text editor to edit the esql.loc file to change the layout of the messages to the following:

  SYB-number-type-text

  where the parameters are:

  SYB-	A string which indicates to COBSQL that this is a modified Sybase error message.
  number-	A unique, four digit error number assigned to the Sybase error.
  type-	Indicates the severity of the errror; some of the Sybase messages are only warnings rather than normal or fatal errors.
  text	The original Sybase error message.

  For example, a typical entry in the esql.loc file might be:

  9 = M_PRECLINE, "Warning(s) during check of query on line %1!."

  and this would be changed to read:

  9 = M_PRECLINE, "SYB-W-2009 Warning(s) during check of query on line %1!."

  It is recommended that you make a copy of esql.loc before altering it. Using the modified version, COBSQL can detect the full range of Sybase error messages.

  The location of esql.loc is dependent on the language and code page used. This is defined in the locales.dat file. If the definition of the default language for the AIX platform was as follows:

  [aix]
  locale = C, us_english, iso_1
  locale = En_US, us_english, iso_1
  locale = en_US, us_english, iso_1
  locale = default, us_english, iso_1

  The default langauge would be us_english, using the iso_1 code page, so the copy of esql.loc that is to be used is:

  /sybase home/locales/messages/us_english/iso_1/esql.loc

  where sybase home is the directory that the Sybase client is installed into.

  For more information on how Sybase uses and locates the different error message files, refer to your Sybase Client Reference Manual.

• To use Sybping with different Sybase servers, each server should have a service defined. The Sybase product sqledit shows you how to enter the required information.

• If the name of the server is not specified when connecting to Sybase, Sybase searches for an environment variable called DSQUERY. This can be set within the environment variable section of a project. Doing this enables different projects to connect to different Sybase servers.

• When the Sybase precompiler runs, it can generate useful information. If the COBSQL directive VERBOSE is set, COBSQL displays as much of this information as it can.

• By using the COBSQL DISPLAY directive, the statistical information created by the Sybase precompiler can be seen on the screen.

• If the COBOL LIST directive is set, COBSQL passes any information it has collected from the precompiler to the COBOL checker for inclusion at the end of the COBOL listing.

• The Sybase precompiler accepts programs that do not contain any SQL statements, and produces an output file. This means that all of the programs in a project can be compiled successfully. However, it also means that the Sybase precompiler inserts all the Working-Storage items and COBOL support code that it would insert for a program that contains Sybase SQL statements into a non-Sybase program. This makes the program larger, and may effect its performance.

• With Sybase System 11 on Windows NT, the Sybase COBOL precompiler can be supplied separately to the Sybase System 11 Server. If this is the case, care must be taken when installing the Sybase COBOL precompiler as it is supplied with its own version of the Open Client support files and problems can arise if the two products have different versions of these files. In this situation, we strongly recommend that you contact Sybase support before proceeding with the installation of the Sybase System 11 COBOL precompiler.

• You should take particular care if you are installing the Sybase client onto a machine that has the Server installed onto it (or vice versa). Installing the Sybase client only does not cause any problems.

11.7.5 Informix Considerations

• Informix uses the eco, cob and mf2 file extensions. For CP to resolve copybooks and include statements correctly, use the following COBOL Compiler directives:

  copyext(eco,mf2,cob,cpy,cbl) osext(eco)

• Under UNIX, because COBSQL invokes the Informix precompiler, the INFORMIXCOB, INFORMIXCOBTYPE and INFORMIXCOBDIR environment variables all need to be setup before using COBSQL. For more information about these environment variables, refer to the Informix COBOL/ESQL Programmers Guide.

• Informix only produces error messages in its list files. Normally the error message will contain the line the error occured on. If COBSQL cannot locate the line number from the error message, it will not report the error.

• To run COBSQL correctly with Informix, the INFORMIXDIR environment variable should be set before using COBSQL.

SQL Option for DB2