General Information about Query and CCSID (Coded Character Set Identifier) Relationships

This document is a list of questions and answers about CCSID and query.

Following are some general questions regarding CCSIDs:

Q1: When attempting to change a physical file from CCSID 500 to 37, it fails with message CPD322D RC1 - explicitly specified CCSIDs or file restrictions present. This appears only to be occurring on query outfiles. Why? Could it occur on nonquery files?

A1: This condition occurs in either of the following situations:

o	When the input fields are defined with multiple CCSID values.
o	When a summary outfile is created. This error does not occur on any other physical files unless explicit CCSID definition was defined in DDS at the field level.

Q2: What is the role of CCSID on the query definition?

A2: CCSID on a query definition only applies to the CCSID values used by constants in the query. It does not affect the processing of data files. CCSID assignments for fields in outfiles are based on the input definition. Fields output from an input file use the CCSID of the input file. Fields output from constants use the CCSID of the query definition.


Q3: Would a file defined with CCSID 500 running under a job defined with CCSID 37 perform better if the file's CCSID matched the job?

A3: Yes. If the file were being updated, the data would be converted on reads and writes. Changing the file and job CCSIDs to match would eliminate this conversion.


Q4: Message CPD322D contains RC2 stating that logical files prevent changing the CCSID of the physical file. Why isn't this error occurring on my physical files with logicals over them?

A4: Beginning in R310, code changes were implemented removing this restriction except for the following circumstances:

o	A keyed logical file with a Sort Sequence Table that is not compatible with the CCSID to which the physical file is being changed.
o	A keyed logical file with a Sort Sequence Table and field CCSIDs such that the CCSID of the logical file's Access Path is different than the CCSID to which the physical file is being changed.
o	A select/omit or join logical file, or both, that performs select/omits or joins between physical file fields that have different CCSIDs.
o	A join logical file with a Sort Sequence Table such that the CCSID of the Secondary Access Path of the logical file is different than the CCSID to which the physical file is being changed.

Q5: CHGPF completed with message CPD3238, stating that a new record format has been created. Why is this occurring, and why is the message text regarding the file name unreadable?

A5: Record formats can be shared between files. This most commonly occurs between a physical file record format and is based on logical files that reference all fields of the physical file. It also occurs when a duplicate of the physical file is made to another library. This error states that a new record format (with the new CCSID) was created to now be used by the physical file. The original record format is left on the system to be used by the other files that were sharing the format. If necessary, the owner of the original record format is changed to one of the sharing files.


Q6: How should data specific to a CCSID be converted to the proper hex value of the new CCSID?

A6: Create a new version of the physical file with the new CCSID value. Run a CPYF of the original file to this new file specifying format option *MAP and *DROP. Then, change the CCSID of the original file and copy the data back, or delete the original file and replace it with the new file.


Q7: On a system where some files have explicitly tagged CCSIDs, the upgrade might create a mixed environment of database files with multiple CCSID values. This could result in performance degradation. It is desirable to have database files within a job defined under the same CCSID as the job. For example, if a batch job runs under CCSID 37 but accesses files of CCSID 37 and 500, the files with CCSID 500 require CCSID conversion to CCSID 37 to match the job definition. In this example, converting the files with CCSID 500 to 37 (using the technique in item 6) might improve performance of the batch job.

A7: The decision on the value for system value QCCSID depends on the CCSID value chosen to convert a mixed environment to. In the example above, if the database files are converted to CCSID 37, then QCCSID could be set to 37 or 65535. CCSID 65535 is acceptable in this case because new database files are created based on the QLANGID value of ENU, which is CCSID 37. If the decision was made to convert to CCSID 500, QCCSID of 65535 is acceptable only if the QLANGID value defaults to CCSID 500 (for example, language ID DES). For most customers, setting QCCSID to a specific value rather than 65535 is easier to understand because knowledge of how the CCSID value is determined for 65535 would not be required.


Q8: Provide an example of the conversion that would occur on a database file that was originally defined with CCSID of 65535, changed to CCSID of 500, then displayed on a job running with CCSID of 37. A simple example is a file that contains a character field with a length of 1. A record in this file contains the left bracket character in the character field.

A8: The file was originally created at R230 with a CCSID value of 65535. This CCSID defines all data in the file as hexadecimal data with no conversion. The system is an EBCDIC-based system so contents recorded to the file use the EBCDIC encoding scheme. This scheme equates to CCSID 37.

A data record is added to this file with a left bracket. Regardless of the job CCSID value, the data is recorded into the file with the default EBCDIC encoding hexadecimal value. This hex value is x'BA' (the CCSID 37 value).

The file is changed to CCSID 500 using CHGPF. CPYF is not run on the file, so the data in the file remains the same. Changing the CCSID of a file does not convert the data; this must be accomplished with a technique such as Q6 above.

A job running under CCSID 500 displays the data. Since the job CCSID and the file CCSID are identical, no conversion occurs. The data is displayed based on the default EBCDIC encoding scheme (CCSID 37). The left bracket displays properly. A new record added would output the left bracket with the hex value of x'BA' (the CCSID 37 value).

A job running under CCSID 37 displays the data. Because the job CCSID and the file CCSID are now different, the data is converted from the assigned CCSID value (500) to the CCSID value for the job. If the data in the file had been converted when the file was originally changed to CCSID 500, the hex value would be x'4A'. This value would then be converted to the appropriate CCSID 37 hex value and the left bracket would be displayed. But because the data was not converted, the data is converted from the CCSID 500 hex value of x'BA' which displays the logical NOT character.

The DSPPFM and CPYF to print commands display the character value of a field based on the default CCSID of the QLANGID system value. For US English, this will be CCSID of 37. This can be very confusing on files with a CCSID other than 37 or 65535. For example, if a file CCSID is 500 and a left bracket is entered into the file, the hex value for the left bracket is x'4A'. If displayed via DSPPFM or CPYF, the hex value displays as x'4A'. However, the character represented by the hex value will not be the CCSID 500 value of a left bracket but the CCSID 37 value of a cents sign. Remember, DSPPFM and CPYF to print always shows the actual hexadecimal value, but displays the character value using CCSID 37.