OpenESQL

COBSQL

Chapter 8: DB2

This chapter describes how you can access a DB2 database from a COBOL program which contains embedded SQL statements and has been compiled and linked using Server Express.

The DB2 External Compiler Module (ECM) is a new type of integrated preprocessor provided with Server Express and designed to work more closely with the Micro Focus COBOL Compiler. The DB2 ECM converts embedded SQL statements into the appropriate calls to DB2 database services.

8.1 Data Types

In addition to the data types described in the chapter Data Types as being supported, DB2 also supports DECIMAL data types and additional data types. These are described in more detail below.

8.1.1 DECIMAL

The DECIMAL data type describes a packed-decimal item, with or without a decimal point. In COBOL, such items can be declared either as COMP-3 or as PACKED-DECIMAL.

8.1.2 Additional Data Types

To declare all additional data types, use SQL syntax of the form:

>--level_number--name--+------------+-SQL-+-----------+-->
                       |            |     |           |
                       +-USAGE-+----+     +-TYPE-+----+
                                 |    |          |    |
                                 +-IS-+          +-IS-+

>--sql_type--+--------------+--><
             |              |
             +--(--size--)--+

where

VALUE clauses are not permitted on the new SQL data types.

Depending on the sql_type specified, the actual data created may be an elementary or group item. The names of elements in the group item are generated automatically.

The table below shows the structure of the data items created using SQL syntax by showing the equivalent native COBOL definition. Note that although the same data is created in each case, the items must be declared using the SQL syntax in order to be recognized as acceptable host variables by the DB2 ECM. (This is because the COBOL definitions are ambiguous: various of the new SQL types, and existing group items which are expanded to individual host variables, are indistinguishable). All previously existing data types continue to be declared using normal COBOL syntax. The only exception to this rule is TIMESTAMP, which may be declared using either form.

01  MY-BLOB SQL BLOB(125M).

01  MY-BLOB.
   49  MY-BLOB-LENGTH PIC S9(9) COMP-5.
   49  MY-BLOB-DATA   PIC X(131072000).

03  A SQL CLOB(3K).

03  A.
   49  A-LENGTH PIC S9(9) COMP-5.
   49  A-DATA   PIC X(3072).

03  HV SQL DBCLOB(125).

03  HV.
   49  HV-LENGTH PIC S9(9) COMP-5.
   49  HV-DATA   PIC G(125).

01  B SQL BLOB-LOCATOR.

01  B PIC S9(9) COMP-5.

01  C SQL CLOB-FILE.

01  C.
   49  C-NAME-LENGTH  PIC S9(9) COMP-5.
   49  C-DATA-LENGTH  PIC S9(9) COMP-5.
   49  C-FILE-OPTIONS PIC S9(9) COMP-5.
   49  C-NAME         PIC X(255).

01  TS SQL TIMESTAMP.

01  TS PIC X(26).

8.2 Compound SQL

Compound SQL is supported, including the extended form now available in DB2 V2. Note that incomplete Compound SQL statements are detected by the DB2 ECM and cause an error to be produced. However, DB2 may not always recover from this condition and valid SQL statements later in the program source may generate additional errors.

8.3 Extensions to Embedded SQL Support

This section discusses Micro Focus extensions to the embedded SQL support.

8.3.1 The INCLUDE Statement

Statements of the form:

exec sql
   include filename
end-exec

are permitted and are processed in exactly the same way as the statement:

copy filename

The included file can contain any COBOL statements that are permitted for a copyfile, including further EXEC SQL statements.

On AIX, the filename is converted to lower case for the special case of SQLCA and SQLDA, no matter how it is specified.

8.3.2 The DECLARE TABLE Statement

Statements of the form:

exec sql
   DECLARE table-name TABLE
   ...
end-exec

are permitted and are treated as comments.

8.3.3 Integer Host Variables

The embedded SQL support requires the format of integers to be USAGE COMP-5. For your convenience, the DB2 ECM also allows host variables to use USAGE COMP, COMP-4 and BINARY and generates additional code to convert the format. The most efficient code is generated when COMP-5 is used.

8.3.4 Qualified Host Variables

Host variables can be qualified using DB2 for MVS compatible syntax.

For example, suppose you have defined some host variables as follows:

01 block-1.
   03 hostvar pic s9(4) comp-5.
01 block-2.
   03 hostvar pic s9(4) comp-5.

You can qualify which instance of hostvar to use with syntax of the form:

exec sql
   fetch s2 into :block-1.hostvar
end-exec

8.3.5 Host Variable Groups and Indicator Arrays

