Bulk load using DB2 UDB stage
Posted: Thu Apr 10, 2014 11:52 am
Hi,
Stuck with bulk load features in DB2 Connector stage.
After multiple errors left with queries.
Below I am trying to explain my problem with maximum details.
Your expert help needed resolve the problem
Using bulk load facility to load massive data for faster load and to avoid creating of archive logs.
There could be violation of constraints (foreign key or not null) when loading the data.
Observed that it is not possible to have a rejects file.
On googling I have found to define an exception table and applied same in DB2 conenctor stage.
But just by defining an exception table: Below is qhat is observed
1. Data loaded into the target table but no exceptions caught.
2. When executed the job again, it failed with the error SQL0668N Operation not allowed for reason code "1" on table "TBL_BEING_LOADED".
3.To resolve this error followed the instructions mentioned in the below page http://publib.boulder.ibm.com/infocente ... reason.htm (set integrity for table_name immediate checked)
by recreating the table and foreign key definition and writing the set integrity command in after query option od the connector properties.
4.When executed with the set integrity in after query, below is the error
SQL3603N Integrity processing through the SET INTEGRITY statement has found an integrity violation involving a constraint, a unique index, a generated column, or an index over an XML column. The associated object is identified by "TBL_BEING_LOADED.FK_CONSTRAINT". SQLSTATE=23514
5.So with the help of my DBA after recreating the table and defining foreign key constraint, changed the after query as
set integrity for TBL_BEING_LOADED immediate checked for exception in TBL_BEING_LOADED use TBL_BEING_LOADED_rej ;
6.The data has been loaded as expected and even the rejects were captured in the exception table.
Observations:
1.Only the foreign key constarints were captured and not null constarints were not captured
2.The exception table is in append mode
3.There is no significance by defining the exception table in the property. Have to explicitly mention in the set integrity statment
Please help me how to capture those records rejected due to not null constraint or any other with the reason code which is possible with insert mode and writing to a reject link in DB2 connector stage
Stuck with bulk load features in DB2 Connector stage.
After multiple errors left with queries.
Below I am trying to explain my problem with maximum details.
Your expert help needed resolve the problem
Using bulk load facility to load massive data for faster load and to avoid creating of archive logs.
There could be violation of constraints (foreign key or not null) when loading the data.
Observed that it is not possible to have a rejects file.
On googling I have found to define an exception table and applied same in DB2 conenctor stage.
But just by defining an exception table: Below is qhat is observed
1. Data loaded into the target table but no exceptions caught.
2. When executed the job again, it failed with the error SQL0668N Operation not allowed for reason code "1" on table "TBL_BEING_LOADED".
3.To resolve this error followed the instructions mentioned in the below page http://publib.boulder.ibm.com/infocente ... reason.htm (set integrity for table_name immediate checked)
by recreating the table and foreign key definition and writing the set integrity command in after query option od the connector properties.
4.When executed with the set integrity in after query, below is the error
SQL3603N Integrity processing through the SET INTEGRITY statement has found an integrity violation involving a constraint, a unique index, a generated column, or an index over an XML column. The associated object is identified by "TBL_BEING_LOADED.FK_CONSTRAINT". SQLSTATE=23514
5.So with the help of my DBA after recreating the table and defining foreign key constraint, changed the after query as
set integrity for TBL_BEING_LOADED immediate checked for exception in TBL_BEING_LOADED use TBL_BEING_LOADED_rej ;
6.The data has been loaded as expected and even the rejects were captured in the exception table.
Observations:
1.Only the foreign key constarints were captured and not null constarints were not captured
2.The exception table is in append mode
3.There is no significance by defining the exception table in the property. Have to explicitly mention in the set integrity statment
Please help me how to capture those records rejected due to not null constraint or any other with the reason code which is possible with insert mode and writing to a reject link in DB2 connector stage