OpenESQL Assistant

COBSQL

Chapter 9: 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 NetExpress.

The DB2 External Compiler Module (ECM) is a new type of integrated preprocessor provided with NetExpress 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.

9.1 Data Types

In addition to the data types described in the chapter Data Types as being supported, DB2 also supports the following data types:

9.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.

9.1.2 Additional Data Types

All additional data types must be declared using 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 recognised 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).

9.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.

9.3 User Defined Functions

A program containing a reference to a User Defined Function (UDF) causes a separate module to be invoked; it contains user-supplied code which returns an appropriate value or values. The UDF code itself does not contain any SQL.

Running a program containing embedded SQL statements causes DB2 to be invoked and this in turn may invoke the UDF module. The declaration of the UDF should specify the language this module is written in. DB2 currently allows this to be C only, although on some platforms it is possible to write the module in COBOL. The following section demonstrates by use of example how this may be achieved. More complete descriptions of User Defined Functions and parameter descriptions are provided in the DB2 documentation.

User Defined Functions written in COBOL are not currently supported on UNIX.

Note: In a client/server configuration, the UDF module is invoked on the server and these restrictions apply to the server only - any client can access UDFs if the server is suitable.

The entry points in the UDF should be defined using C calling conventions. The following sample code segments show the use and definition of a simple UDF to calculate an exponent:

Program 1 declares the function to DB2. This program must be compiled and executed before program 2 can be compiled.

exec sql
   create function mfexp(integer, integer)
      returns integer
      fenced
      external name 'db2v2fun!mfexp'
      not variant
      no sql
      parameter style db2sql
      language cobol
      no external action
end-exec

Note the LANGUAGE COBOL clause. This is provided by Micro Focus as an extension to the DB2 syntax. It is equivalent to LANGUAGE C and, regardless of which is used, the called module should conform to the C calling convention. The EXTERNAL NAME clause specifies, in this case, that the called module is called db2v2fun (.dll or .dlw dependent on platform) and the entry point within this is mfexp.

Program 2 uses the UDF:

move 2 to hv-integer
move 3 to hv-integer-2
exec sql
   values (mfexp(:hv-integer, :hv-integer-2))
   into :hv-integer-3
end-exec

Program 3 is a pure COBOL program containing the UDF itself.

$set case
special-names.
   call-convention 0 is cc.
linkage section.
01  a pic s9(9) comp-5.
01  b pic s9(9) comp-5.
01  c pic s9(9) comp-5.
01  an pic s9(4) comp-5.
01  bn pic s9(4) comp-5.
01  cn pic s9(4) comp-5.
01  udf-sqlstate pic x(6).
01  udf-fname pic x(28).
01  udf-fspecname pic x(19).
01  udf-msgtext pic x(71).
procedure division cc.
   goback
   .
entry "mfexp" cc
   using a b c an bn cn
   udf-sqlstate
   udf-fname
   udf-fspecname
   udf-msgtext.
if an not = 0 or bn not = 0
   move -1 to cn
else
   compute c = a ** b
   move 0 to cn
goback
.

This module should be compiled to create a dynamically loadable executable (dll) and placed somewhere where the operating system can locate it (on the PATH).

Note: Entry-point names are case sensitive on all systems. Care should be exercised in matching case names, and the CASE Compiler directive should be specified (as per the $SET statement in the example program above).

9.4 Extensions to Embedded SQL Support

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

9.4.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 a copyfile can, including further EXEC SQL statements.

UNIX
On AIX, the filename is converted to lower case for the special case of sqlca, no matter how it is specified.

9.4.2 The DECLARE TABLE Statement

Statements of the form:

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

are permitted and are treated as comments.

9.4.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.

9.4.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

9.4.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). The redefinition can be omitted and the COBOL program can refer to the indicator variables using subscripting, if desired.

9.4.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.

9.4.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.

9.4.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.cpy (on Windows) or sqlca.cbl (on UNIX) 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. We recommend you define the entire SQLCA (this facility is provided for ANSI compatibility).

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 (PIC X(n), where n can be any legal value; if the message does not fit into the data item it is truncated).

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

9.4.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 it is used within an OO program. This means that if you want the functionality of the DB2(INIT=PROT) Compiler directive, you will need to 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.

9.5 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, Micro Focus 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. This database protection can be selected by specifying 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.

9.6 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 below.

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.

9.6.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)

9.6.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 is 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

9.6.1.2 DRDA DB2 Directives

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

9.7 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

9.8 Linking

To link an application:

1. Open the NetExpress project and set the Type of Build to Generic Release Build.

2. Right click on the .exe or .dll file.

3. Select Build Settings ... and then click on the Link tab.

4. Set the Category to Advanced.

5. In the Link with these LIB's edit box enter:

   db2api.lib

9.9 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.

9.10 Publishing your DB2 Applications on UNIX

If you want to publish your DB2 application on UNIX you must:

1. Configure your login environment to set the COBOPT environment variable to the name of the database parameter file:

   COBOPT=/usr/lpp/db2_vv_rr_mmmm/lib/db2mkrts.args
   export COBOPT

   where the values of vv, rr and mmmm represent the version, release and modification level of the DB2/6000 system installed on your machine.

2. Edit the file named in the COBOPT environment variable by removing the -vdd option from the initial line of the file.

3. From the directory $COBDIR/src/sql enter:

   mkrts sqlinit.o
   cp rts32 $COBDIR

OpenESQL Assistant

COBSQL