Details of CCSID options for application programs
You have several options in DB2® to set CCSIDs for your applications.
- CCSID SQL processing option
- If
you are using the DB2 precompiler,
use this option to specify the CCSID in which the source program is
written. This value ensures that the DB2 precompiler
correctly parses the SQL statements and literal string values in those
statements at precompile time. The default value is the subsystem
EBCDIC CCSID (DECP value SCCSID if MIXED=NO or MCCSID if MIXED=YES).
DB2 converts the source code from the specified CCSID to Unicode UTF-8 before it is processed by the precompiler. The precompiler then parses the source code in Unicode UTF-8.
The value that you specify for the precompiler must match the value that you specify to the compiler when you compile the program.
If you are using the DB2 coprocessor, do not specify this option. Instead, use the language compiler options. The coprocessor uses the CCSID that is passed to it from the language compiler to convert the SQL statement text. If the compiler does not pass a CCSID, the DB2 coprocessor uses the CCSID suboption of the compiler SQL option. If that suboption is not specified, the DB2 coprocessor uses the subsystem EBCDIC CCSID (DECP value SCCSID or MCCSID) as the CCSID for the source.
- ENCODING bind option
- Use
this option when you bind your application to specify the CCSID of
the data in your application. This value applies to both host variables
in static SQL statements and parameter markers in dynamic SQL statements
unless this value is overridden. For example, you can override the
CCSID of host variables by using certain language compiler options
or by specifying a DECLARE VARIABLE statement with the CCSID option.
You can override the CCSID of parameter markers in dynamic SQL statements
by using the CURRENT APPLICATION ENCODING SCHEME special register.
This value can be EBCDIC, ASCII, Unicode, or a valid CCSID. If the value is EBCDIC, ASCII, or Unicode, DB2 uses the subsystem default CCSID for that encoding scheme.
The default value is the subsystem default application encoding scheme (the DECP value APPENSCH), which, by default, is EBCDIC. The DB2 sample applications are bound with ENCODING EBCDIC.
For example, some possible uses of the ENCODING bind option are as follows:
- You have a C/C++ program that accesses an ASCII library on z/OS. In this case, bind the program with ENCODING ASCII.
- You use QMF™ and have a data center in Germany and 3270 emulators in France. You might want to bind a special version of QMF for French by specifying ENCODING 1147.
In general, any time the CCSID of your source data does not match the subsystem default CCSID, use the ENCODING option to tell DB2 the correct CCSID. The source data can come from a terminal emulator, an MQ queue, or elsewhere.
If you use the DB2 coprocessor on a COBOL application that contains PIC X variables and specify the NOSQLCCSID compiler option, do not specify ENCODING UNICODE. If you specify this option, DB2 interprets these character variables as UTF-8, but COBOL does not support UTF-8.
In a DRDA environment, the CCSIDs are communicated as part of the protocol. DB2 does not use the ENCODING bind option to determine the CCSID of data from a remote application or to encode data to send to a remote application. However, the ENCODING bind option can influence internal DB2 processing. DB2 uses the value of this bind option when it processes SET statements or any statement that contains multiple CCSIDs. For example, DB2 uses the ENCODING option that was specified when the package was bound to evaluate the following statement:SET :hv1 = SUBSTR(:hv_locator, 1, 100);
- CURRENT APPLICATION ENCODING SCHEME special register
- Use
this special register to specify the CCSID for data that is passed
through parameter markers in dynamic SQL statements. This value does
not apply to static SQL statements.
You can set the value of this special register by using the SET CURRENT APPLICATION ENCODING SCHEME statement in your application program.
The value can be EBCDIC, ASCII, Unicode, or a valid CCSID. If the value is EBCDIC, ASCII, or Unicode, DB2 uses the subsystem default CCSID for that encoding scheme.
The default value is the value of the ENCODING bind option. For native SQL procedures, the default value is the APPLICATION ENCODING SCHEME option of the CREATE PROCEDURE or ALTER PROCEDURE statement. If you do not specify these values, the default value is the subsystem default application encoding scheme.
- DECLARE VARIABLE statement with the CCSID option
- Use
this statement in your application to define a CCSID for a particular
host variable. This value overrides the CURRENT APPLICATION ENCODING
SCHEME special register value, the ENCODING bind option and any compiler
and precompiler CCSID options.
The value for the CCSID option can be EBCDIC, ASCII, Unicode, or a valid CCSID. If the value is EBCDIC, ASCII, or Unicode, DB2 uses the subsystem default CCSID for that encoding scheme.
Use the DECLARE VARIABLE statement with the CCSID option when your application handles a piece of data that you know has a different CCSID.
In the case of bit data, no CCSID is needed. For this type of data, use a DECLARE VARIABLE statement so that no CCSID is associated with the variable, as shown in the following COBOL example:
EXEC SQL DECLARE : hv1 VARIABLE FOR BIT DATA END-EXEC.
If you are using DCLGEN, you can specify DCLBIT(YES) to create DECLARE VARIABLE statements for columns that are declared with the FOR BIT DATA clause. For example, the following DCLGEN output shows such a declaration for a COBOL application:
****************************************************************** * DCLGEN TABLE(ADMF001.T1) * * LIBRARY(USER.DBRMLIB.DATA(T1)) * * LANGUAGE(COBOL) * * QUOTE * * DBCSSYMBOL(N) * * DCLBIT(YES) * * ... IS THE DCLGEN COMMAND THAT MADE THE FOLLOWING STATEMENTS * ****************************************************************** EXEC SQL DECLARE ADMF001.T1 TABLE ( NAME VARGRAPHIC(15), ADDRESS VARGRAPHIC(25), … PASSWORD CHAR(8) ) END-EXEC. ****************************************************************** * DECLARED VARIABLES FOR 'FOR BIT DATA' COLUMNS * ****************************************************************** EXEC SQL DECLARE :PASSWORD VARIABLE FOR BIT DATA END-EXEC. ****************************************************************** * COBOL DECLARATION FOR TABLE ADMF001.T1 * ****************************************************************** 01 DCLT1. 10 NAME. 49 NAME-LEN PIC S9(4) USAGE COMP. 49 NAME-TEXT PIC N(15). 10 ADDRESS. 49 ADDRESS-LEN PIC S9(4) USAGE COMP. 49 ADDRESS-TEXT PIC N(25). 10 CITY. 49 CITY-LEN PIC S9(4) USAGE COMP. 49 CITY-TEXT PIC N(20). 10 STATE PIC N(2). 10 ZIP PIC N(5). 10 PASSWORD PIC (8). ****************************************************************** * THE NUMBER OF COLUMNS DESCRIBED BY THIS DECLARATION IS 6 * ******************************************************************
In this example, notice the DBCSSYMBOL option for DCLGEN. You can use this option to specify how you want COBOL graphic variables to be generated. If you plan to use Unicode variables and the DB2 coprocessor, you should specify DBCSSYMBOL(N) so that you get PIC N variables.