Data Integration

  • 1.  Postgres not allowed NULL in char data column when replicating from db2 for IBM i to Postgres.

    Posted Thu April 28, 2022 05:09 PM
    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


  • 2.  RE: Postgres not allowed NULL in char data column when replicating from db2 for IBM i to Postgres.

    Posted Fri April 29, 2022 12:44 AM
    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
    ------------------------------



  • 3.  RE: Postgres not allowed NULL in char data column when replicating from db2 for IBM i to Postgres.

    Posted Sat April 30, 2022 02:27 AM
    Fred

    I would have though that setting the null conversion parameter would have resolved the issue and it may be worth opening a ticket with IBM.

    I should have made it clear that you can create a list of tables and columns that need to have the value translation and then have a script read it and generate a chcclp script to do the work.

    I don't think that there is a user exit you can get off the shelf to resolve this and avoid column level transformations. However you will find sample Java code in the samples directory that may help by providing a starting point

    Thanks

    Robert

    ------------------------------
    Robert Philo
    ------------------------------



  • 4.  RE: Postgres not allowed NULL in char data column when replicating from db2 for IBM i to Postgres.

    Posted Sun June 12, 2022 05:54 PM
    Hi Robert,
    I was working on this issue for some time now, I did open a case with IBM support, and the support was great, there were a few recommendations, but none of worked, except for the following, 

    %REPLACE( column name, "*ALL", "<<0>>", "<<32>>")

    But when you have over 50 char columns in my source table, I had to using the expression for every single column. One would ask the question, should this have been done by the Postgres Agent?



    ------------------------------
    Fred Habibi
    ------------------------------



  • 5.  RE: Postgres not allowed NULL in char data column when replicating from db2 for IBM i to Postgres.

    Posted Mon June 13, 2022 12:56 AM
    Fred

    Thank you for the update.

    Since nulls values and space are not strictly equivalent, such conversions would have to be enabled globally via a parameter on the target agent. As I noted in my previous reply there may be a target datastore parameter such as convert_not_nullable_column already available to do this.

    Regards

    Robert

    ------------------------------
    Robert Philo
    ------------------------------