Chapter 9: DB2

This chapter describes how you can access a DB2 database from a COBOL program which contains embedded SQL statements.

The DB2 External Compiler Module (ECM) is an integrated preprocessor provided with this system and designed to work closely with the Micro Focus COBOL Compiler. The DB2 ECM converts embedded SQL statements into the appropriate calls to DB2 database services.

Character Data

You must take care to use the correct data type for character data. For fixed-length character strings of 254 bytes or under, use CHAR data. For fixed-length character strings of more than 254 bytes, and for variable-length strings, use VARCHAR data. For further information see the section Character Data Types in the chapter Data Types.

Additional Data Types

In addition to the data types described in the chapter Data Types as being supported, DB2 supports the DECIMAL data type. This 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.

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:

level_number is within the range 1 to 48
sql_type is one of the new SQL data types BLOB, CLOB, DBCLOB, BLOB-FILE, CLOB-FILE, DBCLOB-FILE, BLOB-LOCATOR, CLOB-LOCATOR, DBCLOB-LOCATOR or TIMESTAMP. TIMESTAMPs are not new to DB2 V2 and are provided as a convenience by the DB2 ECM.
size may only be specified for BLOBs, CLOBs and DBCLOBs, and is mandatory. It may be qualified with K (Kilobytes), M (Megabytes) or G (Gigabytes).

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.

SQL syntax Equivalent COBOL syntax
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(29).

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.

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 COBOL 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
     end-if 
     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).

Extensions to Embedded SQL Support

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

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.

The DECLARE TABLE Statement

Statements of the form:

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

