PreviousIntroduction to Relational Database Handling SQL, DB2/2 and 32-bit COBOLNext

Chapter 24: Embedded SQL Using DB2

Programs can be created with this COBOL system to use embedded Structured Query Language (embedded SQL) to access database systems that comply with IBM's SQL Precompiler Services Architecture. There are two such systems available - IBM DB2 and Microsoft SQL Server. To use SQL Server you need the add-on product Micro Focus Embedded SQL Toolkit for Microsoft SQL Server (contact your Micro Focus sales representative for details).

This chapter describes how to use IBM DB2 with this COBOL system and how to compile and link COBOL programs containing SQL statements. It also describes the sample program supplied with this system for use with supported IBM database engines.

24.1 Supported DB2 Systems

The following DB2 systems are supported:

These are referred to generically as DB2 throughout this chapter.

This COBOL system works transparently with IBM Distributed Database Connection Services for access to other database systems.

This COBOL system automatically detects the version of DB2 in use. The various combinations of DB2 and COBOL system are treated differently; where possible this is handled automatically by the COBOL system, although how you link a DB2 application depends on the combination of DB2 and COBOL systems you are using. See the section Linking for further details.

This chapter does not document SQL syntax, error messages, or how to use SQL outside of the COBOL environment. For details of these, refer to the documentation supplied with your database system.

OS/2:
In DB2 V1 and later, DB2 is a 32-bit system and more compatible with 32-bit COBOL for OS/2 V2 although you can use it with the 16-bit COBOL system. Additional functionality offered by future releases of DB2, however, may not be available with the 16-bit COBOL system - we therefore recommend that you use the 32-bit COBOL system.

You can move from the 16-bit COBOL system to a 32-bit COBOL system with no source code changes, although you will need to re-compile your program and link your executable applications differently. User-coded calls to DB2 are documented in the DB2 manuals in a form appropriate for the 16-bit COBOL system. The 32-bit COBOL system accepts calls made in this way, although we recommend that you use the native format (which is also compatible with UNIX) for new code. For a detailed description of the issues, see the chapter SQL, DB2/2 and 32-bit COBOL.

24.2 Features of the Micro Focus Support

DB2 supplies a precompiler that takes as input a source program containing embedded SQL and produces a new source file containing only COBOL code. The Micro Focus support offers the following advantages over this:


Note: Greater compatibility is provided by the add-on product Micro Focus Host Compatibility Option, which works with and extends the Micro Focus support documented here. Contact your Micro Focus sales representative for details.


These extensions to the SQL support are documented fully in the following sections.

24.3 Installation

Before using the Micro Focus SQL database interface, you must install one of the database systems listed in the section Supported SQL Systems. See the documentation supplied with that system for installation details.

When installing the COBOL system, select the SQL Support option. This causes all support files to be included.

16-bit Windows:
DB2 requires a larger stack than COBOL provides by default. It is strongly recommended that you include the following line in your autoexec.bat file:

set cobsw=/s32764

16-bit Windows:
For DB2 V2 only, you should edit your autoexec.bat file to include the following line:

set mfdb2ver=2.1

UNIX:
You must additionally 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.

For DB2 V2 only, you should add:

MFDB2VER=2.1
export MFDB2VER

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

From the directory $COBDIR/src/sql enter:

sh mkrts sqlinit.o 
cp rts32 $COBDIR

24.4 SQL in COBOL Programs

This section contains details of COBOL specific features. For details on the SQL syntax and system specifications of your SQL system, see the documentation supplied by the database vendor.

Programs containing SQL syntax must be compiled with the SQL Compiler directive set. This directive has a number of optional parameters to configure the DB2 engine. See the section The SQL Compiler Directive for full details.

24.4.1 The EXEC SQL Statement

SQL statements are embedded in COBOL using the EXEC SQL and END-EXEC delimiters, in the form:

exec sql SQL-statement end-exec

The EXEC SQL statement can be broken over as many lines as necessary, following the normal COBOL rules for continuation. Between the EXEC SQL and END-EXEC statements, you can only code SQL statements. You must not include any COBOL statements.

Example

The following is an example of a complete COBOL program with embedded SQL commands:

 working-storage section.
 exec sql begin declare section end-exec
* Examples of host variables declared in a DECLARE section 
 01 filler.
     03 cust-no pic 9(4) packed-decimal. 
     03 cust pic X(20). 
 exec sql end declare section end-exec 
* Example of host variable not declared in a DECLARE section 
 01 house-no pic 9(4) comp. 
 exec sql include sqlca end-exec

 procedure  division.
     exec sql declare cust cursor for 
         select custno, custname, hseno 
         from region where postcode = "RG1" 
     end-exec

     exec sql open cust end-exec

     perform until sqlcode not = 0 
         exec sql
             fetch cust into :cust-no, :cust, :house-no 
         end-exec
         display "customer-no: " cust-no 
                 "  customer-name: " cust 
                 "  house-no: " house-no
     end-perform
     exec sql close cust end-exec

     stop run.

24.4.2 Host Variables

Host variables are data items defined in a COBOL program to communicate values to and from the SQL system. Some are defined using ordinary COBOL syntax. DB2 V2 and above supports Large OBjects (LOBs) and these are defined using SQL syntax. Host variables can be defined in the File Section, Working-Storage Section, Local-Storage Section or Linkage Section. They can be any level number, from 1 through 48 (level-49 is reserved for VARCHAR elements).

The embedded SQL support specifies that integers have the format of USAGE COMP-5. A Micro Focus extension allows USAGE COMP, COMP-4 and BINARY as described in the section Extensions to Embedded SQL Support.

In an EXEC SQL statement, host variables must be prefaced with a colon (:) to identify them to the Compiler.

The ANSI definition of SQL requires that the declaration of host variables must be preceded by the statement:

exec sql begin declare section end-exec

and followed by:

exec sql end declare section end-exec

although with Micro Focus COBOL you can omit these statements.

Permissible database data-types and their corresponding definitions are described in the following sections.

24.4.2.1 Host Variables Declared Using COBOL Syntax

24.4.2.1.1 Small Integer

