DB2 Bulk Load - log messages - warnings not processed
Posted: Fri May 16, 2008 9:40 am
We are using the IBM DB2 UDB Bulk Loader stage and it works OK, but when there are messages in the log which should be warnings or fatal errors they are not handled correctly.
Has anyone written code to inspect the logs? Or is there a better way to handle this?
Here is my latest example: We are loading to a partitioned table (and I know we should use the Enterprise stage but we don't have it working yet)
and every single row is rejected.
The DataStage log shows only this warning:
HEALTH_PLAN_PAYMENT_FACT,0: Warning: Rebate_HlthPlnPymnt_ds_to_HPP_FACT_bulk_ins.HEALTH_PLAN_PAYMENT_FACT: SQL3107W There is at least one warning message in the message file.
but this is actually due to another warning in the log which is unimportant.
Here is the log:
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 "1" will be used
SQL1128W SAVECOUNT is ignored. Reason code = "1".
SQL3109N The utility is beginning to load data from file
"/tmp/udbpipe06Ij7a".
SQL3500W The utility is beginning the "LOAD" phase at time "05/15/2008
19:22:26.044906".
SQL3535W The LOAD command parameter "USING" is no longer supported; its value
will be ignored by the LOAD utility.
SQL3519W Begin Load Consistency Point. Input record count = "0".
SQL3520W Load Consistency Point was successful.
SQL0327N The row cannot be inserted into table "DBO.HEALTH_PLAN_PAYMENT_FACT"
because it is outside the bounds of the defined data partition ranges.
SQLSTATE=22525
*** this is the INFORMATIONAL message that should have been FATAL
SQL3110N The utility has completed processing. "28984227" rows were read
from the input file.
SQL3519W Begin Load Consistency Point. Input record count = "28984227".
SQL3520W Load Consistency Point was successful.
SQL3515W The utility has finished the "LOAD" phase at time "05/15/2008
21:58:58.765546".
SQL3500W The utility is beginning the "BUILD" phase at time "05/15/2008
21:58:58.768645".
SQL3213I The indexing mode is "INCREMENTAL".
SQL3515W The utility has finished the "BUILD" phase at time "05/15/2008
21:59:00.507812".
SQL3107W There is at least one warning message in the message file.
Number of rows read = 28984227
Number of rows skipped = 0
Number of rows loaded = 0
Number of rows rejected = 28984227
Number of rows deleted = 0
Number of rows committed = 28984227
** The reject rows above is the other big indication of a problem!
Has anyone written code to inspect the logs? Or is there a better way to handle this?
Here is my latest example: We are loading to a partitioned table (and I know we should use the Enterprise stage but we don't have it working yet)
and every single row is rejected.
The DataStage log shows only this warning:
HEALTH_PLAN_PAYMENT_FACT,0: Warning: Rebate_HlthPlnPymnt_ds_to_HPP_FACT_bulk_ins.HEALTH_PLAN_PAYMENT_FACT: SQL3107W There is at least one warning message in the message file.
but this is actually due to another warning in the log which is unimportant.
Here is the log:
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 "1" will be used
SQL1128W SAVECOUNT is ignored. Reason code = "1".
SQL3109N The utility is beginning to load data from file
"/tmp/udbpipe06Ij7a".
SQL3500W The utility is beginning the "LOAD" phase at time "05/15/2008
19:22:26.044906".
SQL3535W The LOAD command parameter "USING" is no longer supported; its value
will be ignored by the LOAD utility.
SQL3519W Begin Load Consistency Point. Input record count = "0".
SQL3520W Load Consistency Point was successful.
SQL0327N The row cannot be inserted into table "DBO.HEALTH_PLAN_PAYMENT_FACT"
because it is outside the bounds of the defined data partition ranges.
SQLSTATE=22525
*** this is the INFORMATIONAL message that should have been FATAL
SQL3110N The utility has completed processing. "28984227" rows were read
from the input file.
SQL3519W Begin Load Consistency Point. Input record count = "28984227".
SQL3520W Load Consistency Point was successful.
SQL3515W The utility has finished the "LOAD" phase at time "05/15/2008
21:58:58.765546".
SQL3500W The utility is beginning the "BUILD" phase at time "05/15/2008
21:58:58.768645".
SQL3213I The indexing mode is "INCREMENTAL".
SQL3515W The utility has finished the "BUILD" phase at time "05/15/2008
21:59:00.507812".
SQL3107W There is at least one warning message in the message file.
Number of rows read = 28984227
Number of rows skipped = 0
Number of rows loaded = 0
Number of rows rejected = 28984227
Number of rows deleted = 0
Number of rows committed = 28984227
** The reject rows above is the other big indication of a problem!