Parameter passing conventions for stored procedures and user-defined functions
The CALL statement and a function call can pass arguments to programs written in all supported host languages and REXX procedures.
Each language supports different data types that are tailored to it, as shown in the following tables. The SQL data type is contained in the leftmost column of each table. Other columns in that row contain an indication of whether that data type is supported as a parameter type for a particular language. If the column is blank, the data type is not supported as a parameter type for that language. A host variable declaration indicates that Db2 for i supports this data type as a parameter in this language. The declaration indicates how host variables must be declared to be received and set properly by the procedure or function. When an SQL procedure or function is called, all SQL data types are supported so no column is provided in the table.
| SQL data type | C and C++ | CL | COBOL and ILE COBOL |
|---|---|---|---|
| SMALLINT | short | PIC S9(4) BINARY | |
| INTEGER | long | PIC S9(9) BINARY | |
| BIGINT | long long | PIC S9(18) BINARY Note: Only supported for ILE COBOL.
|
|
| DECIMAL(p,s) | decimal(p,s) | TYPE(*DEC) LEN(p s) | PIC S9(p-s)V9(s) PACKED-DECIMAL Note: Precision
must not be greater than 18.
|
| NUMERIC(p,s) | PIC S9(p-s)V9(s) DISPLAY SIGN LEADING
SEPARATE Note: Precision must not be greater than 18.
|
||
| DECFLOAT | _Decimal32, _Decimal64, _Decimal128 Note: Only
supported for C.
|
||
| REAL or FLOAT(p) | float | COMP-1 Note: Only supported for
ILE COBOL.
|
|
| DOUBLE PRECISION or FLOAT or FLOAT(p) | double | COMP-2 Note: Only
supported for ILE COBOL.
|
|
| CHARACTER(n) | char ... [n+1] | TYPE(*CHAR) LEN(n) | PIC X(n) |
| VARCHAR(n) | char ... [n+1] | Varying-Length Character String | |
| VARCHAR(n) FOR BIT DATA | VARCHAR structured form | Varying-Length Character String | |
| CLOB | CLOB structured form | CLOB structured form Note: Only
supported for ILE COBOL.
|
|
| GRAPHIC(n) | wchar_t ... [n+1] | PIC G(n) DISPLAY-1 or PIC N(n) Note: Only supported for ILE COBOL.
|
|
| VARGRAPHIC(n) | VARGRAPHIC structured form | Varying-Length Graphic String Note: Only supported for ILE COBOL.
|
|
| DBCLOB | DBCLOB structured form | DBCLOB structured form Note: Only
supported for ILE COBOL.
|
|
| BINARY | BINARY structured form | BINARY structured form | |
| VARBINARY | VARBINARY structured form | VARBINARY structured form | |
| BLOB | BLOB structured form | BLOB structured form Note: Only
supported for ILE COBOL.
|
|
| DATE | char ... [11] | TYPE(*CHAR) LEN(10) | PIC X(10) Note: For
ILE COBOL only, FORMAT DATE.
|
| XML | XML structured form | XML structured form | |
| TIME | char ... [9] | TYPE(*CHAR) LEN(8) | PIC X(8) Note: For
ILE COBOL only, FORMAT TIME.
|
| TIMESTAMP(n) | char ... [20]
when n = 0 char ... [n+21] when n > 0 |
TYPE(*CHAR) LEN(19)
when n = 0 TYPE(*CHAR) LEN(n+20) when n > 0 |
PIC X(19) when n = 0
PIC X(n+20) when n > 0 Note: For ILE COBOL
only, FORMAT TIMESTAMP.
|
| ROWID | ROWID structured form | ROWID structured form | |
| DataLink | |||
| Array | |||
| Indicator variable | short | PIC S9(4) BINARY |
| SQL data type | Java™ parameter style JAVA | Java parameter style DB2GENERAL | PL/I |
|---|---|---|---|
| SMALLINT | short | short | FIXED BIN(15) |
| INTEGER | int | int | FIXED BIN(31) |
| BIGINT | long | long | |
| DECIMAL(p,s) | BigDecimal | BigDecimal | FIXED DEC(p,s) |
| NUMERIC(p,s) | BigDecimal | BigDecimal | |
| DECFLOAT | BigDecimal | BigDecimal | |
| REAL or FLOAT(p) | float | float | FLOAT BIN(p) |
| DOUBLE PRECISION or FLOAT or FLOAT(p) | double | double | FLOAT BIN(p) |
| CHARACTER(n) | String | String | CHAR(n) |
| VARCHAR(n) | String | String | CHAR(n) VAR |
| VARCHAR(n) FOR BIT DATA | byte[ ] | com.ibm.db2.app.Blob | CHAR(n) VAR |
| CLOB | java.sql.Clob | com.ibm.db2.app.Clob | CLOB structured form |
| GRAPHIC(n) | String | String | |
| VARGRAPHIC(n) | String | String | |
| DBCLOB | java.sql.Clob | com.ibm.db2.app.Clob | DBCLOB structured form |
| BINARY | byte[ ] | com.ibm.db2.app.Blob | BINARY structured form |
| VARBINARY | byte[ ] | com.ibm.db2.app.Blob | VARBINARY structured form |
| BLOB | java.sql.Blob | com.ibm.db2.app.Blob | BLOB structured form |
| XML AS CLOB | java.sql.CLOB | ||
| XML AS BLOB | java.sql.BLOB | ||
| DATE | Date | String | CHAR(10) |
| TIME | Time | String | CHAR(8) |
| TIMESTAMP(n) | Timestamp | String | CHAR(19) when n = 0
CHAR(n+20) when n > 0 |
| ROWID | byte[] | com.ibm.db2.app.Blob | ROWID structured form |
| DataLink | |||
| Array | java.sql.Array | ||
| Indicator variable | FIXED BIN(15) |
| SQL data type | REXX | RPG | ILE RPG |
|---|---|---|---|
| SMALLINT | Data structure that contains a single sub-field. B in position 43, length must be 2, and 0 in position 52 of the sub-field specification. | Data specification. B in
position 40, length must be <= 4, and 00 in positions 41-42 of
the sub-field specification. or
Data specification. I in
position 40, length must be 5, and 00 in positions 41-42 of the sub-field
specification. |
|
| INTEGER | Numeric string with no decimal (and an optional leading sign) | Data structure that contains a single sub-field. B in position 43, length must be 4, and 0 in position 52 of the sub-field specification. | Data specification. B in
position 40, length must be <=09 and >=05, and 00 in positions
41-42 of the sub-field specification. or
Data specification. I in
position 40, length must be 10, and 00 in positions 41-42 of the sub-field
specification. |
| BIGINT | Data specification. I in position 40, length must be 20, and 00 in positions 41-42 of the sub-field specification. | ||
| DECIMAL(p,s) | Numeric string with a decimal (and an optional leading sign) | Data structure that contains a single sub-field. P in position 43 and 0 through 9 in position 52 of the sub-field specification. or A numeric input field or calculation result field. | Data specification. P in position 40 and 00 through 31 in positions 41-42 of the sub-field specification. |
| NUMERIC(p,s) | Data structure that contains a single sub-field. Blank in position 43 and 0 through 9 in position 52 of the sub-field specification. | Data specification. S in position 40, or Blank in position 40 and 00 through 31 in position 41-42 of the sub-field specification. | |
| DECFLOAT | |||
| REAL or FLOAT(p) | String with digits, then an E, (then an optional sign), then digits | Data specification. F in position 40, length must be 4. | |
| DOUBLE PRECISION or FLOAT or FLOAT(p) | String with digits, then an E, (then an optional sign), then digits | Data specification. F in position 40, length must be 8. | |
| CHARACTER(n) | String with n characters within two apostrophes | Data structure field without sub-fields or data structure that contains a single sub-field. Blank in position 43 and 52 of the sub-field specification. or A character input field or calculation result field. | Data specification. A in position 40, or Blank in position 40 and 41-42 of the sub-field specification. |
| VARCHAR(n) | String with n characters within two apostrophes | Data specification. A in position 40, or Blank in position 40 and 41-42 of the sub-field specification and the keyword VARYING in positions 44-80. | |
| VARCHAR(n) FOR BIT DATA | String with n characters within two apostrophes | Data specification. A in position 40, or Blank in position 40 and 41-42 of the sub-field specification and the keyword VARYING in positions 44-80. | |
| CLOB | CLOB structured form | ||
| GRAPHIC(n) | String starting with G', then n double-byte characters, then ' | Data specification. G in position 40 of the sub-field specification. | |
| VARGRAPHIC(n) | String starting with G', then n double-byte characters, then ' | Data specification. G in position 40 of the sub-field specification and the keyword VARYING in positions 44-80. | |
| DBCLOB | DBCLOB structured form | ||
| BINARY | BINARY structured form | ||
| VARBINARY | VARBINARY structured form | ||
| BLOB | BLOB structured form | ||
| XML | XML structured form | ||
| DATE | String with 10 characters within two apostrophes | Data structure field without sub-fields or data structure that contains a single sub-field. Blank in position 43 and 52 of the sub-field specification. Length is 10. or A character input field or calculation result field. | Data specification. D in position 40 of the sub-field specification. DATFMT(*ISO) in position 44-80. |
| TIME | String with 8 characters within two apostrophes | Data structure field without sub-fields or data structure that contains a single sub-field. Blank in position 43 and 52 of the sub-field specification. Length is 8. or A character input field or calculation result field. | Data specification. T in position 40 of the sub-field specification. TIMFMT(*ISO) in position 44-80. |
| TIMESTAMP(n) | String with 26 characters within two apostrophes | Data structure field without sub-fields or data structure that contains a single sub-field. Blank in position 43 and 52 of the sub-field specification. Length is 19 for n = 0. Length is n+20 for n > 0. | Timestamp data type with length 19 for n = 0, length n+20 for n > 0. |
| ROWID | ROWID structured form | ||
| DataLink | |||
| Array | |||
| Indicator variable | Numeric string with no decimal (and an optional leading sign). | Data structure that contains a single sub-field. B in position 43, length must be 2, and 0 in position 52 of the sub-field specification. | Data specification. B in position 40, length must be <=4, and 00 in positions 41-42 of the sub-field specification. |