DECLARE VARIABLE statement
The DECLARE VARIABLE statement defines a CCSID for a host variable and the subtype of the variable. When it appears in an application program, the DECLARE VARIABLE statement causes the Db2 precompiler to tag a host variable with a specific CCSID. When the host variable appears in an SQL statement, the Db2 precompiler places this CCSID into the structures that it generates for the SQL statement.
Invocation for DECLARE VARIABLE
This statement can only be embedded in an application program. It is not an executable statement.
Authorization for DECLARE VARIABLE
None required.
Syntax for DECLARE VARIABLE
Description for DECLARE VARIABLE
- host-variable
- Identifies a character or graphic string host variable defined in the program. An indicator variable cannot be specified for the host-variable.
- CCSID ASCII, EBCDIC, or UNICODE
- Specifies that the appropriate
default CCSID for the specified encoding scheme of the server should
be used.
- CCSID ASCII
- Specifies that the default ASCII CCSID for the type of the variable at the server should be used.
- CCSID EBCDIC
- Specifies that the default EBCDIC CCSID for the type of the variable at the server should be used. CCSID EBCDIC is the default if this option is not specified.
- CCSID UNICODE
- Specifies that the default UNICODE CCSID for the type of the variable at the server should be used.
- FOR SBCS DATA, FOR MIXED DATA, or FOR BIT DATA
- Specifies the type of data contained in the variable host-variable.
The FOR clause cannot be specified when declaring a graphic host variable.
For ASCII or EBCDIC data, if this clause is not specified when declaring a character host variable, the default is FOR SBCS DATA if MIXED DATA = NO on the installation panel DSNTIPF. The default is FOR MIXED DATA if MIXED DATA = YES on the installation panel DSNTIPF.
For UNICODE data, the default is always FOR MIXED DATA, regardless of the setting of MIXED DATA on the installation panel DSNTIPF.
- FOR SBCS DATA
- Specifies that the values of the host variable can contain only SBCS (single-byte character set) data.
- FOR MIXED DATA
- Specifies that the values of the host variable can contain both SBCS data and DBCS data.
- FOR BIT DATA
- Specifies that the values of the host-variable are not associated with a coded character set and, therefore, are never converted. The CCSID of a FOR BIT DATA host variable is 65535.
- CCSID integer-constant
- Specifies that the values of the host variable contain data that is encoded using CCSID integer-constant. If the integer is an SBCS CCSID, the host variable is SBCS data. If the integer is a mixed data CCSID, the host variable is mixed data. For character host variables, the CCSID specified must be an SBCS, mixed CCSID, or UNICODE (UTF-8) CCSID. For graphic host variables, the CCSID specified must be a DBCS or UNICODE (UTF-16) CCSID. The valid range of values for the integer is 1–65533.
Notes for DECLARE VARIABLE
Placement of statement: The DECLARE VARIABLE statement can be specified anywhere in an application program that SQL statements are valid with the following exception. The DECLARE VARIABLE statement must occur before an SQL statement that refers to a host variable specified in the DECLARE VARIABLE statement.
CCSID exceptions for EXECUTE IMMEDIATE or PREPARE: When the host variable appears in an SQL statement, the Db2 precompiler places the appropriate numeric CCSID into the structures it generates for the SQL statement. This placement of the CCSID occurs for any SQL statement other than the EXECUTE IMMEDIATE or PREPARE statements. The placement of the CCSID also occurs for a host-variable in an EXECUTE IMMEDIATE or PREPARE statement, but it does not occur for a variable in a string-expression in an EXECUTE IMMEDIATE or PREPARE statement.
If a PL/1 application program contains at least one DECLARE VARIABLE statement, a string-expression in any EXECUTE IMMEDIATE or PREPARE statement cannot be preceded by a colon. An expression that consists of just a variable name preceded by a colon is interpreted as a host-variable.
Specific host languages: If a DECLARE VARIABLE statement is used in an assembler source program, the ONEPASS SQL processing option must not be used. If a DECLARE VARIABLE statement is used in a C, C++, or PL/I source program, the TWOPASS SQL processing option must be used. For those languages, or COBOL, the host-variable definition can either precede or follow a DECLARE VARIABLE statement that refers to that variable. If a DECLARE VARIABLE statement is used in a FORTRAN source program, then the host-variable definition must precede the DECLARE VARIABLE statement.
Example for DECLARE VARIABLE
Example: Define the following host variables using PL/I data types: FRED as fixed length bit data, JEAN as fixed length UTF-8 (mixed) data, DAVE as varying length UTF-8 (mixed) data, PETE as fixed length graphic UTF-16 data, and AMBER as varying length graphic UTF-16 data.
EXEC SQL BEGIN DECLARE SECTION;
DCL FRED CHAR(10);
EXEC SQL DECLARE :FRED VARIABLE CCSID EBCDIC FOR BIT DATA;
DCL JEAN CHAR(30);
EXEC SQL DECLARE :JEAN VARIABLE CCSID 1208;
DCL DAVE CHAR(9) VARYING;
EXEC SQL DECLARE :DAVE VARIABLE CCSID UNICODE;
DCL PETE GRAPHIC(10);
EXEC SQL DECLARE :PETE VARIABLE CCSID 1200;
DCL AMBER GRAPHIC(20) VARYING;
EXEC SQL DECLARE :AMBER VARIABLE CCSID UNICODE;
EXEC SQL END DECLARE SECTION;