Special host-language programming considerations are discussed in the following pages. Included is information on language restrictions, host language specific include files, embedding SQL statements, host variables, and supported data types for host variables.
All API pointers are 4 bytes long. All integer variables used as value parameters in API calls must be declared with a USAGE COMP-5 clause.
By default, the input file has an extension of .sqb, but if you use the TARGET precompile option (TARGET ANSI_COBOL, TARGET IBMCOB, TARGET MFCOB or TARGET MFCOB16), the input file can have any extension you prefer.
By default, the output file has an extension of .cbl, but you can use the OUTPUT precompile option to specify a new name and path for the output modified source file.
The host-language-specific include files for COBOL have the file extension .cbl. The include files that are intended to be used in your applications are described below.
Embedded SQL statements consist of the following three elements:
For example:
EXEC SQL SELECT col INTO :hostvar FROM table END-EXEC.
The following rules apply to embedded SQL statements:
To locate the INCLUDE file, the DB2 COBOL precompiler searches the current directory first, then the directories specified by the DB2INCLUDE environment variable. Consider the following examples:
If the file specified in the INCLUDE statement is not enclosed in quotes, as above, the precompiler searches for payroll.sqb, then payroll.cpy, then payroll.cbl, in each directory in which it looks.
If the file name is enclosed in quotes, as above, no extension is added to the name.
If the file name in quotes does not contain an absolute path, then the contents of DB2INCLUDE are used to search for the file, prepended to whatever path is specified in the INCLUDE file name. For example, with DB2 for AIX, if DB2INCLUDE is set to '/disk2:myfiles/cobol', the precompiler searches for './pay/payroll.cbl', then '/disk2/pay/payroll.cbl', and finally './myfiles/cobol/pay/payroll.cbl'. The path where the file is actually found is displayed in the precompiler messages. On OS/2 and Windows platforms, substitute back slashes (\) for the forward slashes in the above example.
| Note: | The setting of DB2INCLUDE is cached by the DB2 Command Line Processor. To change the setting of DB2INCLUDE after any CLP commands have been issued, enter the TERMINATE command, then reconnect to the database and precompile as usual. |
Note that the actual characters used for end-of-line and TAB vary from platform to platform. For example, OS/2 uses Carriage Return/Line Feed for end-of-line, whereas UNIX-based systems use just a Line Feed.
Host variables are COBOL language variables that are referenced within SQL statements. They allow an application to pass input data to the database manager and receive output data from the database manager. After the application is precompiled, host variables are used by the compiler as any other COBOL variable. Obey the rules described below when naming, declaring, and using host variables.
The SQL precompiler identifies host variables by their declared name. The following rules apply:
SQL interprets a hyphen enclosed by spaces as a subtraction operator. Use hyphens without spaces in host variable names.
An SQL declare section must be used to identify host variable declarations. This alerts the precompiler to any host variables that can be referenced in subsequent SQL statements.
The COBOL precompiler only recognizes a subset of valid COBOL declarations.
Figure 37 shows the syntax for numeric host variables.
Figure 37. Syntax for Numeric Host Variables in COBOL
>>--.-01-.--variable-name--.-PICTURE-.-------------->
'-77-' '-PIC-----'
.-IS-.
>--'----'--picture-string--------------------------->
>--.----------------------------------------.------->
| (1) |
'-.---------------.--.-COMP-3----------.-'
| .-IS-. | |-COMPUTATIONAL-3-|
'-USAGE--'----'-' |-COMP-5----------|
'-COMPUTATIONAL-5-'
>--.----------------------.--.--><
| .-IS-. |
'-VALUE--'----'--value-'
NOTE:
(1) An alternative for COMP-3 is PACKED-DECIMAL.
>>--.-01-.--variable-name--.---------------.-------->
'-77-' | .-IS-. |
'-USAGE--'----'-'
(1)
>--.-.-COMPUTATIONAL-1-.-----.---------------------->
| '-COMP-1----------' |
| (2) |
'-.-COMPUTATIONAL-2-.-----'
'-COMP-2----------'
>--.----------------------.--.---------------------><
| .-IS-. |
'-VALUE--'----'--value-'
NOTES:
(1) REAL (SQLTYPE 480), Length 4
(2) DOUBLE (SQLTYPE 480), Length 8
|
Notes:
Figure 38 shows the syntax for character host variables.
Figure 38. Syntax for Character Host Variables in COBOL
Fixed Length >>--.-01-.--variable-name--.-PICTURE-.-------------->
'-77-' '-PIC-----'
.-IS-.
>--'----'--picture-string--------------------------->
>--.----------------------.--.--><
| .-IS-. |
'-VALUE--'----'--value-'
Variable Length >>--01--variable-name--.--><
.-IS-.
>>--49--identifier-1--.-PICTURE-.--'----'--S9(4)---->
'-PIC-----'
>--.----------------------------------------.------->
'-.---------------.--.-COMP-5----------.-'
| .-IS-. | '-COMPUTATIONAL-5-'
'-USAGE--'----'-'
>--.----------------------.--.--><
| .-IS-. |
'-VALUE--'----'--value-'
>>--49--identifier-2--.-PICTURE-.------------------->
'-PIC-----'
.-IS-.
>--'----'--picture-string--------------------------->
>--.----------------------.--.--><
| .-IS-. |
'-VALUE--'----'--value-'
|
Notes:
EXEC SQL CONNECT TO :dbname USER :userid USING :p-word
END-EXEC.
However, because blanks can be significant in passwords, the p-word host variable should be declared as a VARCHAR data item, so that your application can explicitly indicate the significant password length for the CONNECT statement as follows:
EXEC SQL BEGIN DECLARE SECTION END-EXEC.
01 dbname PIC X(8).
01 userid PIC X(8).
01 p-word.
49 L PIC S9(4) COMP-5.
49 D PIC X(18).
EXEC SQL END DECLARE SECTION END-EXEC.
PROCEDURE DIVISION.
MOVE "sample" TO dbname.
MOVE "userid" TO userid.
MOVE "password" TO D OF p-word.
MOVE 8 TO L of p-word.
EXEC SQL CONNECT TO :dbname USER :userid USING :p-word
END-EXEC.
Figure 39 shows the syntax for graphic host variables.
Figure 39. Syntax for Graphic Host Variables in COBOL
Fixed Length >>--.-01-.--variable-name--.-PICTURE-.-------------->
'-77-' '-PIC-----'
.-IS-.
>--'----'--picture-string--USAGE-------------------->
.-IS-.
>--'----'--DISPLAY-1--.----------------------.------>
| .-IS-. |
'-VALUE--'----'--value-'
Variable Length >>--01--variable-name--.--><
.-IS-.
>>--49--identifier-1--.-PICTURE-.--'----'--S9(4)---->
'-PIC-----'
>--.----------------------------------------.------->
'-.---------------.--.-COMP-5----------.-'
| .-IS-. | '-COMPUTATIONAL-5-'
'-USAGE--'----'-'
>--.----------------------.--.--><
| .-IS-. |
'-VALUE--'----'--value-'
>>--49--identifier-2--.-PICTURE-.------------------->
'-PIC-----'
.-IS-.
>--'----'--picture-string--USAGE-------------------->
.-IS-.
>--'----'--DISPLAY-1--.----------------------.------>
| .-IS-. |
'-VALUE--'----'--value-'
|
Notes:
Indicator variables should be declared as a PIC S9(4) COMP-5 data type.
Figure 40 shows the syntax for declaring large object (LOB) host variables in COBOL.
Figure 40. Syntax for LOB Host Variables in COBOL
>>--01--variable-name--.---------------.------------>
'-USAGE-.----.--'
'-IS-'
>--SQL TYPE IS--.-BLOB---.-------------------------->
|-CLOB---|
'-DBCLOB-'
>--(--length--.---.--)--.--------------------------><
|-K-|
|-M-|
'-G-'
|
Notes:
BLOB Example:
Declaring:
01 MY-BLOB USAGE IS SQL TYPE IS BLOB(2M).
Results in the generation of the following structure:
01 MY-BLOB.
49 MY-BLOB-LENGTH PIC S9(9) COMP-5.
49 MY-BLOB-DATA PIC X(2097152).
CLOB Example:
Declaring:
01 MY-CLOB USAGE IS SQL TYPE IS CLOB(125M).
Results in the generation of the following structure:
01 MY-CLOB.
49 MY-CLOB-LENGTH PIC S9(9) COMP-5.
49 MY-CLOB-DATA PIC X(131072000).
DBCLOB Example:
Declaring:
01 MY-DBCLOB USAGE IS SQL TYPE IS DBCLOB(30000).
Results in the generation of the following structure:
01 MY-DBCLOB.
49 MY-DBCLOB-LENGTH PIC S9(9) COMP-5.
49 MY-DBCLOB-DATA PIC G(30000) DISPLAY-1.
Figure 41 shows the syntax for declaring large object (LOB) locator host variables in COBOL.
Figure 41. Syntax for LOB Locator Host Variables in COBOL
>>--01--variable-name--.---------------.------------>
'-USAGE-.----.--'
'-IS-'
>--SQL TYPE IS--.-BLOB-LOCATOR---.--.--------------><
|-CLOB-LOCATOR---|
'-DBCLOB-LOCATOR-'
|
Notes:
BLOB Locator Example (other LOB locator types are similar):
Declaring:
01 MY-LOCATOR USAGE SQL TYPE IS BLOB-LOCATOR.
Results in the generation of the following declaration:
01 MY-LOCATOR PIC S9(9) COMP-5.
Figure 42 shows the syntax for declaring file reference host variables in COBOL.
Figure 42. Syntax for File Reference Host Variables in COBOL
>>--01--variable-name--.---------------.------------>
'-USAGE-.----.--'
'-IS-'
>--SQL TYPE IS--.-BLOB-FILE---.--.-----------------><
|-CLOB-FILE---|
'-DBCLOB-FILE-'
|
BLOB File Reference Example (other LOB types are similar):
Declaring:
01 MY-FILE USAGE IS SQL TYPE IS BLOB-FILE.
Results in the generation of the following declaration:
01 MY-FILE.
49 MY-FILE-NAME-LENGTH PIC S9(9) COMP-5.
49 MY-FILE-DATA-LENGTH PIC S9(9) COMP-5.
49 MY-FILE-FILE-OPTIONS PIC S9(9) COMP-5.
49 MY-FILE-NAME PIC X(255).
The COBOL precompiler supports declarations of group data items in the host variable declare section. Among other things, this provides a shorthand for referring to a set of elementary data items in an SQL statement. For example, the following group data item can be used to access the STAFF table in the SAMPLE database:
01 staff-record.
05 staff-id pic s9(4) comp-5.
05 staff-name.
49 l pic s9(4) comp-5.
49 d pic x(9).
05 staff-dept pic s9(4) comp-5.
05 staff-job pic x(5).
05 staff-years pic s9(4) comp-5.
05 staff-salary pic s9(5)v99 comp-3.
05 staff-comm pic s9(5)v99 comp-3.
Group data items in the declare section can have any of the valid host variable types described above as subordinate data items. This includes all numeric and character types, as well as all large object types. Group data items cannot be nested. The exception is the varchar character type, shown in staff-name above. It is permitted within a group data item because the precompiler treats it as a single host variable instead of a group. Note that you must declare varchar character types with the subordinate items at level 49, as in the above example. If they are not at level 49, the varchar is treated as a group data item with two subordinates, and is subject to the rules of declaring and using group data items. You must declare group data items at the 01-level, and the subordinate items within them at a common level between 02 and 49. For example, all the subordinates in staff-record above are at the 05 level. FILLER items are not permitted within group data items.
You can use group data items and their subordinates in three ways:
The entire group may be referenced as a single host variable in an SQL statement:
EXEC SQL SELECT ID,NAME,DEPT,JOB,YEARS,SALARY,COM
INTO :staff-record
FROM STAFF WHERE ID = 10 END-EXEC.
The precompiler converts the reference to staff-record into a list, separated by commas, of all the subordinate items declared within staff-record. Each elementary item is qualified with the group name to prevent naming conflicts with other items. This is equivalent to the following method.
The second way of using group data items:
EXEC SQL SELECT id,name,dept,job,years,salary,com
INTO
:staff-record.staff-id,
:staff-record.staff-name,
:staff-record.staff-dept,
:staff-record.staff-job,
:staff-record.staff-years,
:staff-record.staff-salary,
:staff-record.staff-comm
FROM staff WHERE id = 10 END-EXEC.
Note that the reference to staff-id is qualified with its group name, using the staff-record. prefix, and not staff-id of staff-record as in pure COBOL. Assuming there are no other host variables with the same names as the subordinates of staff-record, the above statement can also be coded as in method 3, eliminating the explicit group qualification.
Here, subordinate items are referenced in a typical COBOL fashion, without being qualified to their particular group item.
EXEC SQL SELECT ID, NAME, DEPT, JOB, YEARS, SALARY, COM
INTO
:staff-id,
:staff-name,
:staff-dept,
:staff-job,
:staff-years,
:staff-salary,
:staff-comm
FROM STAFF WHERE ID = 10 END-EXEC.
As in pure COBOL, this method is acceptable to the precompiler as long as a given subordinate item only occurs in one group declared as a host variable. If, for example, staff-id occurs in more than one group, the precompiler issues an error indicating an ambiguous reference:
SQL0088N Host variable "staff-id" is ambiguous.
Because a reference to a group item alone, as in method 1, is equivalent to a comma-separated list of its subordinates, there are instances where this type of reference leads to an error. For example:
EXEC SQL CONNECT TO :staff-record END-EXEC.
Here, the CONNECT statement expects a single character-based host variable. By giving the staff-record group data item instead, the host variable results in the following precompile-time error:
SQL0087N Host variable "staff-record" is a structure used where
structure references are not permitted.
Other uses of group items which cause an SQL0087N to occur include PREPARE, EXECUTE IMMEDIATE, CALL, indicator variables, and SQLDA references. Groups with only one subordinate are permitted in such situations, as are references to individual subordinates, as in method 2 and 3 above.
The COBOL precompiler supports the declaration of tables of indicator variables, which are convenient to use with group data items. They are declared as follows:
01 <indicator-table-name>.
05 <indicator-name> pic s9(4) comp-5
occurs <table-size> times.
For example:
01 staff-indicator-table.
05 staff-indicator pic s9(4) comp-5
occurs 7 times.
This indicator table can be used effectively with the first format of group item reference above:
EXEC SQL SELECT ID, NAME, DEPT, JOB, YEARS, SALARY, COM
INTO :staff-record :staff-indicator
FROM STAFF WHERE ID = 10 END-EXEC.
Here, the precompiler detects that staff-indicator was declared as an indicator table, and expands it into individual indicator references when it processes the SQL statement. staff-indicator(1) is associated with staff-id of staff-record, staff-indicator(2) is associated with staff-name of staff-record, and so on.
| Note: | If there are k more indicator entries in the indicator table than there are subordinates in the data item, (for example, if staff-indicator has 10 entries, making k=3), the k extra entries at the end of the indicator table are ignored. Likewise, if there are k fewer indicator entries than subordinates, the last k subordinates in the group item do not have indicators associated with them. Note that you cannot individually refer to elements in an indicator table in an SQL statement. |
The DB2 COBOL precompiler supports the use of BINARY, COMP, and COMP-4 data types wherever integer host variables and indicators are permitted, as long as the target COBOL compiler views (or can be made to view) the BINARY, COMP, or COMP-4 data types as equivalent to the COMP-5 data type. In this book, such host variables and indicators are shown with type COMP-5. Target compilers supported by DB2 that treat COMP, COMP-4, BINARY COMP and COMP-5 as equivalent are:
You can use the REDEFINES clause when declaring host variables. If you declare a member of a group data item with the REDEFINES clause and that group data item is referred to as a whole in an SQL statement, any subordinate items containing the REDEFINES clause are not expanded. For example:
01 foo.
10 a pic s9(4) comp-5.
10 a1 redefines a pic x(2).
10 b pic x(10).
Referring to foo in an SQL statement as follows:
... INTO :foo ...
The above statement is equivalent to:
... INTO :foo.a, :foo.b ...
That is, the subordinate item a1, declared with the REDEFINES clause is not automatically expanded out in such situations. If a1 is unambiguous, you can explicitly refer to a subordinate with a REDEFINES clause in an SQL statement, as follows:
... INTO :foo.a1 ...
or
... INTO :a1 ...
Certain predefined COBOL data types correspond to column types. Only these COBOL data types can be declared as host variables.
Table 17 shows the COBOL equivalent of each column type. When the precompiler finds a host variable declaration, it determines the appropriate SQL type value. The database manager uses this value to convert the data exchanged between the application and itself.
Not every possible data description for host variables is
recognized. COBOL data items must be consistent with the ones described
in the following table. If you use other data items, an error can
result.
Table 17. SQL Data Types Mapped to COBOL Declarations
| SQL Column Type1 | COBOL Data Type | SQL Column Type Description | ||
|---|---|---|---|---|
SMALLINT (500 or 501) | 01 name PIC S9(4) COMP-5. | 16-bit signed integer | ||
INTEGER (496 or 497) | 01 name PIC S9(9) COMP-5. | 32-bit signed integer | ||
DECIMAL(p,s) (484 or 485) | 01 name PIC S9(m) V9(n) COMP-3. | Packed decimal | ||
REAL2 (480 or 481) | 01 name USAGE IS COMP-1. | Single-precision floating point | ||
DOUBLE3 (480 or 481) | 01 name USAGE IS COMP-2. | Double-precision floating point | ||
CHAR(n) (452 or 453) | 01 name PIC X(n). | Fixed-length character string | ||
VARCHAR(n) (448 or 449) |
01 name. 49 length PIC S9(4) COMP-5. 49 name PIC X(n). 1<=n<=4000 | Variable-length character string | ||
LONG VARCHAR (456 or 457) |
01 name. 49 length PIC S9(4) COMP-5. 49 data PIC X(n). 4001<=n<=32 700 | Long variable-length character string | ||
CLOB(n) (408 or 409) |
01 MY-CLOB USAGE IS SQL TYPE IS CLOB(n). 1<=n<=2 147 483 647 | Large object variable-length character string | ||
CLOB locator variable4 (964 or 965) | 01 MY-CLOB-LOCATOR USAGE IS SQL TYPE IS CLOB-LOCATOR. | Identifies CLOB entities residing on the server | ||
CLOB file reference variable4 (808 or 809) | 01 MY-CLOB-FILE USAGE IS SQL TYPE IS CLOB-FILE. | Descriptor for file containing CLOB data | ||
BLOB(n) (404 or 405) | 01 MY-BLOB USAGE IS SQL TYPE IS BLOB(n).
1<=n<=2 147 483 647 | Large object variable-length binary string | ||
BLOB locator variable4 (960 or 961) | 01 MY-BLOB-LOCATOR USAGE IS SQL TYPE IS BLOB-LOCATOR. | Identifies BLOB entities residing on the server | ||
BLOB file reference variable4 (804 or 805) | 01 MY-CLOB-FILE USAGE IS SQL TYPE IS CLOB-FILE. | Descriptor for file containing CLOB data | ||
DATE (384 or 385) | 01 identifier PIC X(10). | 10-byte character string | ||
TIME (388 or 389) | 01 identifier PIC X(8). | 8-byte character string | ||
TIMESTAMP (392 or 393) | 01 identifier PIC X(26). | 26-byte character string | ||
| ||||
GRAPHIC(n) (468 or 469) | 01 name PIC G(n) DISPLAY-1. | Fixed-length double-byte character string | ||
VARGRAPHIC(n) (464 or 465) |
01 name. 49 length PIC S9(4) COMP-5. 49 name PIC G(n) DISPLAY-1. 1<=n<=2 000 | Variable length double-byte character string with 2-byte string length indicator | ||
LONG VARGRAPHIC (472 or 473) |
01 name. 49 length PIC S9(4) COMP-5. 49 name PIC G(n) DISPLAY-1. 2001<=n<=16 350 | Variable length double-byte character string with 2-byte string length indicator | ||
DBCLOB(n) (412 or 413) | 01 MY-DBCLOB USAGE IS SQL TYPE IS DBCLOB(n).
1<=n<=1 073 741 823 | Large object variable length double-byte character string with 4-byte string length indicator | ||
DBCLOB locator variable4 (968 or 969) | 01 MY-DBCLOB-LOCATOR USAGE IS SQL TYPE IS DBCLOB-LOCATOR. | Identifies DBCLOB entities residing on the server | ||
DBCLOB file reference variable4 (812 or 813) | 01 MY-DBCLOB-FILE USAGE IS SQL TYPE IS DBCLOB-FILE. | Descriptor for file containing DBCLOB data | ||
The following is a sample SQL declare section with a host variable declared for each supported SQL data type.
EXEC SQL BEGIN DECLARE SECTION END-EXEC.
*
01 age PIC S9(4) COMP-5.
01 divis PIC S9(9) COMP-5.
01 salary PIC S9(6)V9(3) COMP-3.
01 bonus USAGE IS COMP-1.
01 wage USAGE IS COMP-2.
01 nm PIC X(5).
01 varchar.
49 leng PIC S9(4) COMP-5.
49 strg PIC X(14).
01 longvchar.
49 len PIC S9(4) COMP-5.
49 str PIC X(6027).
01 MY-CLOB USAGE IS SQL TYPE IS CLOB(1M).
01 MY-CLOB-LOCATOR USAGE IS SQL TYPE IS CLOB-LOCATOR.
01 MY-CLOB-FILE USAGE IS SQL TYPE IS CLOB-FILE.
01 MY-BLOB USAGE IS SQL TYPE IS BLOB(1M).
01 MY-BLOB-LOCATOR USAGE IS SQL TYPE IS BLOB-LOCATOR.
01 MY-BLOB-FILE USAGE IS SQL TYPE IS BLOB-FILE.
01 MY-DBCLOB USAGE IS SQL TYPE IS DBCLOB(1M).
01 MY-DBCLOB-LOCATOR USAGE IS SQL TYPE IS DBCLOB-LOCATOR.
01 MY-DBCLOB-FILE USAGE IS SQL TYPE IS DBCLOB-FILE.
01 MY-PICTURE PIC G(16000) USAGE IS DISPLAY-1.
01 dt PIC X(10).
01 tm PIC X(8).
01 tmstmp PIC X(26).
01 wage-ind PIC S9(4) COMP-5.
*
EXEC SQL END DECLARE SECTION END-EXEC.
The following are additional rules for supported COBOL data types:
01 identifier PIC S9(m)V9(n) COMP-3
01 identifier PIC S9(3)V COMP-3
01 identifier PIC SV9(3) COMP-3
01 identifier PIC S9V COMP-3
01 identifier PIC SV9 COMP-3
Certain database columns can be declared FOR BIT DATA. These columns, which generally contain characters, are used to hold binary information. The CHAR(n), VARCHAR, LONG VARCHAR, and BLOB data types are the COBOL host variable types that can contain binary data. Use these data types when working with columns with the FOR BIT DATA attribute.
When using the LANGLEVEL precompile option with a value of SQL92E, the following two declarations may be included as host variables:
EXEC SQL BEGIN DECLARE SECTION END-EXEC. 01 SQLSTATE PICTURE X(5). 01 SQLCODE PICTURE S9(9) USAGE COMP. . . . EXEC SQL END DECLARE SECTION END-EXEC.
If neither of these is specified, the SQLCODE declaration is assumed during the precompile step. The '01' can also be '77' and the 'PICTURE' can be 'PIC'. Note that when using this option, the INCLUDE SQLCA statement should not be specified.
For applications made up of multiple source files, the SQLCODE and SQLSTATE declarations may be included in each source file as shown above.
Any graphic data sent from your application running under an EUC code set is tagged with the UCS-2 code page identifier, so your application must convert an EUC string to UCS-2 before sending it to a database server. Likewise, graphic data retrieved from a database by your application running under an EUC code is encoded using UCS-2. This requires your application to convert from UCS-2 to EUC internally, unless the user is to be presented with UCS-2 data. You are responsible for converting to and from UCS-2 since this conversion must be conducted before the data is copied to, and after it is copied from, the SQLDA. No application-accessible conversion routines are supplied with the DB2 Universal Database products. Use system calls available from your operating system.
For general EUC application development guidelines, see "Japanese and Traditional-Chinese EUC Code Set Considerations".
If you are using Object Oriented COBOL, you must observe the following: