PreviousOpenESQL Assistant COBSQLNext

Chapter 9: DB2

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

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

9.1 Data Types

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

9.1.1 Decimal

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

9.1.2 Additional Data Types

All additional data types must be declared using SQL syntax of the form:

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

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

where

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

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

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

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

SQL syntax Equivalent COBOL syntax
01  MY-BLOB SQL BLOB(125M).
01  MY-BLOB.
   49  MY-BLOB-LENGTH PIC S9(9) COMP-5.
   49  MY-BLOB-DATA   PIC X(131072000).
03  A SQL CLOB(3K).
03  A.
   49  A-LENGTH PIC S9(9) COMP-5.
   49  A-DATA   PIC X(3072).
03  HV SQL DBCLOB(125).
03  HV.
   49  HV-LENGTH PIC S9(9) COMP-5.
   49  HV-DATA   PIC G(125).
01  B SQL BLOB-LOCATOR.
01  B PIC S9(9) COMP-5.
01  C SQL CLOB-FILE.
01  C.
   49  C-NAME-LENGTH  PIC S9(9) COMP-5.
   49  C-DATA-LENGTH  PIC S9(9) COMP-5.
   49  C-FILE-OPTIONS PIC S9(9) COMP-5.
   49  C-NAME         PIC X(255).
01  TS SQL TIMESTAMP.
01  TS PIC X(26).

9.2 Compound SQL

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

9.3 User Defined Functions

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

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

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


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


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

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

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

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

Program 2 uses the UDF:

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

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

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

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


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


9.4 Extensions to Embedded SQL Support

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

9.4.1 The INCLUDE Statement

Statements of the form:

exec sql
   include filename
end-exec

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

copy filename

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

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

9.4.2 The DECLARE TABLE Statement

Statements of the form:

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

are permitted and are treated as comments.

9.4.3 Integer Host Variables

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

9.4.4 Qualified Host Variables

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

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

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

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

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

9.4.5 Host Variable Groups and Indicator Arrays

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

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

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

In such an example, the procedural statement:

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

is equivalent to:

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

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

The table of indicator variables is redefined only to show the equivalent SQL statement (subscripting is not allowed in SQL statements). The redefinition can be omitted and the COBOL program can refer to the indicator variables using subscripting, if desired.

9.4.6 The NOT Operator (¬)

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

9.4.7 The Concat Operator (|)

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

9.4.8 SQL Communications Area

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

exec sql include sqlca end-exec

This causes the source file sqlca.cpy (on Windows) or sqlca.cbl (on UNIX) to be included in your source code. This source file, supplied with the DB2 ECM, contains a COBOL definition of the SQLCA.

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

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

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

9.4.9 Support for Object Oriented COBOL Syntax

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

9.4.10 Support for Nested COBOL programs

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

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

9.5 DB2 INIT Directive

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

The INIT directive has the additional option of ensuring that the database connection is correctly closed down even if the application is abnormally terminated, to avoid possible database corruption. If the application is abnormally terminated, all changes since the last COMMIT are rolled back. This database protection can be selected by specifying the option INIT=PROT on the DB2 Compiler directive.

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

9.6 Compiling

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

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

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

9.6.1 DB2 Compiler Directive

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

For example:

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

9.6.1.1 Default Values

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

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

Option
Description
ACCESS=package name,ACCESS, NOACCESS Specifies the name of the package to be created and stored in the database. If ACCESS is specified without a parameter, the package name defaults to the program name (without the .CBL extension).

Synonym is PACKAGE.

BIND=bindfile, BIND, NOBIND Specifies the name of the bind file to be created. When BIND is specified without a parameter, the bind file defaults to the program name with the filename extension replaced by .BND.

Synonym is BINDFILE.