A small integer (SMALLINT) is a two-byte binary item, which can be represented in COBOL with USAGE BINARY, COMP, COMP-4, or COMP-5. If a value larger than 32,767 is defined as unsigned, your database system treats it as negative.

Examples

 01 shortint1       pic s9(4) comp.
 01 . . .
     03 shortint2   pic s9(4) comp-5.
     . . . 
     03 shortint3   pic x(2) comp-5.
     . . .
     03 shortint4   pic s9(4) comp-4.
24.4.2.1.2 Large Integer

A large integer (INTEGER) is like a small integer except that it is a four-byte item. If a value larger than 2,147,483,647 is unsigned, your database system treats it as negative.

Examples

 01 longint1        pic s9(9) comp.
 . . .
 01 . . .
     03 longint2    pic s9(9) comp-5.
     . . .
     03 longint3    pic x(4) comp-5.
24.4.2.1.3 Varying Length Character Strings

Varying length character strings (VARCHAR and LONG VARCHAR) are group items containing just two elementary items, which must both have a level number of 49. The first of these, which is a small integer, is used to hold the effective length of the item. The second is PIC X(n), where n is any integer up to 32,700, and holds the actual data. The item is classified as VARCHAR if n is less than or equal to 4,000: otherwise, it is classified as LONG VARCHAR.

Example

 01 varchar1.
         49 varchar1-len        pic 9(4) comp-5.
         49 varchar1-data       pic x(100).
 . . .
 01 . . .
     03 longvarchar1.
         49 longvarchar1-len    pic 9(4) comp.
         49 longvarchar1-data   pic x(20000).
24.4.2.1.4 Fixed Length Character Strings

A fixed length character string (CHAR) is an alphanumeric item having a maximum length of 254 characters.

Example

 01 char-field1        pic x(3). 
 . . . 
 01 . . .
     03 char-field2    pic x(254).
24.4.2.1.5 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.

Example

 01 packed1        pic s9(8)v99 usage comp-3.
 . . . 
 01 . . .
   48  packed2     pic s9(3) usage packed-decimal.
24.4.2.1.6 Float

The FLOAT data-type describes a double-precision floating-point item. In COBOL such items are declared as COMP-2.

Example

 01 float2 usage comp-2.

24.4.2.2 Host Variables Declared Using SQL Syntax

The general format for the declaration of a host variable using SQL syntax is:

level_number  name  sql_type(size)
Parameters:

level_number Within the range 1 to 48.
name The name of the host variable.
sql_type One of the new SQL data types BLOB, CLOB, DBCLOB, BLOB-FILE, CLOB-FILE, DBCLOB-FILE, BLOB-LOCATOR, CLOB-LOCATOR, DBCLOB-LOCATOR or TIMESTAMP.
size Can only be specified for BLOBs, CLOBs and DBCLOBs, and is mandatory. It can be qualified with K, M or G, representing Kilobyte, Megabyte and Gigabyte respectively.

VALUE clauses are not permitted on SQL data declarations.

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

24.4.3 Compound SQL

Incomplete Compound SQL statements are detected by the Compiler and generate an error. However, DB2 may not always recover from this condition and valid SQL statements later in the program may generate additional errors.

24.4.4 User Defined Functions and Stored Procedures

User Defined Functions and Stored Procedures are modules invoked by DB2. In a client/server environment, the modules execute on the server. These modules are new to DB2 V2. Their use and implementation is documented in the DB2 manuals but additional information is provided here.

24.4.4.1 User Defined Functions

DB2 expects User Defined Functions (UDFs) to be written in 'C'. It is possible to write UDFs in COBOL on the 32-bit COBOL system but they are not supported on UNIX servers or on 16-bit OS/2 servers. The entry points in the UDF should be defined using 'C' calling conventions.

UDFs should be declared to DB2 before any program that uses them can be compiled, in the same way that tables in a database must be created before a program that uses them can be compiled.

Example

The following code segments show the definition and use 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

Notice the LANGUAGE COBOL clause. This is provided as a Micro Focus 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 contains code which 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 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/.dlw) and placed where the operating system can find it, for example, in a directory referenced by PATH or LIBPATH.

On all platforms entry-point names are case sensitive; care should be exercised in matching case names, and the CASE Compiler directive should be specified. See the $SET statement in the above program.

24.4.4.2 Stored Procedures

DB2 is unable to invoke stored procedures on OS/2 that have been created using the 16-bit COBOL system. They are supported in all other configurations.

Examples of stored procedures are supplied with DB2 and are compiled and linked as normal. The SQL directive option INIT should be set to NOINIT (the default).

UNIX:
On UNIX, the DB2 example inpsrv.cbl can be compiled as follows:

cob -xcC "sql(db==sample)" inpsrv.cbl
cob -x -o inpsrv inpsrv.o -Q -e inpsrv \
   -Q -bE:inpsrv.exp \
   -Q -bI:/usr/lpp/db2_02_01/lib/db2g.imp \
   -B static -L/usr/lpp/db2_02_01/lib

24.5 Extensions to Embedded SQL Support

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

24.5.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 file named by filename 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

24.5.2 The DECLARE TABLE Statement

Statements of the form:

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

are permitted and are treated as comments.

24.5.3 Integer Host Variables

The embedded SQL support requires the format of integers to be USAGE COMP-5. For your convenience, the Compiler 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.

24.5.4 Undeclared Host Variables

If you have not previously declared a data item, you can still use it as a host variable by using the following syntax:

exec sql begin declare section end-exec

and:

exec sql end declare section end-exec

The Compiler automatically declares host variables to the database system as required.

The DB2 option of the SQL directive must be set in order to use this facility.

24.5.5 Qualified Host Variables

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

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

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

The DB2 option of the SQL directive must be set to enable the use of group host variables and group indicator arrays.

Example

Suppose variables are defined 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.
 exec sql end declare section end-exec

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.

24.5.7 The NOT Operator (¬)

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

24.5.8 SQL Communications Area

