Data Transfer and CCSID 65535 Database Files

When using Client Access for Windows 95/NT, Client Access Express, and iSeries Access data transfer, data transferred from the IBM System i products might appear as hexadecimal numbers rather than as ASCII characters. This occurs any time the data on the System i server has been labeled as being binary data by using a CCSID of 65535. This document explains what a CCSID is and outlines the relationship between a database file containing a character field with CCSID of 65535 and Client Access data transfer.

What Are Database Files with Coded Character Set Identifier (CCSID) 65535?

A Coded Character Set ID (CCSID) is a part of National Language Support (NLS). The CCSIDs assign a value that uniquely identifies the coded graphic character representation used for character data. In other words, it defines how the binary data stored in an IBM OS/400 or IBM i5/OS file should be displayed to the user. For example, a character field with data of x4F marked CCSID 00500 (Multilingual) will appear as an exclamation point (!). The same data, x4F, in a field with CCSID 00037 (US/Canada) will appear as a solid vertical bar (|). The same data in a field with CCSID 65535 will be displayed by data transfer as 4F.

A CCSID is assigned to fields and files during creation. Database files on OS/400 and i5/OS can be created through a number of different methods, but all files fall into two categories: (1) externally described files, and (2) flat files. Externally described files can be created by compiling Data Description Specifications (DDS) source code or by using an SQL CREATE TABLE command. Flat files are usually created by issuing the CRTPF command from a command line and specifying a fixed record length rather than a DDS source file. When either of these file types are created, a CCSID is assigned to the file or fields in a file as an attribute that is used to determine how the data is stored. What CCSID is assigned varies depending on how the file was created.

When DDS source is compiled to create an externally described file, the default CCSID of the file (and any character fields in the file) is the same as the Default coded character set identifier parameter of the current job. The job's default CCSID is determined as follows: "If the job coded character set identifier (CCSID) is not 65535, the default CCSID will equal the job CCSID. If the job CCSID is 65535 (the operating system default), an appropriate value is set for the default CCSID based on the job's language identifier LANGID." The job's language identifier comes from the user profile information and this in turn defaults to the appropriate system value.

When the CRTPF command is used to create a fixed record length file without using a source file (flat file) the operating system will always create the file with CCSID 65535 - binary data. Additionally, some tools on the operating system such as Query/400 will output database files with character fields defined as CCSID 65535.

The Convert CCSID 65535 forced conversion is based on job CCSID. If the job CCSID is not compatible with the column data, incorrect results can occur. If the user profile has not set a specific CCSID (for example, 65535 or *SYSVAL that is 65535), the job CCSID is derived from the client attributes and language ID on the host. Starting with iSeries Access V5R3 (to support Unicode SQL statements properly), the iSeries Access DB interfaces send Unicode attributes that will typically force a job CCSID of 37. This is fine for English users but will cause problems for all other languages.

What Impact Does This Have When Transferring Data to the PC Using Client Access?

When transferring data across platforms, the CCSID is used to determine how the data is handled during the transfer. US English uses 037, Multilingual is 500, but 65535 by definition implies that there is no character set associated with binary data. In the case of Client Access, if the CCSID of the file, or individual fields is 65535, no conversion takes place and the data represents its respective EBCDIC hex value.

Client Access has a setting that can be used to control how the CCSID 65535 data is handled when being transferred. In release V3R1M3 and later, this setting is accessible from the File drop-down menu, Properties, select the Conversion tab, and select the check box for convert CCSID 65535. In previous releases of Client Access this setting is controlled from an .ini file. A file named CWBTFR.INI must be created in the Microsoft Windows directory. Client Access looks in this file for the following entries:

Client Access Data Transfer
ForceTranslation=1; Set to 1 to force 65535 translation, 0 for the default behavior.

If Client Access data transfer finds this information in the CWBTFR.INI file, it translates any field or file with CCSID 65535 during the transfer. The data is no longer in hex, representing the original EBCDIC value. If ForceTranslation=0 is used, translation does not occur, and the resulting data is hex representing the original EBCDIC value. Refer to Rochester Support Center knowledgebase document N1010114, Undocumented Data Transfer Options: CWBTFR.INI. To link to N1010114 immediately, click here .

When a file is transferred to the PC using Client Access data transfer, a file description file can be created. This file description file contains information about the OS/400 or i5/OS file, including record length and field length. If no translation occurs, this information can reflect the actual value of the record length of the OS/400 or i5/OS file. For example, if a file exists on the System i with a record length of 512 and the data is transferred to the PC creating a file description, the file description indicates 1024 as the OS/400 or i5/OS record length if Force translation is set to 0 or if the CWBTFR.INI file does not exist. This creates an error if the File description file is used to transfer the data back to the System i (CWBTF0005).
 

Caution: Technically, when force translation is used, you are corrupting the data. This is why Client Access continues to ship a default value of force translation disabled. Some CCSIDs do not support round trip conversion. If a file that really does contain binary data (CCSID 65535) is downloaded with force translation and then uploaded back to the System i, the data might be altered and corrupted. Before setting force translation on, be sure that the data you are accessing really is intended to be displayable character data.

Changing the CCSID of a Database File

The CCSID of a flat (program-described) file cannot be changed. If you attempt to set the CCSID parameter on either the CRTPF command or CHGPF command, an error will occur. To correct the problem you would need to create a file with the correct CCSID using SQL or DDS and then use the CPYF command to copy the data. For example, to create a file for use with the CPYSPLF command (one character field that is 132 bytes long) use the following DDS:

 R REC1
     A             F1             132A