BLOCK={UNAMBIG | ALL | NO} Specifies the record blocking mode to be used on package creation. For information about row blocking, see the IBM DB2 Administration Guide or the Application Programming Guide.
ALL Specifies blocking for read-only cursors or cursors not specified as FOR UPDATE OF. Ambiguous cursors are treated as read-only.
NO Specifies no blocking of any cursors. Ambiguous cursors are treated as updateable.
UNAMBIG Specifies blocking for read-only cursors or cursors not specified as FOR UPDATE OF. Ambiguous cursors are treated as updateable.

Synonym is BLOCKING.

COLLECTION=schema name, NOCOLLECTION Specifies an 8-character collection identifier for the package. If this is not specified, the authorization identifier for the user processing the package is used.
COMMIT={1 | 2 | 3 | 4} Specifies where implicit COMMIT statements should be generated.
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
CONCAT=(ascii character code | 33 } Specifies the ASCII character code to use for the CONCAT symbol (|).
CONNECT={1 | 2}, NOCONNECT Specifies that a CONNECT statement is to be processed as either a type 1 CONNECT or a type 2 CONNECT.
CTRACE, NOCTRACE Creates a trace file for submission to technical support if requested. The filename of the file that is created is sqltrace.txt.
DB=database name, DB Specifies the name of the database that the program accesses. If DB is specified without a parameter, the database specified in the environment variable DB2DBDFT is used.
DEFERRED_PREPARE={NO | YES | ALL}, NODEFERRED_PREPARE Provides a performance enhancement when accessing DB2 common server databases or DRDA databases. This option combines the SQL PREPARE statement flow with the associated OPEN, DESCRIBE, or EXECUTE statement flow to minimize inter-process or network flow.
NO The PREPARE statement will be executed at the time it is issued.
YES Execution of the PREPARE statement will be deferred until the corresponding OPEN, DESCRIBE, or EXECUTE statement is issued. The PREPARE statement will not be deferred if it uses the INTO clause, which requires an SQLDA to be returned immediately. However, if the PREPARE INTO statement is issued for a cursor that does not use any parameter markers, the processing will be optimized by pre-OPENing the cursor when the PREPARE is executed.
ALL Same as YES, except that a PREPARE INTO statement which contains parameter markers is deferred. If a PREPARE INTO statement does not contain parameter markers, pre-OPENing of the cursor will still be performed. If the PREPARE statement uses the INTO clause to return an SQLDA, the application must not reference the content of this SQLDA until the OPEN, DESCRIBE, or EXECUTE statement is issued and returned.
DEGREE={1 | degree-of-parallelism | ANY}, NODEGREE Specifies whether or not the query is to be executed using I/O parallel processing.
1 Prohibits parallel I/O operations
degree-of-I/O-parallelism Specifies the degree of parallel I/O operations, a value between 2 and 32767 (inclusive)
ANY Allows parallel I/O operations.
DISCONNECT={EXPLICIT | CONDITIONAL | AUTOMATIC}, NODISCONNECT
AUTOMATIC Specifies that all database connections are to be disconnected at commit.
CONDITIONAL Specifies that the database connections that have been marked RELEASE or have no open WITH HOLD cursors are to be disconnected at commit.
EXPLICIT Specifies that only database connections that have been explicitly marked for release by the RELEASE statement are to be disconnected at commit.
EXPLAIN={NO | YES | ALL}, NOEXPLAIN Stores information in the Explain tables about the access plans chosen for each SQL statement in the package. DRDA does not support the ALL value for this option.
NO Explain information will not be captured.
YES Explain tables will be populated with information about the chosen access plan.
ALL Explain information for each eligible static SQL statement will be placed in the Explain tables. In addition, Explain information will be gathered for eligible dynamic SQL statements at run time, even if the CURRENT EXPLAIN SNAPSHOT register is set to NO. For more information about special registers, see the IBM DB2 SQL Reference.
EXPLSNAP={NO | YES | ALL}, NOEXPLSNAP Stores Explain Snapshot information in the Explain tables. This DB2 precompile/bind option is not supported by DRDA.
NO An Explain Snapshot will not be captured.
YES An Explain Snapshot for each eligible static SQL statement will be placed in the Explain tables.
ALL An Explain Snapshot for each eligible static SQL statement will be placed in the Explain tables. In addition, Explain Snapshot information will be gathered for eligible dynamic SQL statements at run time, even if the CURRENT EXPLAIN SNAPSHOT register is set to NO. For more information about special registers, see the IBM DB2 SQL Reference.
FORMAT={DEF | USA | EUR | ISO | JIS | LOC} Specifies the date and time format when date/time fields are assigned to string representations in host variables. DEF is a date and time format associated with the country code of the database.
EUR is the IBM standard for European date and time format.
ISO is the date and time format of the International Standards Organization.
JIS is the date and time format of the Japanese Industrial Standard.
LOC is the date and time format in local form associated with the country code of the database.
USA is the IBM standard for U.S. date and time format.

