AS/400 to MS SQL Server: character encoding Q&A

This page covers common questions about character encoding and CCSID handling when replicating from Db2 for IBM i (AS/400) to Microsoft SQL Server.

Q&A

  1. How does Gluesync determine the source character encoding / CCSID when reading character data from Db2 for IBM i?

    Gluesync reads the per-column CCSID from the Db2 for IBM i catalog during schema discovery (the CCSID / CHARACTER_SET_ID of each character column). Conversion is driven by that column CCSID, not by a single global setting.

  2. Who performs the conversion: the IBM JTOpen JDBC driver, IBM i code-page translation tables, or Gluesync itself?

    Both paths use IBM’s CCSID conversion tables (the same tables the IBM Toolbox/JTOpen uses), so the result is identical Unicode:

    • Snapshot (JDBC): the IBM JTOpen driver converts EBCDIC/DBCS → Java Unicode String using the column CCSID.

    • CDC (journals): Gluesync receives the raw journal bytes and decodes them itself with the IBM Toolbox AS400Text converter, keyed by the column CCSID.

Internally, every character value travels as a Unicode Java String. Only the conversion mechanism differs; the resulting value is the same.

For CDC mode, since the data is captured from IBM i Journals, does Gluesync receive already-converted character values, or does it decode raw journal data internally before writing to the target?

Gluesync receives the raw journal bytes and decodes them internally using the column CCSID. The journal does not deliver pre-converted text.

For Snapshot mode, is the character handling different from CDC mode?

No. Internally, every character value travels as a Unicode Java String. Snapshot and CDC produce the same value; only the conversion mechanism differs.

How does Gluesync handle rare Chinese characters, DBCS characters, and AS/400 user-defined characters / private-use characters?

DBCS is fully supported. GRAPHIC/VARGRAPHIC with CCSID 1200 or 13488 are treated as pure Unicode; other graphic CCSIDs are treated as DBCS. Mixed CHAR/VARCHAR DBCS is also handled.

Whether a specific glyph is preserved depends on the IBM CCSID conversion table for that column containing it. Standard Traditional Chinese characters under a correct DBCS/mixed CCSID (for example, 937, 5033, 1388) are preserved as Unicode.

User-defined / private-use characters are only preserved if the host CCSID actually defines them in its conversion table. If a code point is not mappable, the IBM Toolbox substitutes a substitution character. Gluesync does not inject its own ?.

Note: a CHAR column with CCSID 65535 is treated as binary (no text conversion) unless you enable the agent’s Translate binary option. For details, see the EBCDIC encoding section.

Will they be preserved as Unicode when writing to MS SQL Server?

Yes, as long as the target column is NVARCHAR/NCHAR. To keep Chinese and user-defined characters, the target column must be Unicode. See Data type mapping and Fields editor.

Could they be replaced by "?", blank values, or fail during replication if the character is not mappable?

If a character is not mappable, the IBM Toolbox conversion layer substitutes a substitution character. Gluesync does not raise a per-row error and replication will not fail.

A Gluesync UDF (user-defined function) cannot recover a character already lost on the host, but it can be used to detect, flag, or remap substitution characters per row. For more about UDFs, see User Defined Functions.

Is there any error log or warning when unmappable characters are encountered?

There is a JDBC option Enable data truncation warnings on the agent. There is no dedicated "unmappable character" error event; substitution is handled by the IBM conversion layer and is not raised as a per-row error.

On the MS SQL target side, do you recommend using NVARCHAR / NCHAR columns to preserve Chinese and user-defined characters?

Yes. NVARCHAR/NCHAR are fully supported target types. We recommend using Unicode column types for any character data that may contain Chinese, DBCS, or user-defined characters.

If Gluesync creates the target table automatically, will it map AS/400 character fields to Unicode-compatible MS SQL types?

No. The default mapping for string columns is VARCHAR (code-page dependent), which will not preserve Chinese and may produce ?. Before creating the table from the Gluesync UI, override the column type to NVARCHAR/NCHAR in the mapping, or adjust the generated DDL. This is the single most important step for this scenario. See Target table creation.

Or should we manually adjust the generated DDL before starting Snapshot / CDC?

Yes. Override the target column type to NVARCHAR/NCHAR in the Fields Editor or edit the generated DDL before execution. Do this before starting the first snapshot or CDC task.

Are there any recommended IBM i JDBC connection properties, CCSID settings, job CCSID settings, or Gluesync agent configurations for Traditional Chinese AS/400 environments?

Make sure the source columns have a correct DBCS/mixed CCSID (not 65535) and that the job CCSID is a proper Chinese CCSID. For the agent, enable Translate binary only if you need CCSID 65535 columns converted. No special JDBC properties are required beyond the standard connection settings.

If the customer uses AS/400 custom fonts or user-defined character tables, is there any additional setup required on the Gluesync agent host or MS SQL target to preserve those characters correctly?

No extra font or table install is needed on the Gluesync host or on MS SQL. For user-defined characters, the host CCSID must have the user-defined character tables associated with it — this is an IBM i host-side prerequisite.

Could you provide the best-practice configuration or a simple validation procedure to test whether rare Chinese characters and user-defined characters are replicated correctly from AS/400 to MS SQL?

  1. Create a small test table on IBM i with a few rows containing common Chinese, rare Chinese, and known user-defined/private-use characters.

  2. Run the snapshot to an MS SQL table whose text column is NVARCHAR.

  3. Perform a few INSERT/UPDATE operations on the source so the same rows flow through CDC.

  4. On MS SQL, compare with SELECT col, UNICODE(col), CONVERT(VARBINARY, col) against the expected Unicode code points. Snapshot and CDC values must match. If any character shows as ?, the target column is not NVARCHAR or the source CCSID does not define that character.