are permitted and are treated as comments.

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.

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

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.

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.

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.

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.

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 (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 is required to be declared as host variables.

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:

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:

INIT DB2 Directive Option

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. Previous versions of Micro Focus products 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 option 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 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.

If you use DB2 UDB 7.2 with fixpack 8 or later, and if you specify the PASS directive option with the INIT directive option, INIT does not generate blank host variables for the user ID and password to connect to the database with, since this version of DB2 UDB generates an SQL error when these variables are passed with spaces or low values on a CONNECT. This is a change in the behavior of the INIT option. Programs compiled with these values will not work. We recommend that you do not use these directives and instead use an SQL CONNECT statement in your program.

UDB-VERSION DB2 Directive Option

The DB2 ECM can use one of two sets of API calls, depending on the version of DB2 Universal Database you are using. The set of calls used for DB2 UDB Version 7.1 and later make use of the new facilities that IBM introduced in Version 7. In this version, IBM changed the size of the program identifier string (PID) from 40 bytes to 162 bytes. The PID is stored in each application program when it is compiled. You need to use the new PID structure if you use a COLLECTION-ID greater than 8 characters.

The DB2 ECM automatically tries to use the newer set of calls if you connect to a DB2 UDB server at Version 7.1 or later when you compile your program. However, you might want to use the newer set of calls in other circumstances, for example, if you are not using Version 7.1 client software, or if you are connected to another DB2 server via DB2 Connect. To do this, use the DB2 Compiler directive UDB-VERSION, which allows you to indicate which DB2 UDB version you want to use. Valid values are V2, V5, V6, V7 and V8. If you specify V7 or V8, the ECM makes the new calls. The default is DB2 V6. For example:

DB2(UDB-VERSION=V7)

If the DB2 ECM fails to find the new API, it displays the following message:

* critical errror - DB2Initialize API not found
* Try using directive DB2(UDB-VERSION=V6)
* to compile program

followed by the message:

** DB00010 DB2 rejected an unspecified option. This error 
** prevents SQL processing from continuing - further 
** EXEC SQL statements will be ignored

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.

Before you try to compile any SQL program, please make sure the following have been done:

Note: This is important since to compile a program, you must first connect to the database. If the connect fails, your program will not compile.

If you have done all of the above steps, and get a DB006 error ("Required DB software: db2agapi could not be found"), check the PATH statement that Net Express generates from a Net Express command prompt. The PATH gets modified by Net Express and the directory where DB2 is installed may be beyond 255 bytes. Modify your PATH statement to insure that ..\SQLLIB\BIN directory is closer to the beginning of the PATH statement so that required database DLL's required to compile the program can be loaded.

Before you can compile a SQL program, you must have been granted authorization. This is usually done by the DB2 Database Administrator. You must have one of the following:

The user also needs all table privileges required to compile any static SQL statement in the application. Note that privileges granted to groups are not used for authorization checking of static SQL statements. If a program fails to compile because of lack of authority on an SQL object, please contact your company's DB2 Database Administrator.

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.

If you are using the API calls introduced for DB2 UBD Version 7.1, the DB2 ECM calls an IBM module to validate them. If the DB2 ECM finds an error in the directive syntax, it displays a -104 SQLCODE error message:

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.

Compiling Programs that use a Remote DB2 Server

To compile a program that uses a remote DB2 server, you must first connect to that remote server. The DB2 ECM first attempts to connect to the database using the default values for the client workstation you logged on with. If the logon fails, the DB2 ECM will invoke the Micro Focus SQL Logon dialog in which you can then enter a logon ID and password for the database you are trying to compile your program against. The dialog box is shown below.

Micro Focus SQL Logon Dialog Box.

Figure 9-1: Micro Focus SQL Logon Dialog Box.

There is an option to save your logon ID and password in memory so that you do not need to be prompted the next time you try to compile a program using the same database. This information goes away the next time you re-boot your client machine or if you type the following command from a Net Express command prompt:

MFDAEMON CLOSE 

Automated Compiles

Having the graphical logon dialog appear might not be acceptable for automating compiles from a background process such as a command file. There is a way to supply the logon information by setting an environment variable and pointing the variable at a text file that contains the logon ID and password. To do this, set the environment variable SQLPASS.TXT to the name of the text file that contains the logon ID and password. For example:

SET SQLPASS.TXT=D:\BATCH.TXT 

Then in the file batch.txt, specify the logon ID and password in the format id.password. For example:

MyId.Mypassword

If the security system used to validate your logon ID and password is case sensitive, you need to specify id.password in the correct case in this text file.

Note: Specifying the logon and password in a text file does raise security concerns, so care should be used when implementing this facility.

DB2 Compiler Directive

You can specify the DB2 Compiler directive anywhere where you can specify any other Compiler directive. For example:

For information on setting the SQL Compiler directive from the IDE, see the topic To set SQL Compiler directive options .

Note: You cannot specify the SQL directive more than once, so must only specify it in one of the possible places.

DB2 Compiler Directive Options

The DB2 Compiler directive consists of a set of options. See the topic DB2 Compiler directive for more details. Available options are listed in the topic DB2 Compiler Directive Options.

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.

Note: This list assumes that you are using DB2 UDB V7.x. Some directives are only valid when using DB2 Connect with DB2 Universal Database. If you are using a different version of DB2 UDB, consult your IBM DB2 reference documentation under PRECOMPILE or BIND to see if the DB2 directive option is supported locally (DB2) or only by DB2 Connect (DRDA). If you are in any doubt, you can specify the directive and attempt to compile the program. If it is not supported, an error message will be issued.

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 might be slightly modified if you are using an older version of DB2 UDB. Hyphens are shown as underscores (_), and there are up to three additional characters which can be ignored at the end of the name. These changes are side effects of modifications made by the DB2 ECM to the SQL code. For more information see the entry for the QUALFIX option in the section DB2 Compiler Directive Options.

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

Creating Debug Files

If an error occurs when compiling a program that requires technical support, your support representative might ask you to provide additional debug files to help in determining the cause of the problem. You obtain these debug files by specifying extra DB2 Compiler directives. You might want to specifiy some of these directives to help in your own debugging efforts. The directives are:

Directive File created Information in the file
CHKECM(CTRACE) ecmtrace.txt This file contains pseudo COBOL code that shows the code generated to replace the EXEC SQL statements. This code is equivalent to output from the IBM DB2 COBOL precompiler.
CHKECM(TRACE) ecmtrace.txt This file contains detailed information as to what information is passed between the DB2 ECM and the Compiler. If an error occurs that generates invalid syntax, this file will be needed to help isolate where the problem occurred.
DB2(CTRACE) sqltrace.txt This file contains a detailed list of information passed to IBM Precompiler Services, and the results. This file is very useful if an error might involve a bug in the DB2 system software as well as the DB2 ECM.
ECMLIST program-name.lst This file is the standard COBOL list file, with pseudo COBOL code that shows the code generated to replace the EXEC SQL statements. You must also compile the program with the CHKECM(CTRACE) and LIST directives.

Linking

To link an application:

  1. Open the Net Express 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

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.

You can direct the DB2 ECM to store bind files in a directory other than the current source directory by specifying the enviroment variable HCOBND, for example

SET HCOBND=d:\production\binds

The directory you specify in the HCOBND environment variable is used to all bind files until you unset or reset the environment variable or unless you specify a specific bind file name using the DB2 BIND directive option, for example:

DB2(bind=d:\test\test1.bnd)

The DB2 BIND directive option overrides the HCOBND environment variable.


Copyright © 2006 Micro Focus (IP) Ltd. All rights reserved.