After any SQL statement has executed, important information is returned to the program in an area called the SQL Communication Area (SQLCA). There is a status indicator in a field of the SQLCA called SQLCODE and, if your system supports it, SQLSTATE. SQLCODE is a numeric data item (PIC S9(9) COMP-5 or COMP) which contains the result of the SQL operation. A value of zero means that the statement has executed successfully; a positive value means the statement did execute, but that some exceptional condition occurred; and a negative value means that an error occurred and the statement did not complete successfully. SQLSTATE is defined as a five-character data item PIC X(5) and is provided for compatibility with the ANSI SQL2 database language definition.

The SQL Communication Area is usually included in your program by the statement:

exec sql include sqlca end-exec

This causes the source file sqlca.cpy (on Windows and OS/2) or sqlca.cbl (on UNIX), which contains a COBOL definition of the SQLCA, to be included in your source code.

If you do not include this statement, the Compiler 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 Compiler 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).

If your selected COBOL dialect does not support COMP-5 data items (for example mainframe emulation), you can edit the SQLCA source file and change the COMP-5 items to COMP. However, this can result in larger and slower object code.

After any non-zero condition in SQLCODE, the Compiler 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.

24.5.9 The Sqlinit and sqlini2 Modules

Early versions of DB2 do not provide any means, using SQL syntax, to connect to a database at execution time; instead you are expected to code calls to the appropriate SQL API routines. This COBOL system provides modules which contain the required calls, Sqlinit and sqlini2. If the INIT option of the SQL directive is set to S or X a call to one of these modules is automatically embedded in the application by default. These modules can be used even if SQL syntax is available.

The Sqlinit and sqlini2 modules also ensure that the database connection is closed down correctly even if the application terminates abnormally. All changes since the last COMMIT are rolled back. This database protection can be selected without connection to a database by specifying the option INIT=PROT on the SQL directive.

The Sqlinit or sqlini2 module must only be called once for an application. SQL programs called by other SQL programs should not have the INIT option set. Invoking the module more than once may cause your program to terminate abnormally.

When using the Micro Focus add-on products, CICS Option, IMS Option or COBOL/SQL Transparency (CST), you must specify NOINIT as these products provide their own support - refer to the product documentation provided.

Windows and OS/2:
When you specify the INIT option, you must include one of sqlinit.obj or sqlini2.obj when the application is linked. When running or animating a program, sqlinit.gnt or sqlini2.gnt in the utils.lbr library is automatically located by the COBOL system. With DB2/2 V1.2 in a client/server configuration and DB2 V2, the sqlini2.obj and sqlini2.gnt files are used. On previous versions of DB2, the sqlinit.obj and sqlinit.gnt files are used.

UNIX:
When you specify the INIT option, you must include $COBDIR/src/sql/sqlinit.o on the cob command line if you are creating an executable module. When running or animating the program, the sqlinit.gnt file is used. This file is contained in the directory $COBDIR and is automatically located by the COBOL system.

24.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 system.

You use the SQL Compiler directive to give the COBOL system information such as the fact that you are using SQL, and which database you are using. This directive is summarized later in this chapter.

Normally, programs containing embedded SQL are compiled in the same way as non-SQL programs, except that the SQL Compiler directive is required. Special action is only required when creating an executable file if additional modules need to be linked in. Programs containing SQL code can be animated like any other program and you can examine host variables inside SQL statements.

Windows and OS/2:
On Windows and OS/2, if you are not creating an executable file (that is one which can be animated or run), set the PRE option of the SQL directive to either PRE=ALL (the default value) or PRE=REQ. These values cause a module to be called to preload the required database engine support calls. If your program contains only EXEC SQL statements (and not direct API calls to the database engine) select the PRE=REQ setting which can reduce the program's memory requirement and startup time. This option can affect the stack size that is created; see the section Linking for details.

UNIX:
On UNIX, the PRE option is not required and has no effect.

24.6.1 The SQL Compiler Directive

The SQL Compiler directive enables you to use EXEC SQL statements in your program, and specifies a number of options as to how your program is to process EXEC SQL statements, and how it is to handle the database.

Syntax:
 >>-.---.-.----SQL-.----------------------..><
    .-/-+ |        |   +-----.---.-----+  ||
          |        |   |     .-,-+     |  ||
          |        |   ¡               |  || 
          |        .-(.-option=setting-.)-+|
          |           |                |   | 
          |           .-NO-option------+   |
          .-NO-SQL-------------------------+
Parameters:

option One of the SQL options (see below)
setting The setting for the option

Properties:

Default: NOSQL
Phase: Syntax check
Environment: All
$SET: Initial
Comments:

To process EXEC SQL statements, you must specify the SQL directive. If you want to have EXEC SQL statements processed as standard EXEC syntax (see your Language Reference for details), you must explicitly specify the directive NOSQL.

In previous versions of Micro Focus COBOL each of the options specified here was a separate directive; except the DBMAN option which was part of the SQL directive, and the INIT option which was a combination of the directives SQLINIT and SQLPROT. All these directives work in this system, but they will be removed in a future version. We recommend that you change to the new format as soon as possible.

Examples:

Each of the following lines has the same effect:

SQL(FORMAT=LOC, DBMAN=IBM, DB=SAMPLE, NOBIND) 
SQL(FORMAT=LOC DBMAN=IBM DB=SAMPLE NOBIND) 
SQL(FORMAT=LOC IBM DB=SAMPLE NOBIND) 
SQL(FORMAT=LOC) SQL(IBM) SQL(DB=SAMPLE) SQL(NOBIND)


SQL Option - ACCESS

Specifies the name of the access plan (package) to be created by precompiler services and stored in the database.

Syntax:
 >>--.----.---ACCESS=--access-plan---><
     .-NO-+
Synonym:

PACKAGE

Parameters:

access-plan The root of a filename, or a null string.

Properties:

Default: ACCESS
Comments:

The name of the access plan is not a filename and so must not have an extension.

If you specify ACCESS, the access plan is given the same name as the compiled program, without path or extension.

The name of the access plan is always folded to upper case as required by DDCS/2.

If no access plan is to be created, use NOACCESS.


SQL Option - ACTION

Indicates whether the package can be added or replaced.

Only valid with DB2 V2 and above.

