CCSID and Code Pages – Character Encoding on IBM i (AS/400)

1. Introduction

Anyone working with IBM i will sooner or later encounter the terms code page and CCSID. Usually this happens when accented characters are displayed incorrectly, special characters disappear, or unexpected characters appear when exchanging data with Windows, Linux and other systems. The cause often lies not in the data itself, but in how it is interpreted.

2. What is a code page?

A computer does not store letters but numerical values. A code page specifies which code value represents a particular character.
Historically, two encoding families are particularly important:
  • ASCII1: widely used on PCs and Unix systems
  • EBCDIC2: traditionally used on IBM mainframes and IBM i
Here are two examples:
Character            Encoding                   Hexadecimal     Decimal
Capital letter A     ASCII                      41              65
                     EBCDIC (code page 500)     C1              193
                     UTF-8                      41              65
Space                ASCII                      20              32
                     EBCDIC (code page 500)     40              64
                     UTF-8                      20              32
An EBCDIC text cannot therefore simply be interpreted as ASCII. The data must be converted appropriately.

3. What is a CCSID?

CCSID stands for “Coded Character Set Identifier”3
While a code page describes the mapping of characters to code values, an IBM CCSID identifies the character encoding used, including the character set and its associated encoding rules. Code pages also take account of country-specific, regional and language-specific characteristics.
Typical CCSIDs on IBM i include:
CCSID   Meaning
37      EBCDIC, USA/Canada
273     EBCDIC, Germany
500     EBCDIC, international
1141    EBCDIC, Germany with euro sign
1208    Unicode UTF-8
13488   Unicode UTF-16
65534   No assigned character encoding; normal (automatic) text conversion not possible (*MSG)
65535   No assigned character encoding; normal (automatic) text conversion not possible (*HEX)
CCSID 65535 is a legacy issue. Its particular characteristics are examined below.

4. CCSIDs on IBM i

Different CCSIDs can coexist on IBM i, for example at the following levels:
- System value        DSPSYSVAL SYSVAL(QCCSID)
- User profile        DSPUSRPRF USRPRF(MYUSER) TYPE(*BASIC)
- Job                 DSPJOB OPTION(*DFNA)
- Database field      DSPFFD FILE(MYLIB/MYFILE)
- Source member       DSPFD FILE(MYLIB/QRPGLESRC) TYPE(*ATR)
- Files in the IFS    DSPATR OBJ('/home/MYLIB/MYFILE.txt')
At database field level, the information can also be displayed using SQL:
SQL
SELECT
    ORDINAL_POSITION,
    SUBSTR(COLUMN_NAME, 1, 10) COLUMN_NAME,
    DATA_TYPE,
    LENGTH,
    CCSID
FROM QSYS2.SYSCOLUMNS
WHERE TABLE_SCHEMA = 'MYLIB'
  AND TABLE_NAME   = 'MYFILE'
ORDER BY 1;
During database operations, IBM i can automatically convert between different CCSIDs, provided their encodings are known and the characters can be represented. This automatic conversion is one of the platform's strengths.

5. Typical problems in practice

Different CCSIDs

If a program writes text from an environment using CCSID 273 into a database field with CCSID 37, IBM i performs the necessary conversion itself. If an incorrect CCSID is declared, however, special characters may become corrupted.

Data exchange with Windows and Linux

UTF-8 is now frequently expected for CSV files and interfaces. An export can, for example, be performed as follows:
CMD
CPYTOIMPF FROMFILE(MYLIB/MYFILE)
          TOSTMF('/home/export.csv')
          STMFCCSID(1208)
          RCDDLM(*CRLF)
This creates the output file with CCSID 1208 (UTF-8). The prerequisite is that the source text fields are correctly declared so that meaningful conversion is possible.

RPG and SQL

RPG programs and SQL statements are also affected by CCSID differences. Particularly when combining EBCDIC fields, Unicode data and external interfaces, it is essential to know which CCSID each operand uses. Incorrect character encoding is not corrected merely by assigning a different CCSID. The actual data may need to be converted.

6. CCSID 65535 – a typical legacy issue

CCSID 65535 is frequently encountered in older AS/400 applications. It indicates that no specific character encoding has been assigned to the files, fields or bytes, so the usual automatic text conversion does not take place. This does not necessarily mean that the stored data is actually binary. Such fields often contain perfectly ordinary EBCDIC text. The definition simply does not specify which code page should be used to interpret that text.

Why was 65535 used?

There were, and still are, various reasons:
  • Automatic character conversions were to be avoided
  • The application operated exclusively in a homogeneous EBCDIC environment
  • Some fields contained both text and binary content
  • Existing programs were to continue running unchanged
