Bulk load using DB2 UDB stage
Moderators: chulett, rschirm, roy
Bulk load using DB2 UDB stage
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
Last edited by harikhk on Thu Apr 10, 2014 1:12 pm, edited 1 time in total.
Thanks,
HK
*Go GREEN..Save Earth*
HK
*Go GREEN..Save Earth*
Hi harikhk,
it is about how a load in DB2 works.
are you loading to DB2 z/OS or DB2 LUW?
You already talked to your DBA you wrote - which is good - and he should be able to explain a little more.
The exception table is to capture unique key violations - the use is to get the violations caught in a table to be able to process it later on. The set integrity can use this table as well.
If you want to capture invalid data (char in integer columns etc.) you have to specify a dump file - in this case the data will not fit into the provided table and therefore could not be captured in the exception table for the same reason.
it is about how a load in DB2 works.
are you loading to DB2 z/OS or DB2 LUW?
You already talked to your DBA you wrote - which is good - and he should be able to explain a little more.
The exception table is to capture unique key violations - the use is to get the violations caught in a table to be able to process it later on. The set integrity can use this table as well.
If you want to capture invalid data (char in integer columns etc.) you have to specify a dump file - in this case the data will not fit into the provided table and therefore could not be captured in the exception table for the same reason.
regards
Michael
Michael
Hi Michael,
Thanks for the clarification on 'exception table is to capture unique key violations'
How to get the error code in the exception table(Foreign key viloation, unique key violation)
Could you please explain how to create the dump file and also how to get the error code for the same
Thanks for the clarification on 'exception table is to capture unique key violations'
How to get the error code in the exception table(Foreign key viloation, unique key violation)
Could you please explain how to create the dump file and also how to get the error code for the same
Thanks,
HK
*Go GREEN..Save Earth*
HK
*Go GREEN..Save Earth*
Hi harikhk
A dump file will be created by the load if you specify the "Dump file" under "File type modifiers"
The documentation for the exeption tables can be found here
http://pic.dhe.ibm.com/infocenter/db2lu ... 01111.html
A dump file will be created by the load if you specify the "Dump file" under "File type modifiers"
The documentation for the exeption tables can be found here
http://pic.dhe.ibm.com/infocenter/db2lu ... 01111.html
regards
Michael
Michael
Hi Michael,
I have mentioned the dump file as /log/blk_dump.
This path is on the datastage unix server
The job failed with below errors
DB2_Connector_6,0: Error 9 occurred while closing the named pipe.
DB2_Connector_6,0: The DB2 Load API call failed with SQLCODE -2,036.
Load messages are stored in message file:/log/BLK_LD_LOG.txt.
DB2_Connector_6,0: Error 32 occurred while writing to the named pipe. (CC_DB2APILoadRecordDataSetConsumer::putRowBufferInCache, file CC_DB2APILoadRecordDataSetConsumer.cpp
The content of the log file /log/BLK_LD_LOG.txt is
SQL2036N The path for the file or device
"/log/blk_dump.load.000" is not valid.
I have eliminated the dump file name and mentioned only the path but the output is the same.
Please help me in fixing
I have mentioned the dump file as /log/blk_dump.
This path is on the datastage unix server
The job failed with below errors
DB2_Connector_6,0: Error 9 occurred while closing the named pipe.
DB2_Connector_6,0: The DB2 Load API call failed with SQLCODE -2,036.
Load messages are stored in message file:/log/BLK_LD_LOG.txt.
DB2_Connector_6,0: Error 32 occurred while writing to the named pipe. (CC_DB2APILoadRecordDataSetConsumer::putRowBufferInCache, file CC_DB2APILoadRecordDataSetConsumer.cpp
The content of the log file /log/BLK_LD_LOG.txt is
SQL2036N The path for the file or device
"/log/blk_dump.load.000" is not valid.
I have eliminated the dump file name and mentioned only the path but the output is the same.
Please help me in fixing
Thanks,
HK
*Go GREEN..Save Earth*
HK
*Go GREEN..Save Earth*
HI,
Thanks for the response.
I am able to create the dump file in the database server.
I still wonder inorder to capture all the rejects, I have to go through exceptions table and also dump file and is there a way to capture all in once place
Thanks a lot for the help.
Thanks for the response.
I am able to create the dump file in the database server.
I still wonder inorder to capture all the rejects, I have to go through exceptions table and also dump file and is there a way to capture all in once place
Thanks a lot for the help.
Thanks,
HK
*Go GREEN..Save Earth*
HK
*Go GREEN..Save Earth*