Syntax:
 >>--.----.---ACTION=--setting-------><
     .-NO-+
Parameters:

setting Refer to your DB2 documentation.

Properties:

Default: NOACTION
Comments:

If NOACTION is specified, the DB2 defaults apply; otherwise, the possible values of setting are:

ADD
REPLACE


SQL Option - BIND

Specifies the name of the bind file to be created by precompiler services.

Syntax:
 >>--.----.---BIND=--bindfile-name------><
     .-NO-+
Synonym:

BINDFILE

Parameters:

bindfile-name A full file specification or a null string.

Properties:

Default: NOBIND
Comments:

If you specify BIND, the bind file is given the same name as the compiled program, except that the file extension becomes .bnd.

On OS/2 and Windows, the bindfile-name is always folded to upper case. This is required by DDCS/2 and is transparent in all other cases.

If no bind file is to be created, use NOBIND.


SQL Option - BLOCK

Specifies the record blocking mode to be used on plan creation.

Syntax:
 >>---BLOCK=--block-mode----------------><
 
Synonym:

BLOCKING

Parameters:

block-mode The blocking mode to be used.

Properties:

Default: BLOCK=UNAMBIG
Comments:

The possible values of block-mode are:

ALL Blocking occurs for fetch-only cursors. Ambiguous cursors are treated as fetch-only.
UNAMBIG Blocking occurs for fetch-only cursors. Ambiguous cursors are treated as updatable.
NO Blocking does not occur.


SQL Option - CCSIDG

Specifies the default CCSID to use for double-byte characters in character column definitions.

Only valid with DB2 V2 and above.

Syntax:
 >>-.----.---CCSIDG=--integer-------><
    .-NO-+
Parameters:

integer Refer to your DB2 documentation.

Properties:

Default: NOCCSIDG
Comments:

If NOCCSIDG is specified, the default DB2 value applies.


SQL Option - CCSIDM

Specifies the default CCSID to use for mixed byte characters in character column definitions.

Only valid with DB2 V2 and above.

Syntax:
 >>-.----.---CCSIDM=--integer-------><
    .-NO-+
Parameters:

integer Refer to your DB2 documentation.

Properties:

Default: NOCCSIDM
Comments:

If NOCCSIDM is specified, the default DB2 value applies.


SQL Option -CCSIDS

Specifies the default CCSID to use for single byte characters in character column definitions.

Only valid with DB2 V2 and above.

Syntax:
 >>-.----.---CCSIDS=--integer-------><
    .-NO-+
Parameters:

integer Refer to your DB2 documentation.

Properties:

Default: NOCCSIDS
Comments:

If NOCCSIDS is specified, the default DB2 value applies.


SQL Option - CHARSUB

Specifies the default character subtype to use in column definitions.

Only valid with DB2 V2 and above.

Syntax:
 >>-.----.---CHARSUB=--setting----------><
    .-NO-+
Parameters:

setting Refer to your DB2 documentation.

Properties:

Default: NOCHARSUB
Comments:

If NOCHARSUB is specified, the DB2 default applies; otherwise, the possible values of setting are:

BIT
DEFAULT
MIXED
SBCS


SQL Option - COLLECTION

Specifies the collection identifier of the package.

Only valid with DB2 V2 and above.

Syntax:
 >>-.----.---COLLECTION=--setting-------><
    .-NO-+
Parameters:

setting An eight character alphanumeric. Refer to your DB2 documentation.

Properties:

Default: NOCOLLECTION
Comments:

If NOCOLLECTION is specified, the default DB2 value applies.


SQL Option - COMMIT

Specifies where implicit COMMIT statements should be generated.

Syntax:
 >>---COMMIT=--level--------------------><
 
Parameters:

level The level number.

Properties:

Default: COMMIT=2
Comments:

To preserve the integrity of a database on an abnormal program termination, code can be generated to roll back changes when the database manager shuts down. Code can also be generated at strategic points in your program to commit changes made to the database up to that point. The level number in the COMMIT SQL option specifies where these statements should be generated, as follows:

1 No COMMIT statements implicitly generated
2 COMMIT statements are implicitly generated on STOP RUN statements and at the end of the program
3 COMMIT statements are implicitly generated on STOP RUN and EXIT PROGRAM statements and at the end of the program
4 COMMIT statements are implicitly generated after every SQL statement

This option requires INIT to be set.


SQL Option - CONNECT

Specifies the behavior of CONNECT statements with the application.

Only valid with DB2 V2 and above.

Syntax:
 >>-.----.---CONNECT=--setting------><
    .-NO-+
Parameters:

setting Refer to your DB2 documentation.

Properties:

Default: NOCONNECT
Comments:

If NOCONNECT is specified, the DB2 default applies; otherwise, possible values of setting are:

1
2


SQL Option - CTRACE

Causes listing output to contain diagnostics showing calls to precompiler services.

Syntax:
 >>-.----.--CTRACE-----------------><
    .-NO-+
Parameters:

None

Properties:

Default: NOCTRACE
Comments:

This option only affects what the listing file, if produced, contains. It does not determine if a listing file is produced, or the name of the file.


SQL Option - DB

Specifies the name of the database that the program accesses.

Syntax:
 >>-.-----DB=--database-name-.-----><
    .-NO--DB-----------------+
Parameters:

database-name An alphanumeric string obeying the rules for a Database Manager database name.

Properties:

Default: NODB
Comments:

This option should always be specified.

On OS/2 and Windows, the database-name is always folded to upper case. This is required by DDCS/2 and is transparent in all other cases.


SQL Option - DB2

Specifies that data items defined outside the scope of a Declare Section are allowed as host variables. This is for compatibility with IBM mainframes.

Syntax:
 >>-.----.--DB2--------------------><
    .-NO-+
Parameters:

None

Properties:

Default: DB2
Comments:

With NODB2, any data item that is defined outside a Declare Section and is used in an SQL statement is assumed to be an SQLDA.

With DB2 set, data items are inspected to see if they are host variables or an SQLDA. This provides compatibility with DB2 on IBM mainframes.

This option also allows the use of group host variables for compatibility with IBM mainframes.

