Hello
Are you using the generic FlexRep agent to target PostgreSQL or the specific PostgreSQL CDC engine which can be source or target? [Initially FlexRep as a target was only supported via FlexRep but in recent months a full target-capable agent was introduced in a build from IBM Fix Central].
If you are using the FlexRep agent I would imagine that failing to convert nulls to a default value like blank or zero is a limitation of the JDBC driver that the generic FlexRep agent has to use.
If you are using the native PostgreSQL agent, you will probably have a system parameter
convert_not_nullable_column
which can be set to true to convert source nulls to a default value when the target column is not nullable. However the default is true.
If the system parameter is not available (i.e. it is not currently shown in the datastore properties/system parameters and an attempt to add the parameter triggers a message that the parameter is not valid, then you can set up a value translation for the columns in question to convert nulls to spaces etc.
If you have many column to add a value translation you can use CHCCLP as in the following simple example:
connect server username user1 password password1;
connect datastore name DS1 context source;
connect datastore name DS2 context target;
select subscription name SUB1;
select table mapping sourceSchema USER1 sourceTable LOCATION;
add data translation targetColumn STATE before "<NULL>" after " ";
show data translation targetColumn STATE;
disconnect server;
exit;
When a value translation is specified for a column any source value not specified is simply applied as is.
Hope this helps
------------------------------
Robert Philo
------------------------------
Original Message:
Sent: Thu April 28, 2022 11:15 AM
From: Fred Habibi
Subject: Postgres not allowed NULL in char data column when replicating from db2 for IBM i to Postgres.
Has anyone encountered this issue? if so, how did you handle it,
This problem occurs because PostgreSQL Does not Allow null characters in char, varchar, or text As a result, when replicating from CDC for DB2 for I (AS400) to a PostgreSQL database, the operation can fail with the error below. This problem is restricted to PostgreSQL.
ERROR: invalid byte sequence for encoding "UTF8": 0x00
I thought the CDC for Postgres agent would handle this commonly known issue.
Thanks,
H.
------------------------------
Fred Habibi
------------------------------
#DataIntegration
#DataReplication