Determining equivalent SQL and ILE RPG data types

Edit online

The precompiler determines the base SQLTYPE and SQLLEN of host variables according to this table. If a host variable appears with an indicator variable, the SQLTYPE is the base SQLTYPE plus one.

Table 1. ILE RPG declarations mapped to typical SQL data types
RPG data type RPG coding SQLTYPE of host variable SQLLEN of host variable SQL data type
Data structure (without subfields) Free-form:
  • DCL-DS name LEN(n) END-DS;
Fixed-form:
  • Length = n where n ≤ 32766.
 452 n CHAR(n)
Zoned data Free-form:
  • ZONED(p:s)
Fixed-form:
  • Defined on Definition specification as subfield with data type S or blank.
  • Defined on Definition specification with data type S.
  • Defined on Input specification with data type S or blank.
 488 p in byte 1, s in byte 2 NUMERIC(p, s) where p is the number of digits and s is the number of decimal places
Packed data Free-form:
  • PACKED(p:s)
Fixed-form:
  • Defined on Definition specification with decimal positions (pos 69-70) not blank.
  • Defined on Definition specification subfield with data type P.
  • Defined on Definition specification with data type P or blank.
  • Defined on Input specification with data type P.
 484 p in byte 1, s in byte 2 DECIMAL(p, s) where p is the number of digits and s is the number of decimal places
2-byte binary with zero decimal positions Free-form:
  • BINDEC(digits)
where 1 <= digits <= 4

Fixed-form:

  • Defined on Definition specification as subfield with from and to positions and data type B and byte length 2.
  • Defined on Definition specification with data type B and digits from 1 to 4.
  • Defined on Input specification with data type B and byte length 2
 500 2 SMALLINT
4-byte binary with zero decimal positions Free-form:
  • BINDEC(digits)
where 5 <= digits <= 9

Fixed-form:

  • Defined on Definition specification as subfield with from and to positions and data type B and byte length 4.
  • Defined on Definition specification with data type B and digits from 5 to 9.
  • Defined on Input specification with data type B and byte length 4.
 496 4 INTEGER
2-byte integer Free-form:
  • INT(5)
Fixed-form:
  • Defined on Definition specification as subfield with from and to positions and data type I and byte length 2.
  • Defined on Definition specification with data type I and digits 5.
  • Defined on Input specification with data type I and byte length 2.
 500 2 SMALLINT
4-byte integer Free-form:
  • INT(10)
Fixed-form:
  • Defined on Definition specification as subfield with from and to positions and data type I and byte length 4.
  • Defined on Definition specification with data type I and digits 10.
  • Defined on Input specification with data type I and byte length 4.
 496 4 INTEGER
8-byte integer Free-form:
  • INT(20)
Fixed-form:
  • Defined on Definition specification as subfield with from and to positions and data type I and byte length 8.
  • Defined on Definition specification with data type I and digits 20.
  • Defined on Input specification with data type I and byte length 8.
 492 8 BIGINT
short float Free-form:
  • FLOAT(4)
Fixed-form:
  • Data type = F, length = 4.
 480 4 FLOAT (single precision)
long float Free-form:
  • FLOAT(8)
Fixed-form:
  • Data type = F, length = 8.
 480 8 FLOAT (double precision)
Character Free-form:
  • CHAR(n)
Fixed-form:
  • Data type = A or blank, decimal positions blank, length between 1 and 32766.
 452 n CHAR (n) where n is the length
Character varying length greater than 254 Free-form:
  • VARCHAR(n)
Fixed-form:
  • Data type = A or blank, decimal positions blank, VARYING keyword on Definition specification or format *VAR on Input specification.
 448 n VARCHAR (n) where n is the length
Character varying length between 1 and 254 Free-form:
  • VARCHAR(n)
Fixed-form:
  • Data type = A or blank, decimal positions blank, VARYING keyword on Definition specification or format *VAR on Input specification.
 456 n VARCHAR (n) where n is the length
graphic Free-form:
  • GRAPH(n)
Fixed-form:
  • Defined on Definition specification as subfield with from and to positions and data type G and byte-length b.
  • Defined on Definition specification with data type G and length n.
  • Defined on Input specification with data type G and byte-length b
 468 m GRAPHIC(m) where m = n or m = b/2
varying graphic Free-form:
  • VARGRAPH(n)
Fixed-form:
  • Defined on Definition specification as subfield with from and to positions and data type G and byte-length b and VARYING keyword.
  • Defined on Definition specification with data type G and length n and VARYING keyword.
  • Defined on Input specification with data type G and byte-length b and format *VAR.
 464 m VARGRAPHIC(m) where m = n or m = (b-2)/2
UCS-2 Free-form:
  • UCS2(n)
Fixed-form:
  • Defined on Definition specification as subfield with from and to positions and data type C and byte-length b.
  • Defined on Definition specification with data type C and length n.
  • Defined on Input specification with data type C and byte-length b.
 468 m GRAPHIC(m) with CCSID 13488 or CCSID 1200 where m = n or m = b/2
varying UCS-2 Free-form:
  • VARUCS2(n)
Fixed-form:
  • Defined on Definition specification as subfield with from and to positions and data type C and byte-length b and VARYING keyword.
  • Defined on Definition specification with data type C and length n and VARYING keyword.
  • Defined on Input specification with data type C and byte-length b and format *VAR.
 464 m VARGRAPHIC(m) with CCSID 13488 or CCSID 1200 where m = n or m = b/2
Date Free-form:
  • DATE
  • DATE(fs)
Fixed-form:
  • Defined on Definition specification with data type D, format f and separator s from DATFMT keyword.
  • Defined on Input specification with data type D and format in pos 31-34, separator in pos 35.
 384   DATE DATFMT(f) DATSEP(s)1