This option does not affect other DB2 extensions provided by the Compiler.


SQL Option - DBMAN

Specifies the database engine family that is being used at both compilation and run time.

Syntax:
 >>--.--------.--database-manager---><
     .-DBMAN=-+
Parameters:

database-manager The type of database engine to use.

Properties:

Default: DBMAN=IBM
Comments:

The possible values of database-manager are IBM, MSSQL or ODBC. For all database engines referenced in this chapter, use IBM.

IBM OS/2 Extended Edition Database Manager
IBM Extended Services/2 V1.0 Database Manager
IBM DB2/2 V1.0
IBM DB2/2 V1.2
IBM DB2/6000 V1
IBM DB2 V2 (with functionality limited to that of V1.x)

Use MSSQL if you are using Microsoft SQL Server V4.2 or later with Microsoft embedded SQL for COBOL developers kit V4.21 or later.


SQL Option - DEC

Specifies the maximum precision to use in decimal arithmetic operations.

Only valid with DB2 V2 and above.

Syntax:
 >>-.----.---DEC=--setting-----------><
    .-NO-+
Parameters:

setting Refer to your DB2 documentation.

Properties:

Default: NODEC
Comments:

If NODEC is specified, the DB2 default is used; otherwise, possible values of setting are:

15
31


SQL Option - DECDEL

Specifies the decimal point character in numeric literals.

Only valid with DB2 V2 and above.

Syntax:
 >>-.----.---DECDEL=--setting-----------><
    .-NO-+
Parameters:

setting Refer to your DB2 documentation.

Properties:

Default: NODECDEL
Comments:

If NODECDEL is specified, the default DB2 value applies; otherwise, possible values of setting are:

COMMA
PERIOD


SQL Option - DEGREE

Controls the degree of I/O parallelism.

Only valid with DB2 V2 and above.

Syntax:
 >>-.----.---DEGREE=--setting-----------><
    .-NO-+
Parameters:

setting Refer to your DB2 documentation.

Properties:

Default: NODEGREE
Comments:

If NODEGREE is specified, the default DB2 value applies; otherwise, possible values of setting are:

ANY
Positive non-zero integer less than 32768


SQL Option - DISCONNECT

Specifies how database connections are to be broken at COMMIT time.

Only valid with DB2 V2 and above.

Syntax:
 >>-.----.---DISCONNECT=--setting-------><
    .-NO-+
Parameters:

setting Refer to your DB2 documentation.

Properties:

Default: NODISCONNECT
Comments:

If NODISCONNECT is specified, the default DB2 value applies; otherwise, possible values of setting are:

AUTOMATIC
CONDITIONAL
EXPLICIT


SQL Option - DYNAMICRULES

Specifies which authorisation identifier to use when dynamic SQL is executed.

Only valid with DB2 V2 and above.

Syntax:
 >>-.----.---DYNAMICRULES=--setting--><
    .-NO-+
Parameters:

setting Refer to your DB2 documentation.

Properties:

Default: NODYNAMICRULES
Comments:

If NODYNAMICRULES is specified, the default DB2 value applies; otherwise, possible values of setting are:

BIND
RUN


SQL Option - ECSPP

This option is reserved for use with the add-on products "Micro Focus Host Compatibility Option" and "Micro Focus DB2 Option". Do not change its setting.

Syntax:
 >>-.----.---ECSPP=--parameter-------><
    .-NO-+
Parameters:

parameter Has no effect on the behavior of this option. It is included for compatibility purposes.

Properties:

Default: NOECSPP


SQL Option - EXPLAIN

Specifies that information about the access plans is to be stored in the Explain tables.

Only valid with DB2 V2 and above.

Syntax:
 >>-.----.---EXPLAIN=--setting----------><
    .-NO-+
Parameters:

setting Refer to your DB2 documentation.

Properties:

Default: NOEXPLAIN
Comments:

If NOEXPLAIN is specified, the default DB2 value applies; otherwise, possible values of setting are:

NO
YES


SQL Option - EXPLSNAP

Specifies that Explain snapshots are to be stored in the Explain tables.

Only valid with DB2 V2 and above.

Syntax:
 >>-.----.---EXPLSNAP=--setting--------><
    .-NO-+
Parameters:

setting Refer to your DB2 documentation.

Properties:

Default: NOEXPLSNAP
Comments:

If NOEXPLSNAP is specified, the default DB2 value applies; otherwise, possible values of setting are:

ALL
NO
YES


SQL Option - FORMAT

Determines the date and time format when date/time fields are assigned to string representations in host variables.

Syntax:
 >>---FORMAT=--date-format----------><
Synonym:

DATETIME

Parameters:

date-format Identifies the format to use.

Properties:

Default: FORMAT=LOC
Comments:

The possible values of date-format are:

DEF determined by country code
USA mm/dd/yyyy hh:mm xM (AM/PM)
EUR dd.mm.yyyy hh.mm.ss
ISO yyyy-mm-dd hh.mm.ss
JIS yyyy-mm-dd hh:mm:ss
LOC Local form

When DEF or LOC is specified, these are the formats used:

-------------------------------------------------------------- 
Country           LOC date    LOC time   DEF date    DEF time 
-------------------------------------------------------------- 
001 USA           mm-dd-yyyy  hh:mm:ss   mm/dd/yyyy  hh:mm xM 
002 Canada/Fr     dd-mm-yyyy  hh.mm.ss   yyyy-mm-dd  hh.mm.ss 
044 UK            dd/mm/yyyy  hh:mm:ss   dd/mm/yyyy  hh:mm:ss 
033 France        dd/mm/yyyy  hh:mm:ss   dd.mm.yyyy  hh.mm.ss 
049 Germany       dd/mm/yyyy  hh.mm.ss   yyyy-mm-dd  hh.mm.ss 
034 Spain         dd/mm/yyyy  hh:mm:ss   dd/mm/yyyy  hh:mm:ss 
039 Italy         dd/mm/yyyy  hh:mm:ss   dd/mm/yyyy  hh:mm:ss 
046 Sweden        dd/mm/yyyy  hh.mm.ss   yyyy-mm-dd  hh.mm.ss 
045 Denmark       dd-mm-yyyy  hh.mm.ss   yyyy-mm-dd  hh.mm.ss 
047 Norway        dd/mm/yyyy  hh.mm.ss   dd.mm.yyyy  hh.mm.ss 
031 Netherlands   dd-mm-yyyy  hh:mm:ss   yyyy-mm-dd  hh.mm.ss 
032 Belgium       dd/mm/yyyy  hh:mm:ss   dd/mm/yyyy  hh:mm:ss 
--------------------------------------------------------------

