Bulk load using DB2 UDB stage

harikhk · Post by **harikhk** » 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

chulett · Post by **chulett** » Thu Apr 10, 2014 1:05 pm

You posted in the Parallel forum yet marked your Job Type as 'Server'. Not sure it ultimately matters in this case but can you clarify which job type we're talking about?

harikhk · Post by **harikhk** » Thu Apr 10, 2014 1:12 pm

Sorry.. Put in the wrong category. changing to parallel

MT · Post by MT » Thu Apr 10, 2014 1:39 pm

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.

harikhk · Post by **harikhk** » Thu Apr 10, 2014 2:42 pm

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

MT · Post by MT » Thu Apr 10, 2014 11:56 pm

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

harikhk · Post by **harikhk** » Fri Apr 11, 2014 2:54 pm

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

chulett · Post by **chulett** » Fri Apr 11, 2014 3:11 pm

Does the "/log" directory exist and is it accessable to your job's user?

harikhk · Post by **harikhk** » Fri Apr 11, 2014 3:53 pm

Hi Craig,

The directory exists and the user has permissions.
The other log file is created in the same route and with teh same user.

Where should the dump file be created-On datastage server or database serve

MT · Post by MT » Sat Apr 12, 2014 10:27 am

Hi harikhk

the dump file will be created on the database server

harikhk · Post by **harikhk** » Wed Apr 16, 2014 3:18 pm

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.