Synonym is DATETIME.

FUNCPATH=schema-name , NOFUNCPATH Specifies the function path to be used in resolving user-defined distinct types and functions in static SQL. If this option is not specified, the default function path is:

"SYSIBM","SYS FUN",USER

where USER is the value of the USER special register. This DB2 precompile/bind option is not supported by DRDA.

schema-name is a short SQL identifier, either ordinary or delimited, which identifies a schema that exists at the application server. No validation that the schema exists is made at precompile or at bind time. The same schema cannot appear more than once in the function path. The number of schemas that can be specified is limited by the length of the resulting function path, which cannot exceed 254 bytes. The schema SYSIBM does not need to be explicitly specified; it is implicitly assumed to be the first schema if it is not included in the function path. For more information, see the IBM DB2 SQL Reference.
IGNORE-NESTED=program-id, IGNORE-NESTED, NOIGNORE-NESTED In nested programs, specifies the program-id at which to start generating DB2 interface code. Any nested program encountered before the program-id is ignored and no DB2 interface code is generated. You must specify a program-id in the COBOL source code; otherwise, a compile error results. If you specify IGNORE-NESTED without a parameter, the program-id defaults to the program name with the filename extension replaced by .CBL.
INIT={PROT | s | x }, NOINIT Makes the program initialize SQL. This option is disabled if it is used within an OO program.
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
INSERT={DEF | BUF}, NOINSERT Allows a program being precompiled or bound from a DB2 V2.1 client to a DATABASE 2 Parallel Edition server to request that data inserts be buffered to increase performance.
BUF Specifies that inserts from an application should be buffered.
DEF Specifies that inserts from an application should not be buffered.
ISOLATION={CS | RR | UR | RS | NC} Determines how far a program bound to this package can be isolated from the effect of other executing programs. For more information about isolation levels, see the IBM DB2 SQL Reference.
CS specifies Cursor Stability as the isolation level.
NC (No Commit) specifies that commitment control is not to be used. This isolation level is not supported by DB2.
RR specifies Repeatable Read as the isolation level.
RS specifies Read Stability as the isolation level. Read Stability ensures that the execution of SQL statements in the package is isolated from other application processes for rows read and changed by the application.
UR specifies Uncommitted Read as the isolation level.
LANGLEVEL={SAA1 | NONE | MIA} For more information about this option, see the IBM DB2 Application Programming Guide.
MIA The FOR UPDATE clause is optional for positioned updates. C null-terminated strings are padded with blank characters, and always include the null-terminating character. This option is not supported by DDCS.
SAA1 Requires the FOR UPDATE clause for all columns that are updated in a positioned update. C null-terminated strings are not padded with blank characters, and do not include a null-terminating character if truncation occurs.
NONE Synonym for SAA1.

Synonym is STDLVL.

