Using character data in SQL statements

You can code any of the following USAGE clauses to describe host variables for character data that you use in EXEC SQL statements: USAGE DISPLAY for single-byte or UTF-8 data, USAGE DISPLAY-1 for DBCS data, USAGE NATIONAL for UTF-16 data, Start of changeor USAGE UTF-8 for UTF-8 dataEnd of change.

When you use the stand-alone Db2® precompiler, you must specify the code page (CCSID) in EXEC SQL DECLARE statements for host variables that are declared with USAGE NATIONAL. You must specify the code page for host variables that are declared with USAGE DISPLAY or DISPLAY-1 only if the CCSID that is in effect for the COBOL CODEPAGE compiler option does not match the CCSIDs that are used by Db2 for character and graphic data.

Consider the following code. The two highlighted statements are unnecessary when you use the integrated Db2 coprocessor (with the SQLCCSID compiler option, as detailed in the related concept below), because the code-page information is handled implicitly.


CBL CODEPAGE(1140) NSYMBOL(NATIONAL)
. . .
WORKING-STORAGE SECTION.
    EXEC SQL INCLUDE SQLCA END-EXEC.
01  INT1 PIC S9(4) USAGE COMP.
01  C1140.
    49 C1140-LEN  PIC S9(4) USAGE COMP.
    49 C1140-TEXT PIC X(50).
    EXEC SQL DECLARE :C1140 VARIABLE CCSID 1140 END-EXEC.
01  G1200.
    49 G1200-LEN  PIC S9(4) USAGE COMP.
    49 G1200-TEXT PIC N(50) USAGE NATIONAL.
    EXEC SQL DECLARE :G1200 VARIABLE CCSID 1200 END-EXEC.
    . . .
    EXEC SQL FETCH C1 INTO :INT1, :C1140, :G1200 END-EXEC.

If you specify EXEC SQL DECLARE variable-name VARIABLE CCSID nnnn END-EXEC, that specification overrides the implied CCSID. For example, the following code would cause Db2 to treat C1208-TEXT as encoded in UTF-8 (CCSID 1208) rather than as encoded in the CCSID in effect for the COBOL CODEPAGE compiler option:


01 C1208.
    49 C1208-LEN  PIC S9(4) USAGE COMP.
    49 C1208-TEXT PIC X(50).
    EXEC SQL DECLARE :C1208 VARIABLE CCSID 1208 END-EXEC.

The NSYMBOL compiler option has no effect on a character literal inside an EXEC SQL statement. Character literals in an EXEC SQL statement follow the SQL rules for character constants.

Related concepts  
COBOL and Db2 CCSID determination

Related tasks  
Using the separate Db2 precompiler
DB2® Application Programming and SQL Guide (Coding SQL statements in a
   COBOL application)

Related references  
Differences in how the Db2 precompiler and coprocessor behave  
CODEPAGE  
DB2 SQL Reference