Appendix E.2 SQL data types and COBOL data descriptions

This section provides the correspondence between SQL data types and COBOL data descriptions.

Data can be exchanged between variables of compatible data types and between variables of either convertible or assignable data types.

Table E-8 shows SQL data types and COBOL data descriptions. Table E-9 shows SQL data types and COBOL data descriptions when arrays are used. Table E-10 shows SQL data types and COBOL data descriptions when repetition columns are used. Note that the data descriptions in these tables can also be coded as follows:

PICTURE:
PIC
COMPUTATIONAL:
COMP
COMPUTATIONAL-n:
COMP-n
9(n):
99 9
X(n):
XX X
OCCURS n TIMES:
OCCURS 1 TO n TIMES 0
OCCURS 1 TO n
OCCURS n

Table E-8 SQL data types and COBOL data descriptions

SQL data typeCOBOL data descriptionItem codingRemarks
SMALLINT
L1 elementary-item-name
PICTURE S9(4)
COMPUTATIONAL.
elementary-item or independent-item[Figure]
INTEGER
L1 elementary-item-name
PICTURE S9(9)
COMPUTATIONAL.
elementary-item or independent-item[Figure]
DECIMAL [(p[,s])]
L1 elementary-item-name
PICTURE S9(p-s) [V9(s)]
COMPUTATIONAL-3.
elementary-item or independent-itemIf 1 [Figure] p[Figure] 2910, 0 [Figure] s[Figure] p,
and p = s, then SV9(s).
If s = 0, then [V9(s)] is omitted.
L1 elementary-item-name
PICTURE S9(p-s)[V9(s)]
DISPLAY SIGN
LEADING SEPARATE.9
SMALLFLT (REAL)
L1 elementary-item-name
COMPUTATIONAL-1.
elementary-item or independent-item[Figure]
FLOAT (DOUBLE PRECISION)
L1 elementary-item-name
COMPUTATIONAL-2.
elementary-item or independent-item[Figure]
CHAR [(n)]
L1 elementary-item-name
PICTURE X(n).5
elementary-item or independent-item1 [Figure] n[Figure] 30000
VARCHAR (n)L2 group-item-name
L3 elementary-item-name-1
PICTURE S9(4)
COMPUTATIONAL.
L3 elementary-item-name-2
PICTURE X(n).5
A group item composed of two elementary items
elementary-item-name-1:
character-string-length
elementary-item-name-2:
character-string
1 [Figure] n[Figure] 32000
NCHAR [(n)]L1 elementary-item-name
PICTURE N(n).
elementary-item or independent-item1 [Figure] n[Figure] 15000
NVARCHAR (n)L2 group-item-name
L3 elementary-item-name-1
PICTURE S9(4)
COMPUTATIONAL.
L3 elementary-item-name-2
PICTURE N(n)
A group item composed of two elementary items
elementary-item-name-1:
character-string-length
elementary-item-name-2:
character-string
1 [Figure] n[Figure] 16000
MCHAR [(n)]L1 elementary-item-name
PICTURE X(n).6
elementary-item or independent-item1 [Figure] n[Figure] 30000
MVARCHAR (n)L2 group-item-name
L3 elementary-item-name-1
PICTURE S9(4)
COMPUTATIONAL.
L3 elementary-item-name-2
PICTURE X(n).6
A group item composed of two elementary items
elementary-item-name-1:
character-string-length
elementary-item-name-2:
character-string
1 [Figure] n[Figure]32000
DATE
L1 elementary-item-name
PICTURE X(10).6
elementary-item or independent-item[Figure]
TIME
L1 elementary-item-name
PICTURE X(8).6
elementary-item or independent-item[Figure]
TIMESTAMP[(p)]L1 elementary-item-name PICTURE X(n).6elementary-item or independent-itemIf p = 0, n = 19.
If p = 2, n = 21 or 22.
If p = 4, n = 23 or 24.
If p = 6, n = 25 or 26.
INTERVAL YEAR TO DAY
L1 elementary-item-name
PICTURE S9(8)
COMPUTATIONAL-3.
elementary-item or independent-item[Figure]
INTERVAL HOUR TO SECONDL1 elementary-item-name
PICTURE S9(6)
COMPUTATIONAL-3.
elementary-item or independent-item[Figure]
ROW3Combination of data items and group items in this table1A group item composed of elementary items1 [Figure] total-length
[Figure] 30000
BLOB
L2 group-item-name2
[USAGE [IS]]
SQL TYPE IS
BLOB(n[K |M |G]). 4, 7
elementary-itemDefault: 1 [Figure] n[Figure] 2147483647​
In units of K: 1 [Figure] n[Figure] 2097152​
In units of M: 1 [Figure] n[Figure] 2048
In units of G: 1 [Figure] n[Figure] 2
BINARY(n)L2 group-item-name.
L3 elementary-item-name-1
PICTURE S9(9)
COMPUTATIONAL.
L3 elementary-item-name-2
PICTURE X(n).5, 7
A group item composed of two elementary items
elementary-item-name-1:
character-string-length
elementary-item-name-2:
character-string
character-string-length is the byte count.
1 [Figure] n[Figure] 2147483647​
BLOB locatorL1 elementary-item-name
SQL TYPE IS
BLOB AS LOCATOR.8
elementary-item or independent-item
BINARY locatorL1 elementary-item-name
SQL TYPE IS
BINARY AS LOCATOR.8
elementary-item or independent-item
Indicator variableOther than BLOB, BINARY, BLOB locator, or BINARY locator
L1 elementary-item-name
PICTURE S9(4)
COMPUTATIONAL.
elementary-item or independent-item[Figure]
BLOB, BINARY, BLOB locator, or BINARY locator
L1 elementary-item-name
PICTURE S9(9)
COMPUTATIONAL.
SQL statementL2 group-item-name
L3 elementary-item-name-1
PICTURE S9(9)
COMPUTATIONAL.
L3 elementary-item-name-2
PICTURE X(n)
A group item composed of two elementary items
elementary-item-name-1:
character-string-length
elementary-item-name-2:
character-string
1 [Figure] n[Figure] 2000000​
Legend:
L1: Level number 01-49 or 77
L2: Level number 01-48
L3: Level number 02-49 (L2 < L3)
n: Length (bytes)
p: Precision (total number of digits)
s: Scale (number of digits beyond the decimal point)
1 The following clauses can be used:
  • REDEFINES
  • OCCURS
  • ADDRESSED BY
2 A group item name should be coded as no more than 21 characters. However, for COBOL2002, a group item name should be 22 characters or less.
3 Operations involving the ROW type are allowed only when the HiRDB server and the HiRDB client use the same endian type.
4 The coding of a BLOB UAP is expanded internally as follows:

L2 group-item-name.
49 group-item-name_RESERVED PIC S(9) USAGE IS BINARY. 1
49 group-item-name_LENGTH   PIC S(9) USAGE IS BINARY. 2
49 group-item-name_DATA     PIC X(m).                 3

  1. group-item-name_RESERVED is not used.
  2. group-item-name_LENGTH is equal to the BLOB actual length.
  3. group-item-name_DATA is the BLOB data storage area (where m denotes the actual data length).

5 This item can be defined using 9 in place of X. If 9 is used for definition, the operation when a character string containing a character other than a number is substituted or received as the retrieval result depends on the installed COBOL compiler.

6 Do not use 9 for X during definition, although using 9 does not cause an error during preprocessing.
7 The maximum value that can be declared depends on the installed COBOL compiler. For details, see the manual for the COBOL compiler to be used.
8 The following internal expansion takes place:

L1 elementary-item-name PICTURE S9(9) COMPUTATIONAL.

9 The data type for the HiRDB server is the DECIMAL type, but it is represented as a signed external decimal item of the numeric type.
10 The value range depends on the specifications of the COBOL compiler. For example, for COBOL85, the range is 1 [Figure] p[Figure] 18.

Table E-9 SQL data types and COBOL data descriptions when arrays are used

SQL data typeCOBOL data descriptionItem codingRemarks
SMALLINT
L2 elementary-item-name
PICTURE S9(4)
COMPUTATIONAL
OCCURS m TIMES.
A group item composed of repetitions of data items in which the same data structure is repeated through specification of OCCURS[Figure]
INTEGER
L2 elementary-item-name
PICTURE S9(9)
OCCURS m TIMES.
[Figure]
DECIMAL [(p[,s])]
L2 elementary-item-name
PICTURE S9
(p-s)[V9(s)]
COMPUTATIONAL-3
OCCURS m TIMES.
1 [Figure] p[Figure] 293,
0 [Figure] s[Figure] p
If p = s, SV9(s) is used.
If s = 0, [V9(s)] is omitted.
L2 elementary-item-name
PICTURE S9(p-s)[V9(s)]
DISPLAY SIGN LEADING
SEPARATE OCCURS m
TIMES.
SMALLFLT (REAL)
L2 elementary-item-name
COMPUTATIONAL-1
OCCURS m TIMES.
[Figure]
FLOAT (DOUBLE PRECISION)
L2 elementary-item-name
COMPUTATIONAL-2
OCCURS m TIMES.
[Figure]
CHAR [(n)]
L2 elementary-item-name
PICTURE X(n)
OCCURS m TIMES.1
1 [Figure] n[Figure] 30000
VARCHAR (n)L2 group-item-name
OCCURS m TIMES.
L3 elementary-item-name-1
PICTURE S9(4)
COMPUTATIONAL.
L3 elementary-item-name-2
PICTURE X(n).*
1 [Figure] n[Figure] 32000
NCHAR [(n)]
L2 elementary-item-name
PICTURE N(n)
OCCURS m TIMES.
1 [Figure] n[Figure] 15000
NVARCHAR (n)L2 group-item-name
OCCURS m TIMES.
L3 elementary-item-name-1
PICTURE S9(4)
COMPUTATIONAL.
L3 elementary-item-name-2
PICTURE N(n)
1 [Figure] n[Figure] 16000
MCHAR [(n)]
L2 elementary-item-name
PICTURE X(n)
OCCURS m TIMES.2
1 [Figure] n[Figure] 30000
MVARCHAR (n)L2 group-item-name-2
OCCURS m TIMES.
L3 elementary-item-name-1
PICTURE S9(4)
COMPUTATIONAL.
L3 elementary-item-name-2
PICTURE X(n).2
1 [Figure] n[Figure] 32000
DATE
L2 elementary-item-name
PICTURE X(10)
OCCURS m TIMES.2
[Figure]
TIME
L2 elementary-item-name
PICTURE X(8)
OCCURS m TIMES.2
[Figure]
TIMESTAMP(n)L2 elementary-item-name
PICTURE X(n)
OCCURS m TIMES.2
If p = 0, n = 19.
If p = 2, n = 21 or 22.
If p = 4, n = 23 or 24.
If p = 6, n = 25 or 26.
INTERVAL YEAR TO DAY
L2 elementary-item-name
PICTURE S9(8)
COMPUTATIONAL-3
OCCURS m TIMES.
[Figure]
INTERVAL HOUR TO SECOND
L2 elementary-item-name
PICTURE S9(6)
COMPUTATIONAL-3
OCCURS m TIMES.
[Figure]
ROW
L2 group-item-name-2
OCCURS m TIMES.
Combination of data items and group items in this table
[Figure]
BLOBCNCN[Figure]
BINARYL2 group-item-name-2
OCCURS m TIMES.
L3 elementary-item-name-1
PICTURE S9(9)
COMPUTATIONAL.
L3 elementary-item-name-2
PICTURE X(n).1
A group item composed of repetitions of data items in which the same data structure is repeated through specification of OCCURS.
  • FETCH that uses an array
    4 [Figure] n[Figure] 2147483644​ (n must be a multiple of 4.)
  • Other than FETCH that uses an array
4 [Figure] n[Figure] 32000 (n must be a multiple of 4.)
BLOB locator[Figure][Figure]
BINARY locatorL2 elementary-item-name
SQL TYPE IS
BINARY AS LOCATOR
OCCURS m TIMES.
Group item consisting of iterative data items that repeat the same data structure according to the OCCURS specification
Indicator variableOther than BINARY or BINARY locator
L2 elementary-item-name
PICTURE S9(4)
COMPUTATIONAL
OCCURS m TIMES.
[Figure]
BINARY or BINARY locatorL2 elementary-item-name
PICTURE S9(9)
COMPUTATIONAL
OCCURS m TIMES.
[Figure]
SQL statementCNCN[Figure]
Legend:
CN: Cannot be coded.
L2: Level number 02-49 (L2 < L3). You cannot specify level number 01, 66, 77, or 88 for L2. For details, see the syntax rules for the OCCURS clause in the COBOL manual.
L3: Level number 03-49
m: Number of array elements (1-4,096)
n: Length (bytes)
p: Precision (total number of digits)
s: Scale (number of digits beyond the decimal point)
1 This item can be defined using 9 in place of X. If 9 is used for definition, the operation when a character string containing a character other than a number is substituted or received as the retrieval result depends on the installed COBOL compiler.
2 Do not use 9 for X during definition, although using 9 does not result in an error during preprocessing.
3 The range depends on the specifications of the COBOL compiler. For example, for COBOL85, the range is 1 [Figure] p[Figure] 18.

Table E-10 SQL data types and COBOL data descriptions when repetition columns are used

SQL data typeCOBOL data descriptionItem codingRemarks
SMALLINTL2 group-item-name
L3 elementary-item-name-1
PICTURE S9(9) COMPUTATIONAL.
L3 elementary-item-name-2
PICTURE S9(4) COMPUTATIONAL OCCURS m TIMES.
A group item composed of two elementary items[Figure]
INTEGERL2 group-item-name
L3 elementary-item-name-1
PICTURE S9(9) COMPUTATIONAL.
L3 elementary-item-name-2
PICTURE S9(9) COMPUTATIONAL OCCURS m TIMES.
[Figure]
DECIMAL [(p[,s])]L2 group-item-name
L3 elementary-item-name-1
PICTURE S9(9) COMPUTATIONAL.
L3 elementary-item-name-2
PICTURE S9
(p-s)[V9(s)] COMPUTATIONAL-3 OCCURS m TIMES.
1 [Figure] p[Figure] 293,
0 [Figure] s[Figure] p
When p = s, SV9(s) is used.
When s = 0, [V9(s)] is omitted.
L2 group-item-name.
L3 elementary-item-name-1
PICTURE S9(9) COMPUTATIONAL.
L3 elementary-item-name-2
PICTURE S9(p-s)[V9(s)]
DISPLAY SIGN LEADING SEPARATE
OCCURS m TIMES.
SMALLFLT (REAL)L2 group-item-name
L3 elementary-item-name-1
PICTURE S9(9) COMPUTATIONAL.
L3 elementary-item-name-2
COMPUTATIONAL-1 OCCURS m TIMES.
[Figure]
FLOAT (DOUBLE PRECISION)L2 group-item-name
L3 elementary-item-name-1
PICTURE S9(9) COMPUTATIONAL.
L3 elementary-item-name-2
COMPUTATIONAL-2 OCCURS m TIMES.
[Figure]
CHAR [(n)]L2 group-item-name
L3 elementary-item-name-1
PICTURE S9(9) COMPUTATIONAL.
L3 elementary-item-name-2
PICTURE X(n) OCCURS m TIMES.1
1 [Figure] n[Figure] 30000
VARCHAR (n)L2 group-item-name
L3 elementary-item-name-1
PICTURE S9(9) COMPUTATIONAL.
L3 elementary-item-name-2
OCCURS m TIMES.
L4 elementary-item-name-3
PICTURE S9(4) COMPUTATIONAL.
L4 elementary-item-name-4
PICTURE X(n).1
A group item composed of two elementary items and a group item composed of one elementary item.1 [Figure] n[Figure] 32000
NCHAR [(n)]L2 group-item-name
L3 elementary-item-name-1
PICTURE S9(9) COMPUTATIONAL.
L3 elementary-item-name-2
PICTURE N(n) OCCURS m TIMES.
A group item composed of two elementary items.1 [Figure] n[Figure] 15000
NVARCHAR (n)L2 group-item-name
L3 elementary-item-name-1
PICTURE S9(9) COMPUTATIONAL.
L3 elementary-item-name-2
OCCURS m TIMES.
L4 elementary-item-name-3
PICTURE S9(4) COMPUTATIONAL.
L4 elementary-item-name-4
PICTURE N(n).
A group item composed of two elementary items and a group item composed of one elementary item.1 [Figure] n[Figure] 16000
MCHAR [(n)]L2 group-item-name
L3 elementary-item-name-1
PICTURE S9(9) COMPUTATIONAL.
L3 elementary-item-name-2
PICTURE X(n) OCCURS m TIMES.1
A group item composed of two elementary items.1 [Figure] n[Figure] 30000
MVARCHAR (n)L2 group-item-name
L3 elementary-item-name-1
PICTURE S9(9) COMPUTATIONAL.
L3 elementary-item-name-2
OCCURS m TIMES.
L4 elementary-item-name-3
PICTURE S9(4) COMPUTATIONAL.
L4 elementary-item-name-4
PICTURE X(n).1
A group item composed of two elementary items and a group item composed of one elementary item.1 [Figure] n[Figure] 32000
DATEL2 group-item-name
L3 elementary-item-name-1
PICTURE S9(9) COMPUTATIONAL.
L3 elementary-item-name-2
PICTURE X(10) OCCURS m TIMES.2
A group item composed of two elementary items.[Figure]
TIMEL2 group-item-name
L3 elementary-item-name-1
PICTURE S9(9) COMPUTATIONAL.
L3 elementary-item-name-2
PICTURE X(8 OCCURS m TIMES.2
[Figure]
TIMESTAMP[(n)]L2 group-item-name.
L3 elementary-item-name-1
PICTURE S9(9) COMPUTATIONAL.
L3 elementary-item-name-2
PICTURE X(n) OCCURS m TIMES.2
If p = 0, n = 19.
If p = 2, n = 21 or 22.
If p = 4, n = 23 or 24.
If p = 6, n = 25 or 26.
INTERVAL YEAR TO DAYL2 group-item-name
L3 elementary-item-name-1
PICTURE S9(8) COMPUTATIONAL.
L3 elementary-item-name-2
PICTURE S9(8) COMPUTATIONAL-3 OCCURS m TIMES.
[Figure]
INTERVAL HOUR TO SECONDL2 group-item-name
L3 elementary-item-name-1
PICTURE S9(6) COMPUTATIONAL.
L3 elementary-item-name-2
PICTURE S9(6) COMPUTATIONAL-3 OCCURS m TIMES.
[Figure]
ROWCNCN[Figure]
BLOBCNCN[Figure]
BINARYCNCN[Figure]
BLOB locatorCNCN[Figure]
BINARY locatorCNCN[Figure]
Indicator variable (other than BLOB, BINARY, BLOB locator, or BINARY locator)L2 group-item-name
L3 elementary-item-name-1
PICTURE S9(9) COMPUTATIONAL.
L3 elementary-item-name-2
PICTURE S9(4) COMPUTATIONAL OCCURS m TIMES.
A group item composed of two elementary items.[Figure]
SQL statementCNCN[Figure]
Legend:
CN: Cannot be coded.
L2: Level number 02-49
L3 and L4: Level number 03-49
m: Maximum number of repetition column elements (2-30000)
n: Length (bytes)
p: Precision (total number of digits)
s: Scale (number of digits beyond the decimal point)
Notes
  1. The value of elementary-item-name-1 must be the current element count.
  2. The values of elementary-item-name-2 and group-item-name-2 must be specified as the value of each repetition element.
  3. elementary-item-name-1 of the indicator variable must be specified as the indicator of the entire repetition column.
  4. elementary-item-name-2 of the indicator variable must be specified as the indicator of each repetition column element.

1 This item can be defined using 9 in place of X. If 9 is used for definition, the operation when a character string containing a character other than a number is substituted or received as the retrieval result depends on the installed COBOL compiler.

2 Do not use 9 for X during definition, although using 9 does not result in an error during preprocessing.

3 The range depends on the specifications of the COBOL compiler. For example, for COBOL85, the range is 1 [Figure] p[Figure] 18.