MSGAREA={data-item-name | MFSQLMESSAGETEXT },NOMSGAREA) Specifies the name of an alphanumeric data item. If this item is present in the program source it will automatically contain a description of a DB2 error condition (when SQLCODE is non zero).
NOT={ascii character code | 170 } Specifies the ASCII character code to use for NOT character (¬).
PASS={password |userid.password}, NOPASS Specifies the userid and password to connect to the database with.
QUALFIX, NOQUALFIX Causes the DB2 ECM to append three characters to the name of the host variables when declaring them to DB2. This ensures problems caused by qualification (where two or more host variables have identical names when not qualified) are avoided but has the side-effect that
(1) host variable names have a maximum length of 27 characters
(2) DB2 error messgaes will sometimes display the names of host variables with the three additional characters appended to them.
QUERYOPT= optimization-level, NOQUERYOPT Indicates the desired level of optimization for all static SQL statements contained in the package. The default value is 5. For the complete range of optimization levels available, see the SET CURRENT QUERY OPTIMIZATION statement in the SQL Reference. This DB2 precompile/bind option is not supported by DRDA.
SQLFLAG={MVSDB2V23 | MVSDB2V31 | MVSDB2V41}-SYNTAX, NOSQLFLAG Identifies and reports on deviations from the SQL language syntax specified.

A bind file or a package is created only if the bindfile or the package option is specified in addition to the sqlflag option.

Local syntax checking is performed only if one of the following options is specified: bindfile, package, sqlerror check, syntax.

If sqlflag is not specified, the flagger function is not invoked, and the bind file or the package is not affected.

MVSDB2V23-SYNTAX The SQL statements will be checked against MVS DB2 Version 2.3 SQL language syntax. Any deviation from the syntax is reported in the precompiler listing.
MVSDB2V31-SYNTAX The SQL statements will be checked against MVS DB2 Version 3.1 SQL language syntax. Any deviation from the syntax is reported in the precompiler listing.
MVSDB2V41-SYNTAX The SQL statements will be checked against MVS DB2 Version 4.1 SQL language syntax. Any deviation from the syntax is reported in the precompiler listing.

Synonym is FLAG.

SQLRULES={DB2 | STD}, NOSQLRULES Specifies whether type 2 CONNECTs are to be processed according to the DB2 rules or the Standard (STD) rules based on ISO/ANS SQL92.
DB2 Allow the use of the SQL CONNECT statement to switch the current connection to another established (dormant) connection.
STD Allow the use of the SQL CONNECT statement to establish a new connection only. The SQL SET CONNECTION statement must be used to switch to a dormant connection.

Synonym is RULES.

SQLWARN={YES | NO}, NOSQLWARN Indicates whether warnings will be returned from the compilation of dynamic SQL statements (via PREPARE or EXECUTE IMMEDIATE), or from describe processing (via PREPARE...INTO or DESCRIBE). This DB2 precompile/bind option is not supported by DRDA.
NO Warnings will not be returned from the SQL compiler.
YES Warnings will be returned from the SQL compiler.

Note: SQLCODE +238 is an exception. It is returned regardless of the sqlwarn option value.

Synonym is WARN.

SYNCPOINT={ONEPHASE | TWOPHASE | NONE}, NOSYNCPOINT Specifies how commits or rollbacks are to be coordinated among multiple database connections.
NONE Specifies that no Transaction Manager (TM) is to be used to perform a two-phase commit, and does not enforce single updater, multiple reader. A COMMIT is sent to each participating database. The application is responsible for recovery if any of the commits fail.
ONEPHASE Specifies that no TM is to be used to perform a two-phase commit. A one-phase commit is to be used to commit the work done by each database in multiple database transactions.
TWOPHASE Specifies that the TM is required to coordinate two-phase commits among those databases that support this protocol.

9.6.1.2 DRDA DB2 Directives

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

Option Description
ACTION={ADD | REPLACE }, NOACTION ACTION indicates whether the package can be added or replaced. This DRDA precompile/bind option is not supported by DB2.
ADD Indicates that the named package does not exist, and that a new package is to be created. If the package already exists, execution stops, and a diagnostic error message is returned.
REPLACE Indicates that the old package is to be replaced by a new one with the same location, collection, and package name.
CCSIDG=double-ccsid , NOCCSIDG An integer specifying the coded character set identifier (CCSID) to be used for double byte characters in character column definitions (without a specific CCSID clause) in CREATE and ALTER TABLE SQL statements. This DRDA precompile/bind option is not supported by DB2. The DRDA server will use a system defined default value if this option is not specified.
CCSIDM=mixed-ccsid , NOCCSIDM An integer specifying the coded character set identifier (CCSID) to be used for mixed byte characters in character column definitions (without a specific CCSID clause) in CREATE and ALTER TABLE SQL statements. This DRDA precompile/bind option is not supported by DB2. The DRDA server will use a system defined default value if this option is not specified.
CCSIDS=sbcs-ccsid, NOCCSIDS An integer specifying the coded character set identifier (CCSID) to be used for single byte characters in character column definitions (without a specific CCSID clause) in CREATE and ALTER TABLE SQL statements. This DRDA precompile/bind option is not supported by DB2. The DRDA server will use a system defined default value if this option is not specified.
CHARSUB={DEFAULT | BIT | SBCS | MIXED}, NOCHARSUB Designates the default character sub-type that is to be used for column definitions in CREATE and ALTER TABLE SQL statements. This DRDA precompile/bind option is not supported by DB2.
BIT Use the FOR BIT DATA SQL character sub-type in all new character columns for which an explicit sub-type is not specified.
DEFAULT Use the target system defined default in all new character columns for which an explicit sub-type is not specified.
MIXED Use the FOR MIXED DATA SQL character sub-type in all new character columns for which an explicit sub-type is not specified.
SBCS Use the FOR SBCS DATA SQL character sub-type in all new character columns for which an explicit sub-type is not specified.
DEC={31 | 15} , NODEC Specifies the maximum precision to be used in decimal arithmetic operations. This DRDA precompile/bind option is not supported by DB2. The DRDA server will use a system defined default value if this option is not specified.

Use 15 to specify 15-digit precision is used in decimal arithmetic operations.

Use 31 to specify 31-digit precision is used in decimal arithmetic operations.

DECDEL={PERIOD | COMMA}, NODECDEL Designates whether a period (.) or a comma (,) will be used as the decimal point indicator in decimal and floating point literals. This DRDA precompile/bind option is not supported by DB2. The DRDA server will use a system defined default value if this option is not specified.
COMMA Use a comma (,) as the decimal point indicator.
PERIOD Use a period (.) as the decimal point indicator.
DYNAMICRULES={BIND | RUN}, NODYNAMICRULES Specifies which authorization identifier to use when dynamic SQL in a package is executed. This DRDA precompile/bind option is not supported by DB2.
BIND Indicates that the authorization identifier used for the execution of dynamic SQL is the package owner.
RUN Indicates that the authorization identifier used for the execution of dynamic SQL is the authid of the person executing the package.
LEVEL=consistency-token, NOLEVEL Defines the level of a module using the consistency token. The consistency token is any alphanumeric value up to 8 characters in length. The RDB package consistency token verifies that the requester's application and the relational database package are synchronized. This DRDA precompile option is not supported by DB2.

Note: This option is not recommended for general use.

OWNER=authorization-id, NOOWNER Designates an 8-character authorization identifier for the package owner. The owner must have the privileges required to execute the SQL statements in the package. The default is the primary authorization ID of the precompile/bind process if this option has not been explicitly specified. This DRDA precompile/bind option is not supported by DB2.

Synonym is SCHEMA.

QUALIFIER=qualifier-name, NOQUALIFIER Provides an 18-character implicit qualifier for unqualified table names, views, indexes, and aliases contained in the package. The default is the owner's authorization ID, whether or not the owner is explicitly specified. This DRDA precompile/bind option is not supported by DB2.

