Topic
IC4NOTICE: developerWorks Community will be offline May 29-30, 2015 while we upgrade to the latest version of IBM Connections. For more information, read our upgrade FAQ.
1 reply Latest Post - ‏2011-10-20T18:21:10Z by SystemAdmin
EKG0_Tyler_Kisling
EKG0_Tyler_Kisling
4 Posts
ACCEPTED ANSWER

Pinned topic SQL3125W - truncate error - Oracle to DB2 - has anyone seen this before?

‏2011-08-18T15:32:36Z |
I have encountered an issue where I am getting a SQL3125W error when loading Oracle data to DB2. We are seeing this when loading varchar2 fields from Oracle to DB2. For example, in one case the field was a varchar2(30) field in Oracle and the same in DB2. Why would this happen when the data types and definitions are the same? Has anyone ever encountered this? Is there somehow a difference in the way Oracle and DB2 store data that could lead to this?

There are no records rejected. We couldn't find any data missing, although it was difficult to troubleshoot as we couldn't clearly identify which row was failing. Any feedback would be much appreciated.
Here is an example of what I am seeing:

SQL3501W The table space(s) in which the table resides will not be placed in backup pending state since forward recovery is disabled for the database.

SQL3039W The memory available to LOAD for DATA BUFFER prohibits full LOAD parallelism. Load parallelism of "3" will be used

SQL3109N The utility is beginning to load data from file "/db2/db2poc/Desktop/new/IBMTEST_RUN3/data/ibmtest_esst_materialized_de".

SQL3500W The utility is beginning the "LOAD" phase at time "07/20/2011 16:35:18.750987".

SQL3519W Begin Load Consistency Point. Input record count = "0".

SQL3520W Load Consistency Point was successful.

SQL3125W The character data in row "F2-40" and column "2" was truncated because the data is longer than the target database column.

SQL3110N The utility has completed processing. "9433" rows were read from the input file.

SQL3519W Begin Load Consistency Point. Input record count = "9433".

SQL3520W Load Consistency Point was successful.

SQL3515W The utility has finished the "LOAD" phase at time "07/20/2011 16:35:18.890360".

SQL3107W There is at least one warning message in the message file.

Number of rows read = 9433
Number of rows skipped = 0
Number of rows loaded = 9433
Number of rows rejected = 0
Number of rows deleted = 0
Number of rows committed = 9433
Updated on 2011-10-20T18:21:10Z at 2011-10-20T18:21:10Z by SystemAdmin
  • SystemAdmin
    SystemAdmin
    708 Posts
    ACCEPTED ANSWER

    Re: SQL3125W - truncate error - Oracle to DB2 - has anyone seen this before?

    ‏2011-10-20T18:21:10Z  in response to EKG0_Tyler_Kisling
    Yes, we have had the same problem some time ago. In our case we have found this:
    *db2 describe select '*' from cp.tr_errores*

    Información de columnas

    Número de columnas: 18

    Tipo SQL Long. tipo Nombre columna Long nombre




    492 BIGINT 8 ID_ERROR_FIVA 13
    449 VARCHAR 14 NUM_POLIZA 10
    449 VARCHAR 2 TIPOMATR 8
    449 VARCHAR 12 MATRICULA 9
    449 VARCHAR 9 MARCFIVA 8
    449 VARCHAR 1 TIPO_ACC 8
    393 TIMESTAMP 19 FEC_EFECTO_FIVA 15
    393 TIMESTAMP 19 FEC_FVTO_FIVA 13
    449 VARCHAR 1 TIPO_CONT_FIVA 14
    493 BIGINT 8 LOTE_FIVA 9
    449 VARCHAR 10 MENSAJE 7
    393 TIMESTAMP 19 FEC_RECEP_FIVA 14
    501 SMALLINT 2 SW_ENVIO 8
    493 BIGINT 8 CTL_USUARIO 11
    501 SMALLINT 2 CTL_ESTADO 10
    393 TIMESTAMP 19 CTL_FEC_ALTA 12
    393 TIMESTAMP 19 CTL_FEC_MODI 12
    449 VARCHAR 16 CTL_IP 6
    1~"SP00000025"~~"9525GFT""SEAT""A"~9-03-02 00:00:00~2011-03-02 00:00:00~"C"1"24"~2009-02-18 00:00:00~1~1~~~2011-04-13 15:10:34*.0*~
    4~"2000000213"" ""8677FFG""OPEL""A"~2010-09-01 00:00:00~2011-09-01 00:00:00~"C"~~~~1~1~1~2010-09-16 10:46:48*.0*2011-04-13 15:10:34*.0*
    3~"5000000192"" ""2333GGH""PRIM""A"~2010-06-16 00:00:00~2011-06-16 00:00:00~"C"~~~~1~1~1~2010-09-10 12:19:05*.0*2011-04-13 15:10:34*.0*
    2~"SP00000025""C""2206FHH""FIAT""A"~2009-03-02 00:00:00~2009-03-03 00:00:00~"C"~~~~0~1~1~2009-10-30 13:53:51*.0*2011-04-13 15:10:34*.0*

    If we take a look at /logs/db2loadmessages.log we find:

    =================================================================
    Messages start for cp.tr_errores
    =================================================================
    SQL3501W The table space(s) in which the table resides will not be placed in backup pending state since forward recovery is disabled for the database.
    SQL3109N The utility is beginning to load data from file
    "/sw/IBM_DATA_MOV_TOOL/migrtest/data/cp_tr_errores.txt".
    SQL3500W The utility is beginning the "LOAD" phase at time "06/08/2011 21:45:35.488409".
    SQL3519W Begin Load Consistency Point. Input record count = "0".
    SQL3520W Load Consistency Point was successful.
    SQL3125W The character data in row "F0-1" and column "17" was truncated because the data is longer than the target database column.
    SQL0180N The syntax of the string representation of a datetime value is incorrect. SQLSTATE=22007
    SQL3185W The previous error occurred while processing data from row "F0-1" of the input file.

    SQL3125W The character data in row "F0-2" and column "16" was truncated because the data is longer than the target database column.
    SQL3125W The character data in row "F0-2" and column "17" was truncated because the data is longer than the target database column.
    SQL3125W The character data in row "F0-3" and column "16" was truncated because the data is longer than the target database column.
    SQL3125W The character data in row "F0-3" and column "17" was truncated because the data is longer than the target database column.
    SQL3125W The character data in row "F0-4" and column "16" was truncated because the data is longer than the target database column.
    SQL3125W The character data in row "F0-4" and column "17" was truncated because the data is longer than the target database column.
    SQL3110N The utility has completed processing. "4" rows were read from the input file.
    SQL3519W Begin Load Consistency Point. Input record count = "4".
    SQL3520W Load Consistency Point was successful.
    SQL3515W The utility has finished the "LOAD" phase at time "06/08/2011 21:45:35.629939".
    SQL3107W There is at least one warning message in the message file.

    As you can see the error refers to columns 16 and 17 (CTL_FEC_ALTA and CTL_FEC_MODI). Surprisingly there are other timestamp fields containing correct data. Only columns 16 and 17 are affected.

    The tool seems to be adding a .0 to the timestamp data in these two fields. It would be interesting to query the original Oracle table.