Specifying CCSIDs for PL/I applications when using the DB2 coprocessor

If you are using the DB2® coprocessor to prepare a PL/I application with SQL statements, use the PL/I compiler to specify the CCSID of the application source code. For optimal performance, use DB2 to specify the CCSID of the application data in SQL statements.

To specify CCSIDs for PL/I applications when using the DB2 coprocessor:

1. To specify the CCSID of the PL/I application source code, use the CODEPAGE compiler option. ¹
   Example: Both of the following JCL EXEC statements for PL/I compile jobs specify a CCSID of 37:

   //PLI EXEC PGM=IBMZPLI,PARM='...,PP(SQL,...),CODEPAGE(37)...'

   //PLI EXEC PGM=IBMZPLI,PARM='...,PP(SQL,...),CP(37)...'

   Otherwise, if you do not specify the CODEPAGE compiler option, the default PL/I compiler CCSID is passed to the DB2 coprocessor and is used as the CCSID for the source code. The default PL/I compiler CCSID is 1140 unless you changed it.

   Example: The following JCL EXEC statement for a PL/I compile job does not explicitly specify a CCSID. In this case, the PL/I compiler passes the default CCSID, 1140, to the DB2 coprocessor.

   //PLI EXEC PGM=IBMZPLI,PARM='PP(SQL())'

   CCSID 1140 is equivalent to CCSID 37 plus the euro symbol (€). However, be aware that conversions, and thus conversion cost, still occur between CCSID 1140 and CCSID 37.

   Recommendation: If you are using the DB2 coprocessor to prepare a PL/I application, do not specify the PL/I preprocessor option SQL with the CCSID suboption. If you specify it anyway, and it conflicts with the CODEPAGE compiler value, DB2 issues a warning. ² For example, the following EXEC statement for a PL/I compile job specifies the CODEPAGE compiler option with a CCSID value of 1140; the CCSID value 37 is ignored:

   //PLI EXEC PGM=IBMZPLI,PARM='CP(1140),PP(SQL("CCSID(37)"))'

   To verify which CCSID value was used, look at the compiler listing and check the CCSID option.

2. To specify whether DB2 or the PL/I compiler determines the CCSID of the application data in SQL statements, specify one of the following PP compiler options:

   CCSID0 (default and recommended option)

   Specifies that the CCSID that is passed from the PL/I preprocessor of the PL/I compiler is used only for PL/I application source and string literals. That CCSID is not used for the host variables and parameter markers in SQL statements. For host variables and parameter markers, DB2 uses the CCSIDs that are specified through DB2 mechanisms, as described in the next step.

   Specifying CCSID0 can yield better performance, because you can then use a DB2 mechanism, such as the ENCODING bind option, to specify the host variable CCSIDs.

   CCSID0 simulates the behavior of the DB2 precompiler, which does not use the CCSID from the PL/I compiler for host variable data.

   Example: The following examples of the PARM clause specify the PP PL/I preprocessor option of the PL/I compiler with the SQL suboption CCSID0

   PARM='S,XREF,PP(SQL(APOSTSQL,CCSID0))' 
   PARM='S,XREF,PP(SQL(APOSTSQL))'

   NOCCSID0

   Specifies that the DB2 coprocessor is to use the CCSID from the PL/I CODEPAGE(nnnnn) compiler option for your application data. ² The CCSID value is the same one that you specified for your PL/I source code.

   Example: The following example of the PARM clause specifies the PP PL/I preprocessor option of the PL/I compiler with the SQL suboption NOCCSID0

   PARM='S,XREF,PP(SQL(APOSTSQL,NOCCSID0))' 

3. Optional: If you specified the CCSID0 option, explicitly specify a value for the ENCODING bind option

   The ENCODING bind option value is used as the CCSID for the application data. If you do not explicitly specify a value, the default value for the ENCODING bind option is used.

4. To override the CCSID for particular host variables or parameter markers, use the DECLARE VARIABLE statement or CURRENT APPLICATION ENCODING SCHEME special register. If you need help using either of these techniques, following the instructions in Specifying a CCSID for your application.

   If you specify different CCSIDs for different pieces of data, do so with caution.

5. If you want to specify a Unicode CCSID for a particular variable, declare it as a WIDECHAR variable. For PL/I WIDECHAR variables in applications that are processed by the DB2 coprocessor, the DB2 coprocessor always uses the CCSID 1200. You do not need to use a DECLARE VARIABLE statement with the CCSID clause for these variables.