Synonym is CATALOG.

RELEASE={COMMIT | DEALLOCATE}, NORELEASE Indicates whether resources are released at each COMMIT point, or when the application terminates. This DRDA precompile/bind option is not supported by DB2.
COMMIT Release resources at each COMMIT point. Used for dynamic SQL statements.
DEALLOCATE Release resources only when the application terminates.
REPLVER=version-id, NOREPLVER Replaces a specific version of a package. The version identifier specifies which version of the package is to be replaced. Maximum length is 254 characters.
RETAIN={YES | NO} , NORETAIN RETAIN indicates whether EXECUTE authorities are to be preserved when a package is replaced. If ownership of the package changes, the new owner grants the BIND and EXECUTE authority to the previous package owner.
NO does not preserve EXECUTE authorities when a package is replaced.
YES preserves EXECUTE authorities when a package is replaced.
SQLERROR={NOPACKAGE | CHECK | CONTINUE}, NOSQLERROR Indicates whether to create a package or a bind file if an error is encountered.
CHECK Specifies that the target system performs all syntax and semantic checks on the SQL statements being bound. A package will not be created as part of this process. If, while creating a package, an existing package with the same name and version is encountered, the existing package is neither dropped nor replaced if action replace was specified.
CONTINUE A package or a bind file is created even when SQL errors are encountered. This option is not supported by DB2.
NOPACKAGE A package or a bind file is not created if an error is encountered.

If syntax is used together with the package option, package is ignored.

Synonym is ERROR.

STRDEL={APOSTROPHE | QUOTE}, NOSTRDEL Designates whether an apostrophe (') or double quotation marks (") will be used as the string delimiter within SQL statements. This DRDA precompile/bind option is not supported by DB2. The DRDA server will use a system defined default value if this option is not specified.

Specify APOSTROPHE to use an apostrophe (') as the string delimiter.

Specify QUOTE to use double quotation marks (") as the string delimiter.

SYNTAX A synonym for directive SQLERROR=CHECK.
TEXT=label, NOTEXT The description of a package. Maximum length is 255 characters. The default value is blanks. This DRDA precompile/bind option is not supported by DB2.
VALIDATE={RUN | BIND}, NOVALIDATE Determines when the database manager checks for authorization errors and object not found errors. The package owner authorization ID is used for validity checking. This DRDA precompile/bind option is not supported by DB2.
BIND Validation is performed at precompile/bind time. If all objects do not exist, or all authority is not held, error messages are produced. If sqlerror continue is specified, a package/bind file is produced despite the error message, but the statements in error are not executable.
RUN Validation is attempted at bind time. If all objects exist, and all authority is held, no further checking is performed at execution time.

If all objects do not exist, or all authority is not held at precompile/bind time, warning messages are produced, and the package is successfully bound, regardless of the sqlerror continue option setting. However , authority checking and existence checking for SQL statements that failed these checks during the precompile/bind process may be redone at execution time.

VERSION=version-id, NOVERSION Defines the version identifier for a package. The version identifier is any alphanumeric value, $, #, @, _, -, or ., up to 254 characters in length. This DRDA precompile option is not supported by DB2.

9.7 Error Codes

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

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

For example:

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

9.8 Linking

To link an application:

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

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

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

  4. Set the Category to Advanced.

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

    db2api.lib

9.9 Binding

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

9.10 Publishing your DB2 Applications on UNIX

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

  1. Configure your login environment to set the COBOPT environment variable to the name of the database parameter file:
    COBOPT=/usr/lpp/db2_vv_rr_mmmm/lib/db2mkrts.args
    export COBOPT

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

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

  3. From the directory $COBDIR/src/sql enter:
    mkrts sqlinit.o
    cp rts32 $COBDIR


Copyright © 1998 Micro Focus Limited. All rights reserved.
This document and the proprietary marks and names used herein are protected by international law.
PreviousOpenESQL Assistant COBSQLNext