Determining equivalent SQL and COBOL data types


The precompiler determines the base SQLTYPE and SQLLEN of host variables based on this table. If a host variable appears with an indicator variable, the SQLTYPE is the base SQLTYPE plus one.
COBOL declarations mapped to typical SQL data types

COBOL data type
SQLTYPE of host variable
SQLLEN of host variable
SQL data type
S9(i)V9(d) COMP-3 or S9(i)V9(d) COMP or S9(i)V9(d) PACKED-DECIMAL
484
i+d in byte 1, d in byte 2
DECIMAL(i+d,d)
S9(i)V9(d) DISPLAY SIGN LEADING SEPARATE
504
i+d in byte 1, d in byte 2
No exact equivalent use DECIMAL(i+d,d) or NUMERIC (i+d,d)
S9(i)V9(d)DISPLAY
488
i+d in byte 1, d in byte 2
NUMERIC(i+d,d)
S9(i) BINARY or S9(i) COMP-4 where i is from 1 to 4
500
2
SMALLINT
S9(i) BINARY or S9(i) COMP-4 where i is from 5 to 9
496
4
INTEGER
S9(i) BINARY or S9(i) COMP-4 where i is from 10 to 18.
Not supported by OPM COBOL.
492
8
BIGINT
S9(i)V9(d) BINARY or S9(i)V9(d) COMP-4 where i+d ≤ 4
500
i+d in byte 1, d in byte 2
No exact equivalent use DECIMAL(i+d,d) or NUMERIC (i+d,d)
S9(i)V9(d) BINARY or S9(i)V9(d) COMP-4 where 4 < i+d ≤ 9
496
i+d in byte 1, d in byte 2
No exact equivalent use DECIMAL(i+d,d) or NUMERIC (i+d,d)
COMP-1
Not supported by OPM COBOL.
480
4
FLOAT(single precision)
COMP-2
Not supported by OPM COBOL.
480
8
FLOAT(double precision)
Fixed-length character data
452
m
CHAR(m)
Varying-length character data
448
m
VARCHAR(m)
Fixed-length graphic data
Not supported by OPM COBOL.
468
m
GRAPHIC(m)
Varying-length graphic data
Not supported by OPM COBOL.
464
m
VARGRAPHIC(m)
DATE
Not supported by OPM COBOL.
384

DATE
TIME
Not supported by OPM COBOL.
388

TIME
TIMESTAMP
Not supported by OPM COBOL.
392
26
TIMESTAMP
The following table can be used to determine the COBOL data type that is equivalent to a given SQL data type.
SQL data types mapped to typical COBOL declarations

SQL data type
COBOL data type
Notes
SMALLINT
S9(m) COMP-4
m is from 1 to 4
INTEGER
S9(m) COMP-4
m is from 5 to 9
BIGINT
S9(m) COMP-4 for ILE COBOL.
Not supported by OPM COBOL.
m is from 10 to 18
DECIMAL(p,s)
If p<64: S9(p-s)V9(s) PACKED-DECIMAL or S9(p-s)V9(s) COMP or S9(p-s)V9(s) COMP-3. If p>63: Not supported
p is precision; s is scale. 0<=s<=p<=63. If s=0, use S9(p) or S9(p)V. If s=p, use SV9(s).
NUMERIC(p,s)
If p<19: S9(p-s)V9(s) DISPLAY If p>18: Not supported
p is precision; s is scale. 0<=s<=p<=18. If s=0, use S9(p) or S9(p)V. If s=p, use SV9(s).
FLOAT(single precision)
COMP-1 for ILE COBOL.
Not supported by OPM COBOL.

FLOAT(double precision)
COMP-2 for ILE COBOL.
Not supported by OPM COBOL.

CHAR(n)
Fixed-length character string
32766≥n≥1
VARCHAR(n)
Varying-length character string
32740≥n≥1
CLOB
None
Use SQL TYPE IS to declare a CLOB for ILE COBOL.
Not supported by OPM COBOL.
GRAPHIC(n)
Fixed-length graphic string for ILE COBOL.
Not supported by OPM COBOL.
16383≥n≥1
VARGRAPHIC(n)
Varying-length graphic string for ILE COBOL.
Not supported by OPM COBOL.
16370≥n≥1
DBCLOB
None
Not supported by OPM COBOL.
Use SQL TYPE IS to declare a DBCLOB for ILE COBOL.
BINARY
None
Use SQL TYPE IS to declare a BINARY.
VARBINARY
None
Use SQL TYPE IS to declare a VARBINARY.
BLOB
None
Not supported by OPM COBOL.
Use SQL TYPE IS to declare a BLOB.
DATE
Fixed-length character string or DATE for ILE COBOL.
If the format is *USA, *JIS, *EUR, or *ISO, allow at least 10 characters. If the format is *YMD, *DMY, or *MDY, allow at least 8 characters. If the format is *JUL, allow at least 6 characters.
TIME
Fixed-length character string or TIME for ILE COBOL.
Allow at least 6 characters; 8 to include seconds.
TIMESTAMP
Fixed-length character string or TIMESTAMP for ILE COBOL.
n must be at least 19. To include microseconds at full precision, n must be 26. If n is less than 26, truncation occurs on the microseconds part.
DATALINK
Not supported
ROWID None Use SQL TYPE IS to declare a ROWID.

Equivalences SQL and COBOL data types:
When you declare host variables in your COBOL programs, the precompiler uses equivalent SQL data types. When you retrieve data of a particular SQL data type into a host variable, you need to ensure that the host variable is of an equivalent data type.
The following table describes the SQL data type and the base SQLTYPE and SQLLEN values that the precompiler uses for host variables in SQL statements.
SQL data types, SQLLEN values, and SQLTYPE values that the precompiler uses for host variables in COBOL programs
COBOL host variable data type
SQLTYPE of host variable1
SQLLEN of host variable
SQL data type
COMP-1 480 4 REAL or FLOAT(n) 1<=n<=21
COMP-2 480 8 DOUBLE PRECISION, or FLOAT(n) 22<=n<=53
S9(i)V9(d) COMP-3 or S9(i)V9(d) PACKED-DECIMAL 484 i+d in byte 1, d in byte 2 DECIMAL(i+d,d) or NUMERIC(i+d,d)
S9(i)V9(d) DISPLAY SIGN LEADING SEPARATE 504 i+d in byte 1, d in byte 2 No exact equivalent. Use DECIMAL(i+d,d) or NUMERIC(i+d,d)
S9(i)V9(d) NATIONAL SIGN LEADING SEPARATE 504 i+d in byte 1, d in byte 2 No exact equivalent. Use DECIMAL(i+d,d) or NUMERIC(i+d,d)
S9(4) COMP-4, S9(4) COMP-5, S9(4) COMP, or S9(4) BINARY 500 2 SMALLINT
S9(9) COMP-4, S9(9) COMP-5, S9(9) COMP, or S9(9) BINARY 496 4 INTEGER
S9(18) COMP-4, S9(18) COMP-5, S9(18) COMP, or S9(18) BINARY 492 8 BIGINT
Fixed-length character data 452 n CHAR(n)
Varying-length character data 1<=n<=255 448 n VARCHAR(n)
Varying-length character data m>255 456 m VARCHAR(m)
Fixed-length graphic data 468 m GRAPHIC(m)
Varying-length graphic data 1<=m<=127 464 m VARGRAPHIC(m)
Varying-length graphic data m>127 472 m VARGRAPHIC(m)
SQL TYPE is BINARY(n), 1<=n<=255 912 n BINARY(n)
SQL TYPE is VARBINARY(n), 1<=n<=32 704 908 n VARBINARY(n)
SQL TYPE IS RESULT-SET-LOCATOR 972 4 Result set locator2
SQL TYPE IS TABLE LIKE table-name AS LOCATOR 976 4 Table locator2
SQL TYPE IS BLOB-LOCATOR 960 4 BLOB locator2
SQL TYPE IS CLOB-LOCATOR 964 4 CLOB locator2
SQL TYPE IS DBCLOB-LOCATOR 968 4 DBCLOB locator2
USAGE IS SQL TYPE IS BLOB(n) 1≤n≤2147483647 404 n BLOB(n)
USAGE IS SQL TYPE IS CLOB(n) 1≤n≤2147483647 408 n CLOB(n)
USAGE IS SQL TYPE IS DBCLOB(m) 1≤m≤10737418233 412 n DBCLOB(m)3
SQL TYPE IS XML AS BLOB(n) 404 0 XML
SQL TYPE IS XML AS CLOB(n) 408 0 XML
SQL TYPE IS XML AS DBCLOB(n) 412 0 XML
SQL TYPE IS BLOB-FILE 916/917 267 BLOB file reference2
SQL TYPE IS CLOB-FILE 920/921 267 CLOB file reference2
SQL TYPE IS DBCLOB-FILE 924/925 267 DBCLOB file reference2
SQL TYPE IS XML AS BLOB-FILE 916/917 267 XML BLOB file reference2
SQL TYPE IS XML AS CLOB-FILE 920/921 267 XML CLOB file reference2
SQL TYPE IS XML AS DBCLOB-FILE 924/925 267 XML DBCLOB file reference2
SQL TYPE IS ROWID 904 40 ROWID
Notes:
  1. If a host variable includes an indicator variable, the SQLTYPE value is the base SQLTYPE value plus 1.
  2. Do not use this data type as a column type.
  3. m is the number of double-byte characters.
The following table shows equivalent COBOL host variables for each SQL data type. Use this table to determine the COBOL data type for host variables that you define to receive output from the database. For example, if you retrieve TIMESTAMP data, you can define a fixed-length character string variable of length n
This table shows direct conversions between SQL data types and COBOL data types. However, a number of SQL data types are compatible. When you do assignments or comparisons of data that have compatible data types, DB2® converts those compatible data types.

COBOL host variable equivalents that you can use when retrieving data of a particular SQL data type
SQL data type
COBOL host variable equivalent
Notes
SMALLINT 
S9(4) COMP-4,
S9(4) COMP-5,
S9(4) COMP,
or S9(4) BINARY
INTEGER 
S9(9) COMP-4,
S9(9) COMP-5,
S9(9) COMP,
or S9(9) BINARY
DECIMAL(p,s) or NUMERIC(p,s) 
S9(p-s)V9(s) COMP-3 or
S9(p-s)V9(s)
 PACKED-DECIMAL
 DISPLAY SIGN
 LEADING SEPARATE
 NATIONAL SIGN
 LEADING SEPARATE
 p is precision; s is scale. 0<=s<=p<=31. If s=0, use S9(p)V or S9(p). If s=p, use SV9(s). If the COBOL compiler does not support 31–digit decimal numbers, no exact equivalent exists. Use COMP-2.
REAL or FLOAT (n) 
COMP-1
 1<=n<=21
DOUBLE PRECISION, DOUBLE or FLOAT (n) 
COMP-2
 22<=n<=53
BIGINT S9(18) COMP-4, S9(18) COMP-5, S9(18) COMP, or S9(18) BINARY
CHAR(n)
Fixed-length character string. For example, 
01 VAR-NAME PIC X(n).
 1<=n<=255
VARCHAR(n)
Varying-length character string. For example, 
01 VAR-NAME.
   49 VAR-LEN PIC S9(4)
 USAGE BINARY.
   49 VAR-TEXT PIC X(n).
 The inner variables must have a level of 49.
GRAPHIC(n)
Fixed-length graphic string. For example, 
01 VAR-NAME PIC G(n)
   USAGE IS DISPLAY-1.
 n refers to the number of double-byte characters, not to the number of bytes. 1<=n<=127
VARGRAPHIC(n)
Varying-length graphic string. For example, 
01 VAR-NAME.
   49 VAR-LEN PIC S9(4)
 USAGE BINARY.
   49 VAR-TEXT PIC G(n) 
      USAGE IS DISPLAY-1.
n refers to the number of double-byte characters, not to the number of bytes.
The inner variables must have a level of 49.
BINARY(n) SQL TYPE IS BINARY(n) 1<=n<=255
VARBINARY(n) SQL TYPE IS VARBINARY(n) 1<=n<=32 704
DATE
Fixed-length character string of length n. For example, 
01 VAR-NAME PIC X(n).
 If you are using a date exit routine, n is determined by that routine. Otherwise, n must be at least 10.
TIME
Fixed-length character string of length n. For example, 
01 VAR-NAME PIC X(n).
 If you are using a time exit routine, n is determined by that routine. Otherwise, n must be at least 6; to include seconds, n must be at least 8.
TIMESTAMP
Fixed-length character string of length n. For example, 
01 VAR-NAME PIC X(n).
 n must be at least 19. To include microseconds, n must be 26; if n is less than 26, truncation occurs on the microseconds part.
TIMESTAMP(0)
Fixed-length character string of length n. For example, 
01 VAR-NAME PIC X(n).
 n must be at least 19.
TIMESTAMP(p) p > 0
Fixed-length character string of length n. For example, 
01 VAR-NAME PIC X(n
).		 n must be at least 19. To include fractional seconds, n must be 20+x where x is the number of fractional seconds to include; if x is less than p, truncation occurs on the fractional seconds part.
TIMESTAMP(0) WITH TIME ZONE
Varying-length character string. For example, 
01 VAR-NAME.
49 VAR-LEN PIC S9(4) USAGE
BINARY. 49 VAR-TEXT PIC
X(n).
 The inner variables must have a level of 49. n must be at least 25.
TIMESTAMP(p) WITH TIME ZONE
Varying-length character string. For example, 
01 VAR-NAME.
49 VAR-LEN PIC S9(4) USAGE
BINARY. 49 VAR-TEXT PIC
X(n).
 The inner variables must have a level of 49. n must be at least 26+p.
Result set locator 
SQL TYPE IS
RESULT-SET-LOCATOR
 Use this data type only for receiving result sets. Do not use this data type as a column type.
Table locator 
SQL TYPE IS
 TABLE LIKE
 table-name
 AS LOCATOR
 Use this data type only in a user-defined function or stored procedure to receive rows of a transition table. Do not use this data type as a column type.
BLOB locator 
USAGE IS SQL TYPE IS
BLOB-LOCATOR
 Use this data type only to manipulate data in BLOB columns. Do not use this data type as a column type.
CLOB locator 
USAGE IS SQL TYPE IS
CLOB-LOCATOR
 Use this data type only to manipulate data in CLOB columns. Do not use this data type as a column type.
DBCLOB locator 
USAGE IS SQL TYPE IS
DBCLOB-LOCATOR
 Use this data type only to manipulate data in DBCLOB columns. Do not use this data type as a column type.
BLOB(n) 
USAGE IS SQL TYPE IS
BLOB(n)
 1≤n≤2147483647
CLOB(n) 
USAGE IS SQL TYPE IS
CLOB(n)
 1≤n≤2147483647
DBCLOB(n) 
USAGE IS SQL TYPE IS
DBCLOB(n)
 n is the number of double-byte characters. 1≤n≤1073741823
XML SQL TYPE IS XML AS BLOB(n) 1≤n≤2147483647
XML SQL TYPE IS XML AS CLOB(n) 1≤n≤2147483647
XML SQL TYPE IS XML AS DBCLOB(n) n is the number of double-byte characters. 1≤n≤1073741823
BLOB file reference 
USAGE IS SQL TYPE IS
BLOB-FILE
 Use this data type only to manipulate data in BLOB columns. Do not use this data type as a column type.
CLOB file reference 
USAGE IS SQL TYPE IS
CLOB-FILE
 Use this data type only to manipulate data in CLOB columns. Do not use this data type as a column type.
DBCLOB file reference 
USAGE IS SQL TYPE IS
DBCLOB-FILE
 Use this data type only to manipulate data in DBCLOB columns. Do not use this data type as a column type.
XML BLOB file reference SQL TYPE IS XML AS BLOB-FILE Use this data type only to manipulate XML data as BLOB files. Do not use this data type as a column type.
XML CLOB file reference SQL TYPE IS XML AS CLOB-FILE Use this data type only to manipulate XML data as CLOB files. Do not use this data type as a column type.
XML DBCLOB file reference SQL TYPE IS XML AS DBCLOB-FILE Use this data type only to manipulate XML data as DBCLOB files. Do not use this data type as a column type.
ROWID 
SQL TYPE IS ROWID



Publicado por

No hay comentarios:

Publicar un comentario

Suscribirse a: Enviar comentarios (Atom)