Time Free-form:
  • TIME
  • TIME(fs)
Fixed-form:
  • Defined on Definition specification with data type T, format f and separator s from TIMFMT keyword.
  • Defined on Input specification with data type T and format in pos 31-34, separator in pos 35.
 388   TIME TIMFMT(f) TIMSEP(s)1
Timestamp Free-form:
  • TIMESTAMP(n)
Fixed-form:
  • Data type Z.
 392 19 when n = 0, otherwise 20+n TIMESTAMP(n)
Start of changeIndicatorEnd of change Start of changeFree-form:
  • IND
Fixed-form:
  • Defined on Definition specification as subfield with data type N.
  • Defined on Definition specification with data type N.
  • Defined on Input specification with data type N.
End of change		 Start of change2436End of change Start of change1End of change Start of changeBOOLEANEnd of change
1SQL creates the date/time subfield using the DATE/TIME format specified on the CRTSQLRPGI command. The conversion to the host variable DATE/TIME format occurs when the mapping is done between the host variables and the SQL-generated subfields.

The following table can be used to determine the RPG data type that is equivalent to a given SQL data type.

Table 2. SQL data types mapped to typical RPG declarations
SQL data type RPG data type Notes
SMALLINT Free-form:
  • INT(5)
  • BINDEC(n) where 1 <= n <= 4
Fixed-form:
  • Definition specification. I in position 40, length must be 5 and 0 in position 42.
  • Definition specification. B in position 40, length must be ≤ 4 and 0 in position 42.
  
INTEGER Free-form:
  • INT(10)
  • BINDEC(n) where 5 <= n <= 9
Fixed-form:
  • Definition specification. I in position 40, length must be 10 and 0 in position 42.
  • Definition specification. B in position 40, length must be ≤ 9 and ≥ 5 and 0 in position 42.
  
BIGINT Free-form:
  • INT(20)
Fixed-form:
  • Definition specification. I in position 40, length must be 20 and 0 in position 42.
  
DECIMAL Free-form:
  • PACKED(p:s)
Fixed-form:
  • Definition specification. P in position 40 or blank in position 40 for a non-subfield, 0 through 63 in position 41,42.
  • Defined as numeric on non-definition specification.
 Maximum length of 32 (precision 63) and maximum scale of 63.
NUMERIC Free-form:
  • ZONED(p:s)
Fixed-form:
  • Definition specification. S in position 40 or blank in position 40 for a subfield, 0 through 63 in position 41,42.
 Maximum length of 63 (precision 63) and maximum scale of 63.
DECFLOAT Not supported Not supported
FLOAT (single precision) Free-form:
  • FLOAT(4)
Fixed-form:
  • Definition specification. F in position 40, length must be 4.
  
FLOAT (double precision) Free-form:
  • FLOAT(8)
Fixed-form:
  • Definition specification. F in position 40, length must be 8.
  
CHAR(n) Free-form:
  • CHAR(n)
Fixed-form:
  • Definition specification. A or blank in positions 40 and blanks in position 41,42.
  • Input field defined without decimal places.
  • Calculation result field defined without decimal places.
 n can be from 1 to 32766.
CHAR(n) Free-form:
  • DCL-DS name LEN(n) END-DS;
Fixed-form:
  • Data structure name with no subfields in the data structure.
 n can be from 1 to 32766.
VARCHAR(n) Free-form:
  • VARCHAR(n)
Fixed-form:
  • Definition specification. A or blank in position 40 and VARYING in positions 44-80.
 n can be from 1 to 32740.
CLOB Not supported Use SQLTYPE keyword to declare a CLOB.
GRAPHIC(n) Free-form:
  • GRAPH(n)
Fixed-form:
  • Definition specification. G in position 40.
  • Input field defined with G in position 36.
 n can be 1 to 16383.
VARGRAPHIC(n) Free-form:
  • VARGRAPH(n)
Fixed-form:
  • Definition specification. G in position 40 and VARYING in positions 44-80.
 n can be from 1 to 16370.
DBCLOB Not supported Use SQLTYPE keyword to declare a DBCLOB.
BINARY Not supported Use SQLTYPE keyword to declare a BINARY.
VARBINARY Not supported Use SQLTYPE keyword to declare a VARBINARY.
BLOB Not supported Use SQLTYPE keyword to declare a BLOB.
DATE Free-form:
  • DATE
Fixed-form:
  • A character field
  • Definition specification with a D in position 40.
  • Input field defined with D in position 36.
 If the format is *USA, *JIS, *EUR, or *ISO, the length must be at least 10. If the format is *YMD, *DMY, or *MDY, the length must be at least 8. If the format is *JUL, the length must be at least 6.
TIME Free-form:
  • TIME
Fixed-form:
  • A character field
  • Definition specification with a T in position 40.
  • Input field defined with T in position 36.
 Length must be at least 6; to include seconds, length must be at least 8.
TIMESTAMP(n) Free-form:
  • TIMESTAMP(n)
Fixed-form:
  • A character field
  • Definition specification with a Z in position 40.
  • Input field defined with Z in position 36.

For a character field, the length must be at least 19; to include fractional seconds, the length can be 21 to 32. If less than 32, some fractional seconds will be truncated.
Start of changeBOOLEANEnd of change Start of changeFree-form:
  • IND
Fixed-form:
  • Definition specification with a N in position 40.
  • Input field defined with N in position 36.
End of change		 Start of change End of change
XML Not supported Use SQLTYPE keyword to declare an XML.
DATALINK Not supported  
ROWID Not supported Use SQLTYPE keyword to declare a ROWID.
Result set locator Not supported Use SQLTYPE keyword to declare a result set locator.