See your SQL Reference or equivalent for more details.


SQL Option - FUNCPATH

Specifies where User Defined Functions are located.

Only valid with DB2 V2 and above.

Syntax:
 >>-.----.---FUNCPATH=--setting------><
    .-NO-+
Parameters:

setting Alphanumeric path name. Refer to your DB2 documentation.

Properties:

Default: NOFUNCPATH
Comments:

If NOFUNCPATH is specified, the default DB2 value applies.


SQL Option - INIT

Makes the program initialize SQL and log on to the database.

Syntax:
 >>-.----.---INIT=--mode----------------><
    .-NO-+
Parameters:

mode Specifies whether the database is to be used in shared or exclusive mode.

Properties:

Default: NOINIT (for DB2 V2 and above)INIT=S (otherwise)
Comments:

INIT causes the program to initialize the connection to the database. As part of this process it registers a procedure so that the database is always properly closed down when a STOP RUN occurs. Without this you can leave the database in a corrupt condition if the program terminates before completion; for example, if you terminate an animation session before completing the program.

The possible values of mode are:

PROT For SQL programs that need to protect the database on STOP RUN but do not want to initialize.
S Database to be used in shared mode
X Database to be used in exclusive mode

NOINIT should be specified for SQL programs that are called by other SQL programs.


Note: DB2 has been enhanced considerably since the Micro Focus default for this option was originally chosen. In particular, it now provides a CONNECT statement and implicit connection, making INIT=S less appropriate. In addition, it exits cleanly when a database connection is left open which reduces the need for INIT=PROT. However, you may wish to explicitly set INIT=PROT, especially when working in Workbench or similar Micro Focus development environments. Without it, not explicitly issuing a RESET CONNECT statement can cause DB2 to complain that it is already connected when re-running or compiling a program. If this happens, leaving the development environment causes DB2 to tidy up and close the open connection. For DB2 V2, the default setting INIT=NO removes the need to include the Micro Focus supplied object file SQLINI?.O/OBJ when linking to create an executable.



SQL Option - INSERT

Specifies whether DB2 PE inserts should be buffered.

Only valid with DB2 V2 and above.

Syntax:
 >>-.----.---INSERT=--setting--------><
    .-NO-+
Parameters:

setting Refer to your DB2 documentation.

Properties:

Default: NOINSERT
Comments:

If NOINSERT is specified, the default DB2 value applies; otherwise, possible values of setting are:

BUF
DEF


SQL Option - ISOLATION

Specifies the isolation level to use.

Syntax:
 >>---ISOLATION=--isol-level--------->< 
Parameters:

isol-level The isolation level to use.

Properties:

Default: ISOLATION=CS
Comments:

The possible values of isol-level are:

RR Repeatable read
CS Cursor stability
UR Uncommitted read
RS Read stability - valid only with DB2 V2 and above
NC No commit - valid only with DB2 V2 and above


SQL Option - LEVEL

Specifies a consistency token.

Only valid with DB2 V2 and above.

Syntax:
 >>-.----.---LEVEL=--setting------------><
    .-NO-+
Parameters:

setting Alphanumeric string up to 8 characters in length. Refer to your DB2 documentation.

Properties:

Default: NOLEVEL
Comments:

If NOLEVEL is specified, the default DB2 value applies.


SQL Option - OWNER

Specifies the package owner.

Only valid with DB2 V2 and above.

Syntax:
 >>-.----.---OWNER=--setting------------><
    .-NO-+
Synonym:

SCHEMA

Parameters:

setting Alphanumeric string up to 8 characters in length. Refer to your DB2 documentation.

Properties:

Default: NOOWNER
Comments:

If NOOWNER is specified, the default DB2 values applies.


SQL Option - NOT

Specifies the value to use for the NOT character (¸).

Syntax:
 >>---NOT=--integer--------------------><
 
Parameters:

integer The ASCII value, in decimal, of the character to use.

Properties:

Default: NOT=170
Comments:

You can specify any value from 0 through 255 for integer. This option is provided for use on operating systems that do not use 170 to represent the not (¸) character.


SQL Option - PASS

Specifies the userid and password of the database.

Syntax:
 >>-.-----PASS=--usid.passwd---.----><
    .-NO--PASS-----------------+
Parameters:

usid.passwd An alphanumeric string obeying the rules for a userid and password, separated by a period. The period can be omitted.

Properties:

Default: NOPASS
Comments:

If the database has no password, use NOPASS. This option should only be specified if you are compiling against a remote database.


SQL Option - PRE

Enables SQL programs to be animated or run by preloading the database API modules.

Syntax:
 >>-.----.---PRE=--set-level---------><
    .-NO-+
Parameters:

set-level Specifies which set of modules to preload

Properties:

Default: PRE=ALL
Comments:

The possible values of set-level are:

ALL All modules
REQ Only those modules that are used by EXEC SQL statements

On 32-bit systems, REQ and ALL are synonymous.

Specifying this option causes a call to be generated to a module which preloads the API modules required when running an SQL program. This ensures that any SQL calls generated by embedded SQL commands or coded in the program can be resolved when running or animating. If you make no calls to SQL, you can use PRE=REQ, which causes the start-up code to be executed more quickly.

Applications that are to be linked to create a .dll or .exe file might not need this option to be specified. See the other chapters in this part for more information.

This option has no effect on UNIX.


SQL Option - QUALIFIER

Specifies the qualifier to use when none is explicitly given.

Only valid with DB2 V2 and above.

Syntax:
 >>-.----.---QUALIFIER=--setting--------><
    .-NO-+