As long as the data is used exclusively within the familiar IBM i environment, these disadvantages are barely noticeable.

CCSID 65534 and 65535 in IBM objects

The default CCSID of message queues, job message queues, system reply lists and history logs is 65534. This value is also known as *MSG or *MSGD.
The default CCSID of message files is 65535. This value is also known as *HEX.

Where do difficulties arise today?

An example: A customer file contains names and addresses in an alphanumeric field with CCSID 65535. In reality, however, the bytes are encoded using the German EBCDIC code page.
An older RPG program displays the names correctly because it assumes the same interpretation of the bytes. A new web application reads the data through an SQL interface and expects UTF-8.
Because the field definition does not provide a usable text CCSID, automatic conversion does not take place. The result: unreadable characters or incorrectly displayed accented letters or special characters.4

How can the problem be solved?

The first step is to examine the bytes actually stored and the encoding previously used. Only then can it be determined how to interpret the content correctly and, if necessary, convert it to a new CCSID. For new interfaces, targeted conversion to UTF-8 is often appropriate. For existing database files, it may make sense in the longer term to define text fields with their actual CCSID. Caution is required: merely redeclaring a CCSID does not clean up the data. Changing the field definition or the underlying bytes can affect RPG programs, sorting, comparisons, database access and interfaces. CCSID 65535 is not inherently wrong. It can be useful for genuine binary data or bytes deliberately transmitted without modification. For ordinary text fields, however, it is often a major stumbling block.
The value 65535 deserves particular attention for the QCCSID system value: during modernisation, the job and field CCSIDs actually in use, together with the interfaces involved, should be checked rather than changing the system setting without due consideration.

7. Unicode – the modern alternative

Unicode defines a universal character set for virtually all characters commonly used today. Widely used encoding forms include UTF-85 and UTF-166. UTF-8 requires between one and four bytes depending on the character. Ordinary ASCII characters (A-Z, 0-9) have the same byte values in UTF-8 as in ASCII. IBM i has supported Unicode for many years. Modern SQL applications, web services and interfaces can therefore use UTF-8 without having to convert all existing EBCDIC applications at once. This is precisely one of the advantages: proven RPG applications can continue working with EBCDIC while new interfaces use Unicode.

8. Practical recommendations

In existing IBM i applications, character encoding should not be changed without a thorough prior analysis. Sensible measures include:
  1. Document system, database and job CCSIDs
  2. Identify text fields with CCSID 65535 and examine their actual content
  3. Explicitly specify the expected character encoding for external interfaces
  4. Prefer UTF-8 for new text-based interfaces
  5. Test conversions using accented characters, the euro sign and other special characters
  6. Examine existing programs for assumptions about byte values, field lengths and sort orders
Finding database fields with CCSID = 65535:
SQL
SELECT
    SUBSTR(TABLE_SCHEMA, 1, 10) LIBRARY,
    SUBSTR(TABLE_NAME, 1, 10) FILE,
    SUBSTR(COLUMN_NAME, 1, 10) FIELD,
    ORDINAL_POSITION POS_IN_FILE,
    DATA_TYPE,
    CCSID
FROM QSYS2.SYSCOLUMNS
WHERE CCSID = 65535
  AND DATA_TYPE IN ('CHAR', 'VARCHAR', 'CLOB')
ORDER BY 1, 2, 4;

9. Conclusion

Code pages and CCSIDs are among the fundamentals of IBM i that often go unnoticed in day-to-day operations. As long as all the systems involved use the same character encoding or convert correctly, everything works unobtrusively. Problems arise mainly during data exchange, modernisation or when new applications access older data. CCSID 65535 is a typical example: what worked reliably in the original environment can become an obstacle for a modern interface.
The key insight is that EBCDIC itself is not the problem; the problem is missing or incorrect information about how stored bytes should be interpreted. For decades, IBM i has provided the technical means to connect different character encodings in a controlled manner. A proper assessment and targeted conversion are therefore often more sensible than a hasty migration of existing applications.

Sources and further reading

IBM documentation, QCCSID system value:
https://www.ibm.com/docs/en/i/7.5?topic=values-coded-character-set-identifier-qccsid-system-value
IBM Support, Data Transfer and CCSID 65535 Database Files:
https://www.ibm.com/support/pages/data-transfer-and-ccsid-65535-database-files
IBM documentation, SQL CAST Specification:
https://www.ibm.com/docs/en/i/7.4.0?topic=expressions-cast-specification

This article is an AI-assisted translation of the German original

German original: CCSID und Codepage – Zeichencodierung auf IBM i (AS/400)