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. ¹
   For 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.

   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 application data for host variables and parameter markers in the program, specify one of the following PP compiler options.

   CCSID0 (default)

   Specifies that only host variables with the WIDECHAR data type are to be assigned a CCSID value by the Db2 coprocessor (PL/I SQL preprocessor). Host variables with the WIDECHAR data type are always assigned a CCSID value of 1200.

   If your program updates FOR BIT DATA columns with a data type that is not BIT data, choose CCSID0. CCSID0 informs Db2 that the host variable is not associated with a CCSID, allowing the assignment to be made.

   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.

   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

   Allows host variables to be assigned a CCSID value by the PL/I SQL preprocessor. If the PL/I SQL preprocessor CODEPAGE option is not also specified, or if the PL/I SQL preprocessor NOCODEPAGE option is also specified, the compiler CODEPAGE option is used as the CCSID for SQL host variables of the CHARACTER data type.

   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))' 

   CODEPAGE

   Specifies that the compiler CODEPAGE option is always used as the CCSID for SQL host variables of the CHARACTER type. The following example of the PARM clause specifies the PP PL/I preprocessor option of the PL/I compiler with the SQL suboption CODEPAGE.

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

   For more information ,see.PL/I PP compiler option.

3. Optional: If you specified the CCSID0 option, you can specify a different encoding scheme for data processed by the application. You can use one or more of the following Db2 approaches to specify CCSID values for the application data², which is called the application encoding scheme:
   • Use the ENCODING bind option.³ This option typically yields the best performance.
   • You can override the CCSID for a particular host variable by using the DECLARE VARIABLE statement with the CCSID option.
   • You can override the CCSID for parameter markers in dynamic SQL by specifying the CURRENT APPLICATION ENCODING SCHEME special register. Db2 uses the value of this special register at the time that the statement is executed.
   • If you use an SQL descriptor to define application data the previous options in this list might not apply if CCSID overrides are defined for host variables and parameter markers in the SQL descriptor (SQLDA).
   
4. If you want to specify a Unicode CCSID for a particular variable, declare it as a WIDECHAR variable, and use the default SQL preprocessor option of CCSID0. For PL/I variables in applications that are processed by the Db2 coprocessor, the Db2 coprocessor always uses CCSID 1200 for WIDECHAR variables. You do not need to use a DECLARE VARIABLE statement with the CCSID clause for these variables.