| Introduction to Relational Database Handling | SQL, DB2/2 and 32-bit COBOL | |
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.
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.
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:
Compiling your SQL programs is performed in one step with little or no difference from compiling non-SQL programs. This simplifies the command line compilation process and provides the advantage of other Micro Focus COBOL extensions which the precompiler may not allow, such as omission of the Identification Division and specification of copyfile search paths.
If you have the add-on product Workbench, this also removes the need to exit from Workbench to precompile.
When you animate a program you are shown the modified source program. Using Micro Focus SQL support, you are shown the original source code.
If the program terminates abnormally, the connection to the database is closed normally in all possible cases. When this is not done, it is possible to corrupt the database. In addition, the database is rolled back to ensure integrity of the data.
Full descriptions of run time SQL errors can be obtained.
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.
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
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.
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.
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.
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.
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.
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).
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).
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.
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.
The general format for the declaration of a host variable using SQL syntax is:
level_number name sql_type(size)
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.
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.
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.
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.
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
This section discusses Micro Focus extensions to the embedded SQL support.
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
Statements of the form:
exec sql DECLARE table-name TABLE .... end-exec
are permitted and are treated as comments.
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.
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.
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
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.
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.
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.
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.
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.
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.
>>-.---.-.----SQL-.----------------------..><
.-/-+ | | +-----.---.-----+ ||
| | | .-,-+ | ||
| | v | ||
| .-(.-option=setting-.)-+|
| | | |
| .-NO-option------+ |
.-NO-SQL-------------------------+
option |
One of the SQL options (see below) |
setting |
The setting for the option |
| Default: | NOSQL |
| Phase: | Syntax check |
| Environment: | All |
| $SET: | Initial |
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.
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)
Specifies the name of the access plan (package) to be created by precompiler services and stored in the database.
>>--.----.---ACCESS=--access-plan--->< .-NO-+
PACKAGE
access-plan |
The root of a filename, or a null string. |
| Default: | ACCESS |
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.
Indicates whether the package can be added or replaced.
Only valid with DB2 V2 and above.
>>--.----.---ACTION=--setting-------><
.-NO-+
setting |
Refer to your DB2 documentation. |
| Default: | NOACTION |
If NOACTION is specified, the DB2 defaults apply; otherwise, the possible values of setting are:
ADD
REPLACE
Specifies the name of the bind file to be created by precompiler services.
>>--.----.---BIND=--bindfile-name------><
.-NO-+
BINDFILE
bindfile-name |
A full file specification or a null string. |
| Default: | NOBIND |
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.
Specifies the record blocking mode to be used on plan creation.
>>---BLOCK=--block-mode----------------><
BLOCKING
block-mode |
The blocking mode to be used. |
| Default: | BLOCK=UNAMBIG |
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. |
Specifies the default CCSID to use for double-byte characters in character column definitions.
Only valid with DB2 V2 and above.
>>-.----.---CCSIDG=--integer-------><
.-NO-+
integer |
Refer to your DB2 documentation. |
| Default: | NOCCSIDG |
If NOCCSIDG is specified, the default DB2 value applies.
Specifies the default CCSID to use for mixed byte characters in character column definitions.
Only valid with DB2 V2 and above.
>>-.----.---CCSIDM=--integer-------><
.-NO-+
integer |
Refer to your DB2 documentation. |
| Default: | NOCCSIDM |
If NOCCSIDM is specified, the default DB2 value applies.
Specifies the default CCSID to use for single byte characters in character column definitions.
Only valid with DB2 V2 and above.
>>-.----.---CCSIDS=--integer-------><
.-NO-+
integer |
Refer to your DB2 documentation. |
| Default: | NOCCSIDS |
If NOCCSIDS is specified, the default DB2 value applies.
Specifies the default character subtype to use in column definitions.
Only valid with DB2 V2 and above.
>>-.----.---CHARSUB=--setting----------><
.-NO-+
setting |
Refer to your DB2 documentation. |
| Default: | NOCHARSUB |
If NOCHARSUB is specified, the DB2 default applies; otherwise, the possible values of setting are:
BIT
DEFAULT
MIXED
SBCS
Specifies the collection identifier of the package.
Only valid with DB2 V2 and above.
>>-.----.---COLLECTION=--setting-------><
.-NO-+
setting |
An eight character alphanumeric. Refer to your DB2 documentation. |
| Default: | NOCOLLECTION |
If NOCOLLECTION is specified, the default DB2 value applies.
Specifies where implicit COMMIT statements should be generated.
>>---COMMIT=--level--------------------><
level |
The level number. |
| Default: | COMMIT=2 |
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.
Specifies the behavior of CONNECT statements with the application.
Only valid with DB2 V2 and above.
>>-.----.---CONNECT=--setting------><
.-NO-+
setting |
Refer to your DB2 documentation. |
| Default: | NOCONNECT |
If NOCONNECT is specified, the DB2 default applies; otherwise, possible values of setting are:
1
2
Causes listing output to contain diagnostics showing calls to precompiler services.
>>-.----.--CTRACE-----------------><
.-NO-+
None
| Default: | NOCTRACE |
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.
Specifies the name of the database that the program accesses.
>>-.-----DB=--database-name-.-----><
.-NO--DB-----------------+
database-name |
An alphanumeric string obeying the rules for a Database Manager database name. |
| Default: | NODB |
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.
Specifies that data items defined outside the scope of a Declare Section are allowed as host variables. This is for compatibility with IBM mainframes.
>>-.----.--DB2--------------------><
.-NO-+
None
| Default: | DB2 |
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.
Specifies the database engine family that is being used at both compilation and run time.
>>--.--------.--database-manager---><
.-DBMAN=-+
database-manager |
The type of database engine to use. |
| Default: | DBMAN=IBM |
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.
Specifies the maximum precision to use in decimal arithmetic operations.
Only valid with DB2 V2 and above.
>>-.----.---DEC=--setting-----------><
.-NO-+
setting |
Refer to your DB2 documentation. |
| Default: | NODEC |
If NODEC is specified, the DB2 default is used; otherwise, possible values of setting are:
15
31
Specifies the decimal point character in numeric literals.
Only valid with DB2 V2 and above.
>>-.----.---DECDEL=--setting-----------><
.-NO-+
setting |
Refer to your DB2 documentation. |
| Default: | NODECDEL |
If NODECDEL is specified, the default DB2 value applies; otherwise, possible values of setting are:
COMMA
PERIOD
Controls the degree of I/O parallelism.
Only valid with DB2 V2 and above.
>>-.----.---DEGREE=--setting-----------><
.-NO-+
setting |
Refer to your DB2 documentation. |
| Default: | NODEGREE |
If NODEGREE is specified, the default DB2 value applies; otherwise, possible values of setting are:
ANY
Positive non-zero integer less than 32768
Specifies how database connections are to be broken at COMMIT time.
Only valid with DB2 V2 and above.
>>-.----.---DISCONNECT=--setting-------><
.-NO-+
setting |
Refer to your DB2 documentation. |
| Default: | NODISCONNECT |
If NODISCONNECT is specified, the default DB2 value applies; otherwise, possible values of setting are:
AUTOMATIC
CONDITIONAL
EXPLICIT
Specifies which authorisation identifier to use when dynamic SQL is executed.
Only valid with DB2 V2 and above.
>>-.----.---DYNAMICRULES=--setting--><
.-NO-+
setting |
Refer to your DB2 documentation. |
| Default: | NODYNAMICRULES |
If NODYNAMICRULES is specified, the default DB2 value applies; otherwise, possible values of setting are:
BIND
RUN
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.
>>-.----.---ECSPP=--parameter-------><
.-NO-+
parameter |
Has no effect on the behavior of this option. It is included for compatibility purposes. |
| Default: | NOECSPP |
Specifies that information about the access plans is to be stored in the Explain tables.
Only valid with DB2 V2 and above.
>>-.----.---EXPLAIN=--setting----------><
.-NO-+
setting |
Refer to your DB2 documentation. |
| Default: | NOEXPLAIN |
If NOEXPLAIN is specified, the default DB2 value applies; otherwise, possible values of setting are:
NO
YES
Specifies that Explain snapshots are to be stored in the Explain tables.
Only valid with DB2 V2 and above.
>>-.----.---EXPLSNAP=--setting--------><
.-NO-+
setting |
Refer to your DB2 documentation. |
| Default: | NOEXPLSNAP |
If NOEXPLSNAP is specified, the default DB2 value applies; otherwise, possible values of setting are:
ALL
NO
YES
Determines the date and time format when date/time fields are assigned to string representations in host variables.
>>---FORMAT=--date-format----------><
DATETIME
date-format |
Identifies the format to use. |
| Default: | FORMAT=LOC |
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.
Specifies where User Defined Functions are located.
Only valid with DB2 V2 and above.
>>-.----.---FUNCPATH=--setting------><
.-NO-+
setting |
Alphanumeric path name. Refer to your DB2 documentation. |
| Default: | NOFUNCPATH |
If NOFUNCPATH is specified, the default DB2 value applies.
Makes the program initialize SQL and log on to the database.
>>-.----.---INIT=--mode----------------><
.-NO-+
mode |
Specifies whether the database is to be used in shared or exclusive mode. |
| Default: | NOINIT (for DB2 V2 and above)INIT=S (otherwise) |
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.
Specifies whether DB2 PE inserts should be buffered.
Only valid with DB2 V2 and above.
>>-.----.---INSERT=--setting--------><
.-NO-+
setting |
Refer to your DB2 documentation. |
| Default: | NOINSERT |
If NOINSERT is specified, the default DB2 value applies; otherwise, possible values of setting are:
BUF
DEF
Specifies the isolation level to use.
>>---ISOLATION=--isol-level---------><
isol-level |
The isolation level to use. |
| Default: | ISOLATION=CS |
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 |
Specifies a consistency token.
Only valid with DB2 V2 and above.
>>-.----.---LEVEL=--setting------------><
.-NO-+
setting |
Alphanumeric string up to 8 characters in length. Refer to your DB2 documentation. |
| Default: | NOLEVEL |
If NOLEVEL is specified, the default DB2 value applies.
Specifies the package owner.
Only valid with DB2 V2 and above.
>>-.----.---OWNER=--setting------------><
.-NO-+
SCHEMA
setting |
Alphanumeric string up to 8 characters in length. Refer to your DB2 documentation. |
| Default: | NOOWNER |
If NOOWNER is specified, the default DB2 values applies.
Specifies the value to use for the NOT character (¬).
>>---NOT=--integer--------------------><
integer |
The ASCII value, in decimal, of the character to use. |
| Default: | NOT=170 |
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.
Specifies the userid and password of the database.
>>-.-----PASS=--usid.passwd---.----><
.-NO--PASS-----------------+
usid.passwd |
An alphanumeric string obeying the rules for a userid and password, separated by a period. The period can be omitted. |
| Default: | NOPASS |
If the database has no password, use NOPASS. This option should only be specified if you are compiling against a remote database.
Enables SQL programs to be animated or run by preloading the database API modules.
>>-.----.---PRE=--set-level---------><
.-NO-+
set-level |
Specifies which set of modules to preload |
| Default: | PRE=ALL |
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.
Specifies the qualifier to use when none is explicitly given.
Only valid with DB2 V2 and above.
>>-.----.---QUALIFIER=--setting--------><
.-NO-+
CATALOG
setting |
Alphanumeric string up to 18 characters in length. Refer to your DB2 documentation. |
| Default: | NOQUALIFIER |
If NOQUALIFIER is specified, the default DB2 value appplies.
Specifies the optimisation level.
Only valid with DB2 V2 and above.
>>-.----.---QUERYOPT=--setting---------><
.-NO-+
setting |
An integer. Refer to your DB2 documentation. |
| Default: | NOQUERYOPT |
If NOQUERYOPT is specified, the default DB2 value applies.
Specifies whether resources can be released at a COMMIT point.
Only valid with DB2 V2 and above.
>>-.----.---RELEASE=--setting------><
.-NO-+
setting |
Refer to your DB2 documentation. |
| Default: | NORELEASE |
If NORELEASE is specified, the default DB2 value applies; otherwise, possible values of setting are:
COMMIT
DEALLOCATE
Specifies the version of a package to replace when ACTION=REPLACE.
Only valid with DB2 V2 and above.
>>-.----.---REPLVER=--setting------><
.-NO-+
setting |
Alphanumeric string up to 254 characters in length. Refer to your DB2 documentation. |
| Default: | NOREPLVER |
If NOREPLVER is specified, the default DB2 value applies.
Specifies whether EXECUTE authorities are preserved when ACTION=REPLACE.
Only valid with DB2 V2 and above.
>>-.----.---RETAIN=--setting-------><
.-NO-+
setting |
Refer to your DB2 documentation. |
| Default: | NORETAIN |
If NORETAIN is specified, the default DB2 value applies; otherwise, possible values of setting are:
NO
YES
Specifies whether a package or bind file is to be created if SQL errors are encountered.
Only valid with DB2 V2 and above.
>>-.----.---SQLERROR=--setting---------><
.-NO-+
ERROR
setting |
Refer to your DB2 documentation. |
| Default: | NOSQLERROR |
If NOSQLERROR is specified, the default DB2 value applies; otherwise, possible values of setting are:
CHECK
CONTINUE
NOPACKAGE
Specifies whether SQL syntax incompatible with DB2 for MVS is to be flagged.
Only valid with DB2 V2 and above.
>>-.----.---SQLFLAG=--setting----------><
.-NO-+
FLAG
setting |
Refer to your DB2 documentation. |
| Default: | NOSQLFLAG |
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.
Specifies whether type-2 CONNECT statements are to be processed with DB2 rules or ISO/ANSI rules.
Only valid with DB2 V2 and above.
>>-.----.---SQLRULES=--setting------><
.-NO-+
RULES
setting |
Refer to your DB2 documentation. |
| Default: | NOSQLRULES |
If NOSQLRULES is specified, the default DB2 value applies; otherwise, possible values of setting are:
DB2
STD
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.
>>-.----.---SQLWARN=--setting----------><
.-NO-+
WARN
setting |
Refer to your DB2 documentation. |
| Default: | NOSQLWARN |
If NOSQLWARN is specified, the default DB2 value applies; otherwise, possible values of setting are:
NO
YES
Specifies the standards level of the database manager.
>>---STDLVL=--standard-level-----------><
LANGLEVEL
standard-level |
The standard level to be used. |
| Default: | STDLVL=NONE |
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. |
Specifies the string delimiter.
Only valid with DB2 V2 and above.
>>-.----.---STRDEL=--setting-------><
.-NO-+
setting |
Refer to your DB2 documentation. |
| Default: | NOSTRDEL |
If NOSTRDEL is specified, the default DB2 value applies; otherwise, possible values of setting are:
APOSTROPHE
QUOTE
Specifies how COMMITs and ROLLBACKs are co-ordinated across multiple database connections.
Only valid with DB2 V2 and above.
>>-.----.---SYNCPOINT=--setting----><
.-NO-+
setting |
Refer to your DB2 documentation. |
| Default: | NOSYNCPOINT |
If NOSYNCPOINT is specified, the default DB2 value applies; otherwise, possible values of setting are:
NONE
ONEPHASE
TWOPHASE
Specifies a package description.
Only valid with DB2 V2 and above.
>>-.----.---TEXT=--setting--------><
.-NO-+
setting |
Alphanumeric string up to 255 characters in length. Refer to your DB2 documentation. |
| Default: | NOTEXT |
If NOTEXT is specified, the default DB2 value applies.
Specifies when authorisation and object not found errors are produced.
Only valid with DB2 V2 and above.
>>-.----.---VALIDATE=--setting---------><
.-NO-+
setting |
Refer to your DB2 documentation. |
| Default: | NOVALIDATE |
If NOVALIDATE is specified, the default DB2 value applies; otherwise, possible values of setting are:
BIND
RUN
Specifies the package version identifier.
Only valid with DB2 V2 and above.
>>-.----.---VERSION=--setting---------><
.-NO-+
setting |
Alphanumeric string up to 254 characters in length. Refer to your DB2 documentation. |
| Default: | NOVERSION |
If NOVERSION is specified, the default DB2 value applies.
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.
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:
Specify the /ST linker option. For example, to increase the program's stack size to 16,384, set the /ST linker option as follows:
/ST:16384
For details of this option, see your Object COBOL User Guide.
set COBSW=/S30000
There is no absolute value to use to resolve this problem. To determine the best value for your circumstances, we recommend you try a value of 30,000 first and adjust this up or down as required. For details on the /S run-time switch, see your Object COBOL User Guide.
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.
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
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.
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.
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 © 1999 MERANT International Limited. All rights reserved.
This document and the proprietary marks and names
used herein are protected by international law.
| Introduction to Relational Database Handling | SQL, DB2/2 and 32-bit COBOL | |