When host variables are declared in a group item, an SQL statement which needs to refer to each of these variables in turn can be abbreviated by referring instead to the group-name. If you need to associate indicator variables with these host variables, define a table of indicator variables with as many instances as there are host variables, and reference this table (the item with the OCCURS clause, not a group item containing it).

For example, suppose you have defined some host variables as follows:

01  host-structure.
   03 sumh           pic s9(9) comp-5.
   03 avgh           pic s9(9) comp-5.
   03 minh           pic s9(9) comp-5.
   03 maxh           pic s9(9) comp-5.
   03 varchar.
      49 varchar-l   pic s9(4) comp.
      49 varchar-d   pic x(1000).
 01  indicator-table.
     03 indic          pic s9(4) comp-5 occurs 4.
 01  redefines indicator-table.
     03 indic1         pic s9(4) comp-5.
     03 indic2         pic s9(4) comp-5.
     03 indic3         pic s9(4) comp-5.
     03 indic4         pic s9(4) comp-5.

In such an example, the procedural statement:

exec sql fetch s3 into
  :host-structure:indic
end-exec

is equivalent to:

exec sql fetch s3 into
  :sumh:indic1, :avgh:indic2, :minh:indic3,
  :maxh:indic4, :varchar
end-exec

The four declared indicator variables are allocated to the first four host variables. If five or more had been declared, all five host variables would have an associated indicator variable.

The table of indicator variables is redefined only to show the equivalent SQL statement; subscripting is not allowed in SQL statements. If desired, you can omit the redefinition and use subscripting to refer the COBOL program to the indicator variables.

8.3.6 The NOT Operator (¬)

DB2 allows the operators ¬=, ¬> and ¬<. These are mapped to <>, <= and >=. The character representation of the NOT operator varies from system to system, so you can define it using the NOT option of the DB2 Compiler directive.

8.3.7 The Concat Operator (|)

In some countries, the symbol used for the concat operator is not the ASCII character (|). The DB2 ECM enables you to specify a different ASCII character for the concat operator via the CONCAT option of the DB2 Compiler directive.

8.3.8 SQL Communications Area

After any SQL statement has executed, important information is returned to the program in an area called the SQL Communications Area (SQLCA). The SQL Communications Area is usually included in your program using the statement:

exec sql include sqlca end-exec

This causes the source file sqlca.cbl to be included in your source code. This source file, supplied with the DB2 ECM, contains a COBOL definition of the SQLCA.

If you do not include this statement, the DB2 ECM automatically allocates an area, but this area is not addressable in your program. However, if you declare either or both of SQLCODE and SQLSTATE, the DB2 ECM generates code to copy the corresponding fields in the SQLCA area to the user-defined fields after each EXEC SQL statement.

Tip: For ANSI compatibility, we recommend you define the entire SQLCA.

After any non-zero condition in SQLCODE, the DB2 ECM updates the contents of the MFSQLMESSAGETEXT data item with a description of the exception condition, provided it has been defined. If it is, it must be declared as a character data item, that is PIC X(n), where n can be any legal value. If the message does not fit into the data item, then the message is truncated.

None of SQLCA, SQLCODE, SQLSTATE and MFSQLMESSAGETEXT are required to be declared as host variables.

8.3.9 Support for Object Oriented COBOL Syntax

The DB2 ECM has been enhanced to work with Object Oriented COBOL syntax (OO programs). There are, however, a couple of restrictions that you should be aware of:

• The INIT option of the DB2 Compiler directive is disabled if used within an OO program. Therefore, to obtain the functionality of the DB2(INIT=PROT) Compiler directive, include a non-OO module and compile it with this directive.

• If you use an EXEC SQL WHENEVER statement within a METHOD, any additional METHODs coded in the same CLASS that have SQL statements in them need to have the section that is referenced in the preceding WHENEVER statement defined. Not doing this results in a compilation error, indicating that the section has not been defined. You can get around this restriction by defining another EXEC SQL WHENEVER statement.

8.3.10 Support for Nested COBOL programs

The DB2 ECM allows you to work with nested COBOL programs.

By default, DB2 interface code is generated for every nested COBOL program. To avoid generating DB2 interface code for each nested program, use the DB2 directive IGNORE-NESTED. To use the IGNORE-NESTED directive properly, there is one restriction that you should be aware of:

• You must specify the PROGRAM-ID statement in the program for which you wish DB2 interface code to be generated. Otherwise, a compile error occurs.

8.4 DB2 INIT Directive

Early versions of DB2 did not provide any means in SQL syntax to connect to a database at execution time; instead you were expected to code calls to the appropriate SQL API routines. In previous versions of Micro Focus products, MERANT included a SQLINIT or SQLINI2 module to perform the CONNECT function. These routines are no longer provided. Instead, the DB2 ECM will now generate the appropriate CONNECT statement, depending on the setting of the INIT option of the DB2 Compiler directive.