Synonym:

CATALOG

Parameters:

setting Alphanumeric string up to 18 characters in length. Refer to your DB2 documentation.

Properties:

Default: NOQUALIFIER
Comments:

If NOQUALIFIER is specified, the default DB2 value appplies.


SQL Option - QUERYOPT

Specifies the optimisation level.

Only valid with DB2 V2 and above.

Syntax:
 >>-.----.---QUERYOPT=--setting---------><
    .-NO-+
Parameters:

setting An integer. Refer to your DB2 documentation.

Properties:

Default: NOQUERYOPT
Comments:

If NOQUERYOPT is specified, the default DB2 value applies.


SQL Option - RELEASE

Specifies whether resources can be released at a COMMIT point.

Only valid with DB2 V2 and above.

Syntax:
 >>-.----.---RELEASE=--setting------><
    .-NO-+
Parameters:

setting Refer to your DB2 documentation.

Properties:

Default: NORELEASE
Comments:

If NORELEASE is specified, the default DB2 value applies; otherwise, possible values of setting are:

COMMIT
DEALLOCATE


SQL Option - REPLVER

Specifies the version of a package to replace when ACTION=REPLACE.

Only valid with DB2 V2 and above.

Syntax:
 >>-.----.---REPLVER=--setting------><
    .-NO-+
Parameters:

setting Alphanumeric string up to 254 characters in length. Refer to your DB2 documentation.

Properties:

Default: NOREPLVER
Comments:

If NOREPLVER is specified, the default DB2 value applies.


SQL Option - RETAIN

Specifies whether EXECUTE authorities are preserved when ACTION=REPLACE.

Only valid with DB2 V2 and above.

Syntax:
 >>-.----.---RETAIN=--setting-------><
    .-NO-+
Parameters:

setting Refer to your DB2 documentation.

Properties:

Default: NORETAIN
Comments:

If NORETAIN is specified, the default DB2 value applies; otherwise, possible values of setting are:

NO
YES


SQL Option - SQLERROR

Specifies whether a package or bind file is to be created if SQL errors are encountered.

Only valid with DB2 V2 and above.

Syntax:
 >>-.----.---SQLERROR=--setting---------><
    .-NO-+
Synonym:

ERROR

Parameters:

setting Refer to your DB2 documentation.

Properties:

Default: NOSQLERROR
Comments:

If NOSQLERROR is specified, the default DB2 value applies; otherwise, possible values of setting are:

CHECK
CONTINUE
NOPACKAGE


SQL Option - SQLFLAG

Specifies whether SQL syntax incompatible with DB2 for MVS is to be flagged.

Only valid with DB2 V2 and above.

Syntax:
 >>-.----.---SQLFLAG=--setting----------><
    .-NO-+
Synonym:

FLAG

Parameters:

setting Refer to your DB2 documentation.

Properties:

Default: NOSQLFLAG
Comments:

If NOSQLFLAG is specified, the default DB2 value applies; otherwise possible values of setting are:

MVSDB2V23-SYNTAX
MVSDB2V31-SYNTAX
MVSDB2V41-SYNTAX

The SQL Flagger is part of the DB2 engine. It produces SQL informational messages when non-compatible syntax is detected and these are displayed by the COBOL system as warning message 1133. In order to see these messages, the WARNING COBOL directive must be set to 2 or higher. The messages produced are not dialect flagging messages as produced when the FLAG compiler directive is used.

It is also important to note that the COBOL system and the Micro Focus Host Compatibility Option (HCO) (if used) provide extensions to the SQL language and may alter the SQL text actually passed to the SQL engine. Because the SQL statement in the COBOL source and that passed to the SQL engine may differ, the flagger may not return the expected information.


SQL Option - SQLRULES

Specifies whether type-2 CONNECT statements are to be processed with DB2 rules or ISO/ANSI rules.

Only valid with DB2 V2 and above.

Syntax:
 >>-.----.---SQLRULES=--setting------><
    .-NO-+
Synonym:

RULES

Parameters:

setting Refer to your DB2 documentation.

Properties:

Default: NOSQLRULES
Comments:

If NOSQLRULES is specified, the default DB2 value applies; otherwise, possible values of setting are:

DB2
STD


SQL Option - SQLWARN

Indicates whether warning (positive) SQLCODE values are produced or suppressed.


Note: It is necessary to set the WARNING Compiler directive in order to display such messages at compilation time.


Only valid with DB2 V2 and above.

Syntax:
 >>-.----.---SQLWARN=--setting----------><
    .-NO-+
Synonym:

WARN

Parameters:

setting Refer to your DB2 documentation.

Properties:

Default: NOSQLWARN
Comments:

If NOSQLWARN is specified, the default DB2 value applies; otherwise, possible values of setting are:

NO
YES


SQL Option - STDLVL

Specifies the standards level of the database manager.

Syntax:
 >>---STDLVL=--standard-level-----------><
Synonym:

LANGLEVEL

Parameters:

standard-level The standard level to be used.

Properties:

Default: STDLVL=NONE
Comments:

Possible values for standard-level are:

NONE FOR UPDATE clause is required on cursor declarations if tables are updated through the cursor.
MIA FOR UPDATE clause is optional. Using this option might degrade performance.


SQL Option - STRDEL

Specifies the string delimiter.

Only valid with DB2 V2 and above.

Syntax:
 >>-.----.---STRDEL=--setting-------><
    .-NO-+
Parameters:

setting Refer to your DB2 documentation.

Properties:

Default: NOSTRDEL
Comments:

If NOSTRDEL is specified, the default DB2 value applies; otherwise, possible values of setting are:

APOSTROPHE
QUOTE


SQL Option - SYNCPOINT

Specifies how COMMITs and ROLLBACKs are co-ordinated across multiple database connections.

Only valid with DB2 V2 and above.

Syntax:
 >>-.----.---SYNCPOINT=--setting----><
    .-NO-+
Parameters:

setting Refer to your DB2 documentation.

Properties:

Default: NOSYNCPOINT
Comments:

If NOSYNCPOINT is specified, the default DB2 value applies; otherwise, possible values of setting are:

NONE
ONEPHASE
TWOPHASE


SQL Option - TEXT

Specifies a package description.

Only valid with DB2 V2 and above.

Syntax:
 >>-.----.---TEXT=--setting--------><
    .-NO-+
Parameters:

setting Alphanumeric string up to 255 characters in length. Refer to your DB2 documentation.

Properties:

Default: NOTEXT
Comments:

If NOTEXT is specified, the default DB2 value applies.


SQL Option - VALIDATE

Specifies when authorisation and object not found errors are produced.

Only valid with DB2 V2 and above.

Syntax:
 >>-.----.---VALIDATE=--setting---------><
    .-NO-+
Parameters:

setting Refer to your DB2 documentation.

Properties:

Default: NOVALIDATE
Comments:

If NOVALIDATE is specified, the default DB2 value applies; otherwise, possible values of setting are:

BIND
RUN


SQL Option - VERSION

Specifies the package version identifier.

Only valid with DB2 V2 and above.

Syntax:
 >>-.----.---VERSION=--setting---------><
    .-NO-+
Parameters:

setting Alphanumeric string up to 254 characters in length. Refer to your DB2 documentation.

Properties:

Default: NOVERSION
Comments:

If NOVERSION is specified, the default DB2 value applies.


24.7 Linking

The linking examples in the following sections show you how to create an executable file from a single object file. You need to select the appropriate example according to the COBOL and DB2 system that you are using.

24.7.1 Linking for the 16-bit COBOL System

This section shows examples of linking for the 16-bit COBOL system. Where two examples are shown, the first creates a coblib-linked file, the second creates an lcobol-linked file. When linking on OS/2 you can link with the file doscalls.lib (as shown) or with os2.lib. You do not need to link in sqlinit.obj or sqlini2.obj if the program was not compiled with the INIT option.

Windows:
Using the 16-bit COBOL system with Windows Requester:

link sqlprog+sqlinit,,,coblibw+coblib+cobw+pcdrdll,
    sqlprog.def/nod/noe/a:16;

where sqlprog.def contains, for example:

exetype windows 3.1
protmode
code  preload fixed
data  preload fixed multiple
stacksize 16384
heapsize  1024

with DB2 V1.2, use:

link sqlprog+cblwina+sqlini2,,,coblibw+coblib+cobw+db2w,
    sqlprog.def/st:32000/se:400/nod/noe;

OS/2:
The link command necessary for OS/2 on the 16-bit COBOL system depends on whether you are using the 16-bit or 32-bit version of DB2.

16-bit DB2:
When using 16-bit versions (IBM OS/2 Extended Edition Database Manager and IBM Extended Services for OS/2 Database Manager) of DB2 on OS/2 with the 16-bit COBOL system, use one of the following link commands:

link sqlprog+sqlinit,,,coblib+doscalls+sql_dyn;
link sqlprog+sqlinit/st:16384,,,lcobol+doscalls+sql_dyn

32-bit DB2:
When using 32-bit versions of DB2 on OS/2 with the 16-bit COBOL system, use one of the following link commands:

link sqlprog+sqlinit,,,coblib+doscalls+sqldyn16;
link sqlprog+sqlini2,,,coblib+doscalls+sqldyn16;
link sqlprog+sqlinit/st:16384,,,lcobol+doscalls+sqldyn16;
link sqlprog+sqlini2/st:16384,,,lcobol+doscalls+sqldyn16;

You can use either the shared run-time system (coblib) or the static linked run-time system (lcobol) for linking SQL programs, and you can create .exe or .dll files.

The module sqlini2.obj is used with DB2 V1.2 in a client/server configuration and with DB2 V2. The module sqlinit.obj is used with DB2 V1.1 and with DB2 V1.2 in a standalone configuration.

The default stack size of the application or of the RTS may be insufficient, although the exact size required depends on the application. If your system traps with error SYS3175 when running a program, you should increase the stack size as follows:

We recommend that you specify the option NOPRE (so that the default stack size is not increased) when creating a coblib-linked dll file. When creating any lcobol-linked executable file, the SQLPRE Compiler directive has no useful function so you can safely set it to NOPRE.

24.7.2 Linking for 32-bit COBOL Systems

This section shows examples of linking for the 32-bit COBOL systems.

OS/2:
A suitable link statement for an SQL program is as follows:

cbllink sqlprog sqlinit _sqlprld sql_dyn.lib
cbllink sqlprog sqlini2 _sqlprld sql_dyn.lib

The module sqlini2.obj is used with DB2 V1.2 in a client/server configuration and with DB2 V2. The module sqlinit.obj is used with DB2 V1.1 and with DB2 V1.2 in a standalone configuration.

See the chapter SQL, DB2/2 and 32-bit COBOL for additional information.

UNIX:
On AIX you can create an executable module directly from the command line:

cob -x sqlprog.cbl $COBDIR/src/sql/sqlinit.o

24.8 Binding

If you use the NOACCESS option of the SQL 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 SQLBIND command. For details on doing this, see the documentation supplied with your SQL system.

24.9 Sample Program

A sample program, Sqldemo, is supplied with this system.

This program is located in the directory cobol\demo on Windows and OS/2 and in the directory $COBDIR/demo/sql on UNIX.

The Sqldemo program demonstrates the use of CREATE TABLE, INSERT and SELECT commands. It uses the sample database provided with DB2, which must be created before the sample programs are compiled.

To prepare the system to run Sqldemo, you must first compile and run the Sqlprep program to create the required tables and views. See your Object COBOL User Guide for information on compiling, and the section Linking earlier in this chapter if you want to link Sqldemo.

Running Sqldemo accesses the sample database, inserts some data, reads it back and then creates a couple of tables.

24.9.1 Error Codes

Error conditions are returned at compilation time as a number and an 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 COBOL system 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 earlier in this chapter for further details about this data item.


Copyright © 1998 Micro Focus Limited. All rights reserved.
This document and the proprietary marks and names used herein are protected by international law.
PreviousIntroduction to Relational Database Handling SQL, DB2/2 and 32-bit COBOLNext