DECLARE VARIABLE

Invocation

This statement can only be embedded in an application program. It is not an executable statement.

Authorization

None required.

Description

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

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

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.

Use the DECLARE VARIABLE statement to specify a data subtype or CCSID for these host variables: FRED as CCSID EBCDIC, JEAN as CCSID 1208 or CCSID UNICODE, DAVE as CCSID 1208 or CCSID UNICODE, PETE as CCSID 1200 or CCSID UNICODE, and AMBER as CCSID 1200 or CCSID UNICODE.

        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;