Data types in stored procedures

A stored procedure that is written in any language except REXX must declare each parameter that is passed to it. The definition for that stored procedure must also contain a compatible SQL data type declaration for each parameter.

For languages other than REXX

For all data types except LOBs, ROWIDs, locators, and VARCHARs (for C language), see the tables listed in the following table for the host data types that are compatible with the data types in the stored procedure definition. You cannot have XML parameters in an external procedure.

For LOBs, ROWIDs, VARCHARs, and locators, the following table shows compatible declarations for the assembler language.

Table 1. Compatible assembler language declarations for LOBs, ROWIDs, and locators
SQL data type in definition	Assembler declaration
TABLE LOCATOR BLOB LOCATOR CLOB LOCATOR DBCLOB LOCATOR	`DS FL4`
BLOB(n)	`If n <= 65535: var DS 0FL4 var_length DS FL4 var_data DS CLn If n > 65535: var DS 0FL4 var_length DS FL4 var_data DS CL65535 ORG var_data+(n-65535)`
CLOB(n)	`If n <= 65535: var DS 0FL4 var_length DS FL4 var_data DS CLn If n > 65535: var DS 0FL4 var_length DS FL4 var_data DS CL65535 ORG var_data+(n-65535)`
DBCLOB(n)	`If m (=2*n) <= 65534: var DS 0FL4 var_length DS FL4 var_data DS CLm If m > 65534: var DS 0FL4 var_length DS FL4 var_data DS CL65534 ORG var_data+(m-65534)`
ROWID	`DS HL2,CL40`
VARCHAR(n)	If PARAMETER VARCHAR NULTERM is specified or implied: `char data[n+1];` If PARAMETER VARCHAR STRUCTURE is specified: `struct {short len; char data[n]; } var;`
Note: This row does not apply to VARCHAR(n) FOR BIT DATA. BIT DATA is always passed in a structured representation.

For LOBs, ROWIDs, and locators, the following table shows compatible declarations for the C language.

Table 2. Compatible C language declarations for LOBs, ROWIDs, and locators
SQL data type in definition	C declaration
TABLE LOCATOR BLOB LOCATOR CLOB LOCATOR DBCLOB LOCATOR	`unsigned long`
BLOB(n)	`struct {unsigned long length; char data[n]; } var;`
CLOB(n)	`struct {unsigned long length; char var_data[n]; } var;`
DBCLOB(n)	`struct {unsigned long length; sqldbchar data[n]; } var;`
ROWID	`struct {short int length; char data[40]; } var;`

For LOBs, ROWIDs, and locators, the following table shows compatible declarations for COBOL.

Table 3. Compatible COBOL declarations for LOBs, ROWIDs, and locators
SQL data type in definition	COBOL declaration
TABLE LOCATOR BLOB LOCATOR CLOB LOCATOR DBCLOB LOCATOR	`01 var PIC S9(9) COMP-5.`
BLOB(n)	`01 var. 49 var-LENGTH PIC S9(9) COMP-5. 49 var-DATA PIC X(n).`
CLOB(n)	`01 var. 49 var-LENGTH PIC S9(9) COMP-5. 49 var-DATA PIC X(n).`
DBCLOB(n)	`01 var. 49 var-LENGTH PIC S9(9) COMP-5. 49 var-DATA PIC G(n) DISPLAY-1.`
ROWID	`01 var. 49 var-LEN PIC S9(4) COMP-5. 49 var-DATA PIC X(40).`

For LOBs, ROWIDs, and locators, the following table shows compatible declarations for PL/I.

Table 4. Compatible PL/I declarations for LOBs, ROWIDs, and locators
SQL data type in definition	PL/I
TABLE LOCATOR BLOB LOCATOR CLOB LOCATOR DBCLOB LOCATOR	`BIN FIXED(31)`
BLOB(n)	If n <= 32767: `01 var, 03 var_LENGTH BIN FIXED(31), 03 var_DATA CHAR(n);` If n > 32767: `01 var, 02 var_LENGTH BIN FIXED(31), 02 var_DATA, 03 var_DATA1(n) CHAR(32767), 03 var_DATA2 CHAR(mod(n,32767));`
CLOB(n)	If n <= 32767: `01 var, 03 var_LENGTH BIN FIXED(31), 03 var_DATA CHAR(n);` If n > 32767: `01 var, 02 var_LENGTH BIN FIXED(31), 02 var_DATA, 03 var_DATA1(n) CHAR(32767), 03 var_DATA2 CHAR(mod(n,32767));`
DBCLOB(n)	If n <= 16383: `01 var, 03 var_LENGTH BIN FIXED(31), 03 var_DATA GRAPHIC(n);` If n > 16383: `01 var, 02 var_LENGTH BIN FIXED(31), 02 var_DATA, 03 var_DATA1(n) GRAPHIC(16383), 03 var_DATA2 GRAPHIC(mod(n,16383));`
ROWID	`CHAR(40) VAR`

Tables of results: Each high-level language definition for stored procedure parameters supports only a single instance (a scalar value) of the parameter. There is no support for structure, array, or vector parameters. Because of this, the SQL statement CALL limits the ability of an application to return some kinds of tables. For example, an application might need to return a table that represents multiple occurrences of one or more of the parameters passed to the stored procedure. Because the SQL statement CALL cannot return more than one set of parameters, use one of the following techniques to return such a table:

Put the data that the application returns in a Db2 table. The calling program can receive the data in one of these ways:
- The calling program can fetch the rows from the table directly. Specify FOR FETCH ONLY or FOR READ ONLY on the SELECT statement that retrieves data from the table. A block fetch can retrieve the required data efficiently.
- The stored procedure can return the contents of the table as a result set. See Writing an external procedure to return result sets to a distributed client and Writing a program to receive the result sets from a stored procedure for more information.
Convert tabular data to string format and return it as a character string parameter to the calling program. The calling program and the stored procedure can establish a convention for interpreting the content of the character string. For example, the SQL statement CALL can pass a 1920-byte character string parameter to a stored procedure, which enables the stored procedure to return a 24x80 screen image to the calling program.