Wrong date format written to DB2 on IBM AIX

[CR #538539] Using DB Data Provider Index Load and Text Data Sink components, the output date format to DB2 on IBM AIX is incorrect.

Workaround: There are two workarounds:

  1. Set the Read Block Size to 1. Be aware that reducing the read block size decreases performance.

  2. Use the CHAR function to change the query to convert the date or time column to the char datatype. For example, if col_1 is a date column, col_2 is a time column, and col_3 is a timestamp (which does not exhibit the problem), this SQL statement corrects the problem. This statement does not force the user to change the Read Block Size and avoids performance degradation associated with larger block processing:

    select CHAR(col_1), CHAR(col_2), col_3 from DATE_TIME_TBL
    

    Text Data Sink component output is:

    COL_1,COL_2,COL_3
    1963-12-08,12.00.00,1991-03-02 08:30:00.000
    1967-04-10,12.01.01,1991-04-02 08:30:00.000
    

    If you do not use the CHAR function, the Text Data Sink output is:

    COL_1,COL_2,COL_3
    1963-12-08,12:00:00,1991-03-02 08:30:00.000
    0004-10-00,01:01:00,1991-04-02 08:30:00.000
    

    To view the data written to the DB2 table, for example the DATE4_TBL table, enter:

    select * from DATE4_TBL
    

    The content in DATE4_TBL in DB2 is:

    COL_1      COL_2    COL_3
    
    ---------- -------- --------------------------
    

    12/08/1963 12:00:00 1991-03-02-08.30.00.000000
    

    04/10/1967 12:01:01 1991-04-02-08.30.00.000000