Search docs.molo17.com
    Configuration guide Connectors

    Map IBM i data types to PostgreSQL without losing text, digits or dates

    Gluesync reads every IBM i column with the type and the CCSID that the column declares, and writes it to the target with the type that the Field mapping dialog proposes. Most columns arrive unchanged, but a few do not: text tagged CCSID 65535 shows up as hex, a wrongly tagged column shows citt{ } pi`, a DECFLOAT column cannot be confirmed, a target column that is too narrow stops the entity, and a column with too few decimals rounds your numbers without a word. This guide shows what lands on PostgreSQL for each IBM i type, which setting changes it, and how to check every case with SQL.

    In this guide

    You will learn how to read what the IBM i declares for each column, change the target type before the table is created, choose how CCSID 65535 and FOR BIT DATA columns are read, correct a column that carries the wrong CCSID, remove trailing blanks, compare the target with the IBM i byte by byte, and find out when a value is rejected and when it is silently rounded. A mapping table at the end lists every IBM i type that we tested.

    Quick information

    Goal

    Get text, binary values, decimals, dates and timestamps from Db2 for IBM i to PostgreSQL exactly as you expect, and know where they would change

    Audience

    Data engineers and IBM i administrators who build and maintain pipelines from IBM i

    Level

    Advanced

    Time required

    About 60 minutes

    Area

    Control Plane → Pipelines → entity Options → Edit mapping; source agent → Edit source

    Applies to

    Gluesync 2.3

    Before you begin

    Make sure that:

    1. You can sign in to the Gluesync Control Plane with a role that can edit pipelines;

    2. A pipeline already has an IBM Db2 for IBM i source and a PostgreSQL target that Gluesync can read, and the IBM i tables you want to replicate are journaled with before and after images. This guide uses the pipeline IBM i types demo, the IBM i library GSDOCSTYP and the PostgreSQL schema ibmi_types;

    3. The target tables do not exist yet, or you accept that you change their columns in the database when the guide says so;

    4. You can run SQL on the target (for example in Query Studio) and on the IBM i (for example in ACS Run SQL Scripts or STRSQL).

    Optional: create the sample tables

    Skip this if you already have tables with the columns you want to check. To repeat the examples of this guide, create a small library with a journal, then run the statements below in it from your own IBM i SQL session. The hexadecimal literals write the bytes of città è più as an application with CCSID 280 would store them, in a column tagged CCSID 65535.

    CREATE TABLE GSDOCSTYP.GS_TEXT (
 ID INTEGER NOT NULL PRIMARY KEY,
 LABEL VARCHAR(40) CCSID 37,
 C37 CHAR(12) CCSID 37,
 C280 CHAR(12) CCSID 280,
 U8 VARCHAR(30) CCSID 1208,
 C65535 CHAR(12) CCSID 65535,
 V65535 VARCHAR(30) CCSID 65535,
 BIN4 BINARY(4),
 NC NCHAR(6));

INSERT INTO GSDOCSTYP.GS_TEXT VALUES
 (2, 'accents', 'città è più', 'città è più', 'città è più €',
  X'8389A3A3C040D040978979', X'8389A3A3C040D040978979', BINARY(X'01020304'), 'città'),
 (3, 'blanks', 'AB', '  AB  ', 'AB   ',
  X'C1C240', X'C1C24040', BINARY(X'00000000'), ' A');

CREATE TABLE GSDOCSTYP.GS_NUMBERS (
 ID INTEGER NOT NULL PRIMARY KEY,
 DEC52 DECIMAL(5,2), DEC90 DECIMAL(9,0), DEC6330 DECIMAL(63,30),
 NUM52 NUMERIC(5,2), DF16 DECFLOAT(16));

CREATE TABLE GSDOCSTYP.GS_DATES (
 ID INTEGER NOT NULL PRIMARY KEY,
 D DATE, T TIME, TS3 TIMESTAMP(3), TS12 TIMESTAMP(12));
INSERT INTO GSDOCSTYP.GS_DATES VALUES
 (2, '0001-01-01', '00.00.00', '0001-01-01-00.00.00.000', '0001-01-01-00.00.00.000000000000'),
 (3, '9999-12-31', '23.59.59', '9999-12-31-23.59.59.999', '9999-12-31-23.59.59.999999999999');

    Journal each table with STRJRNPF …​ IMAGES(*BOTH) into a journal of your own library before you add it to the pipeline.

    Guide steps

    Read what the IBM i declares for each column

    UI

    1. Open Data pipelines and select your pipeline;

    2. In the row of the entity, select Options (the three-dot icon), then Edit mapping;

    3. In the Field mapping dialog, read the Source type column: it shows the IBM i type with its length, for example CHARACTER (12), CHARACTER FOR BIT DATA (12), DECIMAL (5, 2) or NATIONAL CHARACTER (6);

    4. Read the Target type column: it shows the type that Gluesync proposes for PostgreSQL.

    Field mapping dialog for GS_TEXT with the Source type column marked by annotation 1 and the Target type column marked by annotation 2
    Figure 1. Compare the Source type column with the Target type column of the Field mapping dialog.
    Expected result

    Every field has an arrow from its source type to a target type. In the example, the CHAR and VARCHAR columns with CCSID 37, 280 and 1208 become character varying, the columns tagged CCSID 65535 and the BINARY columns become bytea, and the NCHAR and NVARCHAR columns become character varying.

    1. Select Show all columns (the eye icon, at the top right of the dialog);

    2. Read the Char set column on the source side: it is the CCSID that the IBM i declares for each character column.

    Field mapping dialog with the Show all columns icon marked by annotation 1 and the Char set column marked by annotation 2
    Figure 2. Show all columns adds the Char set column with the CCSID of every column.
    Expected result

    The Char set column lists 37, 280, 1208, 65535 and 1200 for the example columns. A CHAR or VARCHAR with 65535 is shown with the source type CHARACTER FOR BIT DATA or CHARACTER VARYING FOR BIT DATA, because the IBM i does not convert such a column.

    1. Close the dialog with Cancel, open the entity, and select Compare DDL in the Inspect tables section to see the IBM i CREATE TABLE next to the PostgreSQL one.

    Compare table DDL dialog with the IBM i DDL on the left marked by annotation 1 and the PostgreSQL DDL on the right marked by annotation 2
    Figure 3. Compare DDL shows the CCSID of each IBM i column next to the PostgreSQL column.
    Expected result

    The IBM i side shows every column with its length and CCSID, for example "C280" CHAR(12) CCSID 280 and "C65535" CHAR(12) FOR BIT DATA. The PostgreSQL side shows the table that Gluesync created.

    Gluesync keeps the IBM i column names, which are upper case, so on PostgreSQL they must be written between double quotes, for example "C37".
    Change a target type before the table is created

    UI

    1. Add the entity: in the pipeline, select Entities, open the library, and select the table. The Field mapping dialog opens;

    2. Select the Target Schema and enter a new name in Target Table;

    3. Select Create table "<name>". The dialog now lets you change the target types, because Gluesync creates the table;

    4. In the row of the field, open Target type and select the type, for example char for a CHAR column that must stay padded, or text for a long text;

    5. For a DECFLOAT column enter a Precision and a Scale that fit your values. Without them the dialog shows Missing required field and Confirm stays disabled;

    6. Select Confirm, review the generated CREATE TABLE statement, and select Create table.

    Field mapping dialog for a new table with the Target type of C12 marked by annotation 1 and the Target type of V30 marked by annotation 2
    Figure 4. Choose the target type of each field; the types of an existing table cannot be changed here.
    Expected result

    The wizard shows the CREATE TABLE statement with your types, and the table appears in the target schema. With char on the CHAR(12) column the table has character(12), and the value AB is stored as AB followed by ten blanks.

    A DECFLOAT column has no fixed number of decimals, but the target column does. Gluesync does not choose a scale for you, and PostgreSQL rounds every value to the scale you enter. In our test DECFLOAT(34) was mapped to numeric(44,10), and the value 0.0000000000000000000000000000000001 arrived as 0.0000000000.
    Choose how CCSID 65535 and FOR BIT DATA columns are read

    UI

    A column tagged CCSID 65535 (CHAR, VARCHAR or CHAR … FOR BIT DATA) holds bytes, and the IBM i does not say which code page they are in. Gluesync gives you three ways to read it. Pick one for the whole agent or for each column.

    Option What lands on PostgreSQL Use it when

    Default (Translate binary off)

    bytea. The bytes are copied unchanged. You read them with encode("C65535", 'hex'), which gives for example 8389a3a3c040d040978979.

    The column holds real binary data, or you do not know the code page.

    Byte Array to String field function with a code page

    varchar. The text is the same in the snapshot and in change data capture (CDC).

    You know the EBCDIC code page of the text, for example IBM280.

    Translate binary on

    varchar for every FOR BIT DATA column of the agent. The code page is not yours to choose.

    All the 65535 columns of the agent hold text in the code page that the IBM i connection uses.

    To use the field function:

    1. In the Field mapping dialog of the entity, set the Target type of the column to varchar and enter its Length;

    2. Select the fx icon at the end of the row;

    3. In Functions collection, select Byte Array to String (it is listed under Built-in);

    4. In Select map to expression, select the code page, for example IBM280;

    5. Select Confirm.

    Field mapping dialog with the fx icon marked by annotation 1
    Figure 5. Apply Byte Array to String with the IBM280 code page to a CCSID 65535 column.

    To use Translate binary instead:

    1. Open the pipeline and select the IBM i agent icon at the top of the pipeline page. The Edit source page opens;

    2. On the Base settings tab, turn on Translate binary;

    3. Select Test and continue and finish the wizard;

    4. Create the entities after this change, so that the new tables get varchar for these columns.

    Edit source page
    Figure 6. Translate binary is a switch of the whole source agent.
    Expected result

    With the field function, the column C65535 that holds the bytes 8389A3A3C040D040978979 reads città è più on the target, for the rows copied by the snapshot and for the rows that arrive through CDC. The dialog keeps the warning Data type mismatch detected for the column, because a byte array is converted to text, but it does not block Confirm. With Translate binary on, the new tables get varchar instead of bytea for these columns, and BINARY and VARBINARY columns still get bytea.

    Translate binary does not give the same text in the snapshot and in CDC when the IBM i connection does not use CCSID 37. In our test, on an IBM i whose system language is Italian (the connection decodes with CCSID 280), the same bytes read città è più in the snapshot and citt{ } pi` in CDC, because CDC decodes CCSID 65535 as 37. The Char set override of the next step is ignored for these columns. Use the field function with the code page when you need the same text in both. Also, a FOR BIT DATA column with real binary values can stop the entity: bytes such as 0000FFFF become the character NUL, and PostgreSQL rejects it with invalid byte sequence for encoding "UTF8": 0x00.
    Correct a column that carries the wrong CCSID

    UI

    Sometimes an application writes the bytes of one code page into a column tagged with another, for example CCSID 280 data in a column tagged 37. The IBM i reads it as it is tagged and shows citt{ } pi`. Gluesync does the same, so tell it the real CCSID.

    1. In the row of the entity, select Options, then Edit mapping;

    2. Select Show all columns;

    3. On the source side, in the Char set cell of the column, replace the CCSID with the real one, for example 280;

    4. Select Confirm, then Confirm and continue. The entity stops with the warning that a new resync is required;

    5. Open the entity, select the play button (Start sync), choose Full copy under Start from, choose Empty the table first under Data already in the target, and select Start entity.

    Field mapping dialog with the Show all columns icon marked by annotation 1 and the Char set cells of two columns
    Figure 7. Replace the CCSID in the Char set cell of the source column.
    Expected result

    The snapshot and the changes that follow read città è più instead of citt{ } pi`. A new row with the same bytes inserted on the IBM i after the restart also arrives as città è più.

    The override applies to the whole column. Rows that were stored correctly in CCSID 37 are decoded with 280 and turn wrong, so use it only when every row of the column holds data of the CCSID you enter. The agent applies it only to CHAR and VARCHAR columns; in our test a value typed in the Char set cell of a FOR BIT DATA column had no effect. A better fix, when you can change the IBM i, is to give the column its real CCSID with ALTER TABLE … ALTER COLUMN … SET DATA TYPE … CCSID: in our test, changing a CCSID 65535 column to 37 left the stored bytes untouched.
    Remove trailing blanks (optional)

    UI

    A CHAR(12) column always holds 12 characters, and Gluesync copies them all: ABC reaches a varchar(12) column as ABC plus nine blanks. A VARCHAR keeps the blanks that were typed. If the blanks are in your way:

    1. Open the source agent with Edit source and select the Advanced settings tab;

    2. Select Add property, select the key Trim text values, and turn the switch on;

    3. Select Test and continue and finish the wizard.

    Edit source page
    Figure 8. Trim text values removes the blanks around text values.
    Expected result

    New rows read through the snapshot and through CDC arrive without blanks around the text: C37 is AB instead of AB followed by ten blanks, and a value stored as ` AB ` arrives as AB. Leading blanks are removed too, so do not use the setting when they are meaningful.

    Trim text values applies to text columns with a real CCSID and to NCHAR columns. The columns read with Translate binary are trimmed in CDC but not in the snapshot. As an alternative that keeps your data unchanged, give the column the target type char (step 2): PostgreSQL then ignores trailing blanks when it compares values, and "C12" = 'AB' is true.
    Compare the target with the IBM i, byte by byte

    SQL

    1. Start the entity with a snapshot, and insert, update or delete a few rows on the IBM i so that they also go through CDC;

    2. On the IBM i, read the bytes of the columns to check: SELECT ID, HEX(C65535), HEX(BIN4) FROM GSDOCSTYP.GS_TEXT ORDER BY ID;

    3. On the entity page, select Query in Studio under TARGET and run the same check on PostgreSQL: SELECT "ID", "C37", "C280", encode("C65535", 'hex') AS c65535_hex, encode("BIN4", 'hex') AS bin4_hex FROM ibmi_types.gs_text WHERE "ID" IN (2, 3, 6) ORDER BY 1;

    4. Compare each hex string with the one from the IBM i.

    Query Studio on the PostgreSQL target with the SQL statement marked by annotation 1 and the result grid with text and hex columns marked by annotation 2
    Figure 9. Run the check in Query Studio on the target; bytea columns are shown with encode.
    Expected result

    Every HEX() value on the IBM i equals the encode(…, 'hex') value on PostgreSQL, upper case against lower case. In our run all 23 rows of the table matched, for CHAR, VARCHAR, FOR BIT DATA, BINARY and VARBINARY columns, in the snapshot and in CDC. The CHAR(12) column tagged 65535 ends with 40 bytes (EBCDIC blanks): the IBM i pads it with them, and they are part of the value.

    For the other types, use the same pattern: compare SELECT results on both sides for the columns that the reference table below flags.

    Find out when a value is rejected and when it is rounded

    UI

    Gluesync does not compare the lengths of the source and the target columns when you pick an existing table, and neither the Error nor the Warning tab of the dialog reports a narrower target column. PostgreSQL then decides. In our test the target table was narrower than the IBM i table:

    IBM i value Target column What happened

    VARCHAR(30) value of 30 characters

    varchar(10)

    The write failed: value too long for type character varying(10). The entity went to Error and kept retrying.

    DECIMAL(9,2) value 1234567.89

    numeric(5,2)

    The write failed: numeric field overflow.

    CHAR(20) value ABC plus 17 blanks

    varchar(5)

    Written as `ABC `. PostgreSQL cuts the extra blanks without an error.

    DECIMAL(9,4) value 9.8765

    numeric(9,2)

    Written as 9.88, without any message.

    TIMESTAMP(6) value …15.123999

    timestamp(3)

    Written as …15.124, without any message.

    When a write fails, the entity page shows the error:

    1. Open the entity. The banner Target write failed names the statement and the PostgreSQL error;

    2. Select Copy error to copy it, or View notifications to see every occurrence;

    3. Widen the target column in the database, for example ALTER TABLE ibmi_types.gs_long ALTER COLUMN "S30" TYPE varchar(30). The entity does not recover by itself;

    4. On the entity page, select the pause button at the top right and confirm with Pause entity;

    5. Select the play button (Start sync), keep Where it stopped under Start from, and select Start entity. The waiting changes are written and the entity returns to Active.

    Entity page with the Target write failed banner marked by annotation 1
    Figure 10. A value that does not fit stops the entity with Target write failed.
    Expected result

    After you widen the column and start the entity, the rejected row appears on the target and the entity is Active again. Each wider column may reveal the next error: in our test the long text failed first, then the number that was too large.

    Silent rounding is not an error, so no banner or notification tells you. Compare the scale and precision of the target with the IBM i for every DECIMAL, NUMERIC, DECFLOAT and TIMESTAMP column, with Compare DDL, before the first snapshot.

    IBM i types and what they become on PostgreSQL

    The table lists the types that we tested, with the type that Gluesync proposes when it creates the table on PostgreSQL. Gluesync reads the same value in the snapshot and in CDC unless the notes say otherwise.

    IBM i type PostgreSQL type Notes

    SMALLINT, INTEGER, BIGINT

    smallint, integer, bigint

    The limits (-32768, 2147483647, -9223372036854775808) arrive unchanged.

    DECIMAL(p,s) (packed)

    numeric(p,s)

    Tested up to DECIMAL(63,30) with all digits kept. DECIMAL(9) is shown as DECIMAL (9) and becomes numeric(9,0).

    NUMERIC(p,s) (zoned)

    numeric(p,s)

    Same type as DECIMAL on the target.

    REAL, DOUBLE

    real, double precision

    3.4028235E38 and 1.7976931348623157E308 arrive unchanged.

    DECFLOAT(16), DECFLOAT(34)

    decimal without scale, which you must complete

    Confirm stays disabled until you enter Precision and Scale. The scale you enter rounds the values.

    CHAR(n), VARCHAR(n) with a CCSID such as 37, 280 or 1208

    character varying(n)

    Converted with the CCSID of the column, in the snapshot and in CDC. CHAR is padded to n. A character that the CCSID cannot hold was replaced on the IBM i when it was written and arrives as the control character U+001A (\x1A), without an error.

    CHAR(n), VARCHAR(n) with CCSID 65535, and … FOR BIT DATA

    bytea

    Bytes unchanged, byte by byte equal to HEX(). CHAR is padded with 40. See the step about CCSID 65535 for the three ways to get text.

    BINARY(n), VARBINARY(n)

    bytea

    Bytes unchanged. Translate binary does not change these.

    GRAPHIC(n), VARGRAPHIC(n) with CCSID 1200 (NCHAR, NVARCHAR)

    character varying(n)

    The source type is NATIONAL CHARACTER. Unicode text such as 日本語 Ωmega arrives unchanged. n counts characters.

    DATE

    date

    0001-01-01 and 9999-12-31 arrive unchanged.

    TIME

    time(0)

    24:00:00 arrives as 00:00:00.

    TIMESTAMP(0) to TIMESTAMP(6)

    timestamp(p)

    0001-01-01 00:00:00 and 9999-12-31 23:59:59.999999 arrive unchanged.

    TIMESTAMP(7) to TIMESTAMP(12)

    timestamp(6)

    PostgreSQL keeps six digits. The snapshot rounds and CDC truncates, so the two can differ by one microsecond, and 9999-12-31 23:59:59.999999999999 arrives as 10000-01-01 00:00:00 in the snapshot and as 9999-12-31 23:59:59.999999 in CDC.

    Not covered by this guide: CLOB, BLOB, DBCLOB, XML, DATALINK, ROWID, user-defined types and the GRAPHIC types with a double-byte CCSID other than 1200.

    What each setting changes

    Setting Where Effect

    Translate binary

    Edit source → Base settings

    Reads every FOR BIT DATA column as text, as varchar, instead of bytea. Applies to the whole agent. Snapshot and CDC can decode with different code pages.

    Trim text values

    Edit source → Advanced settings (Add property)

    Removes leading and trailing blanks from text values.

    Char set cell

    Field mapping → Show all columns

    Decodes a CHAR or VARCHAR column with a different CCSID than the one it declares. Needs a new full copy.

    Target type, Length, Precision, Scale

    Field mapping, for a table that Gluesync creates

    Sets the column of the target table. It cannot be changed for a table that already exists: change the column in the database.

    Byte Array to String with a code page

    Field mapping → fx

    Turns the bytes of a bytea-typed source into text, in the snapshot and in CDC.

    The choices above are about PostgreSQL. For another target, the target type that Gluesync proposes for text, dates and numbers changes, so use Compare DDL and the Fields Editor before the first snapshot. The IBM i agent documentation recommends a Unicode type such as NVARCHAR or NCHAR on SQL Server for text that can hold characters outside the code page (see Character encoding Q&A).
    Verify the result

    You have successfully mapped the IBM i types if:

    • Compare DDL shows the IBM i type and CCSID of each column next to a target column that can hold it;

    • Text columns show the same readable text on the IBM i and on PostgreSQL, in rows copied by the snapshot and in rows that arrived through CDC;

    • The bytea columns have the same hex value as HEX() on the IBM i;

    • The DECIMAL, DECFLOAT and TIMESTAMP columns keep every digit that you need, and the dates 0001-01-01 and 9999-12-31 arrive unchanged;

    • The entities are Active and no Target write failed banner is shown.

    Troubleshooting

    Problem Possible cause What to do

    A text column shows \x8389a3… or a long hex string on PostgreSQL

    The column is tagged CCSID 65535 (or is FOR BIT DATA), so it was mapped to bytea

    Apply Byte Array to String with the right code page in Field mapping, or retag the column on the IBM i with its real CCSID.

    Letters like à, è and £ appear as {, } and #, as in citt{ } pi`

    The bytes are in one code page and the column is tagged with another (for example 280 data in a CCSID 37 column)

    Set the real CCSID in the Char set cell, or retag the column on the IBM i, then copy the table again.

    The same column reads differently in the snapshot and in CDC

    Translate binary is on and the IBM i connection does not use CCSID 37

    Turn Translate binary off and use Byte Array to String with a code page.

    The entity stops with invalid byte sequence for encoding "UTF8": 0x00

    Translate binary is on and a FOR BIT DATA column holds real binary values

    Turn Translate binary off. Decode only the text columns with Byte Array to String.

    Confirm is disabled and the dialog says Missing required field for a DECFLOAT column

    DECFLOAT has no scale, and the target needs one

    Enter a Precision and a Scale wide enough for your values.

    Target write failed with value too long for type character varying(n)

    The target column is narrower than the IBM i column

    Widen the column in the database, then start the entity again.

    Target write failed with numeric field overflow

    The target precision is smaller than the digits of the value

    Widen the numeric column, then start the entity again.

    Numbers or seconds on the target are rounded

    The target scale or precision is smaller than the IBM i one, or the TIMESTAMP has more than six digits

    Use Compare DDL, set the scale to match, or keep six digits at most. Recreate the table and copy it again.

    Text still has blanks at the end

    CHAR is padded to its length

    Turn on Trim text values, or use the target type char.

    After editing the Char set the entity stops with a warning that a new resync is required

    Changing the mapping of a running entity invalidates the rows already copied

    Start the entity with Full copy and Empty the table first.

    Related guides

    Contact MOLO17 Support if you need assistance with IBM i data types.