The INIT directive has the additional option of ensuring that the database connection is correctly closed down, even if the application is abnormally terminated, to avoid possible database corruption. If the application is abnormally terminated, all changes since the last COMMIT are rolled back. To select this level of database protection, specify the option INIT=PROT on the DB2 Compiler directive.

The INIT option must only be set once for an application. SQL programs called by other SQL programs should not have the INIT option set. Alternatively, you can specify the INIT option for the first SQL program to be executed in a run unit. Compiling more than one module in an application with the INIT option may cause your program to terminate abnormally.

8.5 Compiling

Compiling your SQL program with the COBOL Compiler is logically equivalent to two steps: precompiling to change SQL lines into host language statements, and then compiling the resulting source. These two steps actually occur in a single process, which is performed by the COBOL Compiler in conjunction with the DB2 ECM.

You use the DB2 Compiler directive to give the DB2 ECM information such as the fact that you are using SQL, and which database you are using. See the section DB2 Compiler Directive in this chapter.

Normally, programs containing embedded SQL are compiled in the same way as non-SQL programs, except that the DB2 Compiler directive is required. Special action is required only when creating an executable (binary) file when additional modules need to be linked in. Programs containing SQL code can be animated like any other program. You can examine host variables inside SQL statements as they are regular COBOL data items.

8.5.1 DB2 Compiler Directive

You can specify options for the DB2 Compiler directive using the $SET statement in your program.

For example:

$SET DB2(INIT=PROT BIND COLLECTION=MYSCHEMA)

8.5.1.1 Default Values

Compiler directives have a default value which is used if no other value is specified. This also applies to all existing DB2 directive options. Many of the options are passed straight to DB2 at compilation time and the Compiler default is used when no other value is specified. In these cases, however, the suitability of, and default values for these options are dependent on the DB2 configuration, notably whether it is connected to a DRDA server via DDCS. Because of this, the default Compiler setting of these options is "not set". This means that no value is passed to DB2 and the default value (if applicable) is determined by DB2 itself. Consult your IBM DB2 reference documentation for these values.

The table below lists the DB2 Compiler directive options. The default value is highlighted and underlined.

"SYSIBM","SYS FUN",USER

8.5.1.2 DRDA DB2 Directives

The following options are only valid if you are connected to a DRDA Server:

8.6 Error Codes

Error conditions are returned at compilation time as a number and explanation. Further details of these messages are given in the documentation supplied with your database system. Messages referencing host variables show slightly modified names: hyphens are shown as underscores (_), and there are up to three additional characters at the end of the name which can be ignored. These changes are side-effects of modifications made by the DB2 ECM to the SQL code.

Error conditions at run time are indicated by non-zero values in SQLCODE. Explanatory text is placed in the MFSQLMESSAGETEXT data item if defined; see the section SQL Communications Area above for further details about this data item.

For example:

801-S
**    External Compiler Module message
**    SQ0100 SQL1032N  No start database manager command was issued.
**    SQLSTATE=57019

8.7 Linking and Running

If your DB2 application is to run as intermediate or generated code, you do not need to link.

If you wish to link your DB2 application as an executable or callable shared object, however, you must include the DB2 libraries.

If a DB2 application is to be linked as an executable or callable shared object, you must tell the linker about the location and names of the DB2 libraries. To do so, use the -L and -l cob flags.

By default, the Checker inserts a pre-load call to cobdb2, which is required when running intermediate or generated code. If you wish to link your application as an executable or callable shared object, this pre-load is not required. Therefore, exclude the pre-load in one of the following ways:

• Specify the Checker directive DB2(NOPRE). For example:

  $ cob -xv -C "DB2(NOPRE)" db2prog.cbl -Llocation of
  db2 libraries -ldb2 library -ldb2 library …

• Use the -d cob flag. For example:

  $ cob -xv -dcobdb2 db2prog.cbl -Llocation of db2 libraries
      -ldb2 library -ldb2 library …

For example, the command line to use for linking the supplied example program db2test1.cbl under AIX is:

$ cob -xvC "DB2(NOPRE)" db2test1.cbl
    -L/usr/lpp/db2_05_00/lib -ldb2 -ldb2gmf

8.8 Binding

If you use the NOACCESS option of the DB2 Compiler directive or intend to execute the application on a machine other than the one it was compiled on, bind the application to a particular database before execution. In this case, you should use the BIND option to create a bind file that can then be used to bind the program to the database using the DB2 BIND command. For details on doing this, see the documentation supplied with your SQL system.

OpenESQL

COBSQL