Hi we are using DS 8.1
We able to execute bulk load using DB2 Connector only for less volume of records.
It breaks when volume is huge.
Bulk load also takes more time compared to Normal insert. I have set Non recoverable load to YES.
Normal insert: 189K records: 3:44 mins
Bulk load: 80K records: 1:13 hrs
Bulk load: 189K records: Aborted
Below are the error messages we got
dbeW_Sales_Cost_Planning,0: [IIS-CONN-DB2-000314] The DB2 Load API call failed with SQLCODE -30,081.
dbeW_Sales_Cost_Planning,0: [IIS-CONN-DB2-000299] An error occurred in the thread for loading data. (CC_DB2APILoadRecordDataSetConsumer::processOneTopLevelDataItem, file CC_DB2APILoadRecordDataSetConsumer.cpp, line 1,881)
I was working with bulk load for full volume in dev and was failing repeatedly In development environment the jobs are aborting for huge volume which the normal insert is able to handle effectively. Ideally bulk load should be much faster if it is preferred load method. Can any of you help me in understanding why DB2 connector stage with bulk load fails for huge volume?
Bulk Load Option for DB2 Connector Stage
Moderators: chulett, rschirm, roy
-
chandra.shekhar@tcs.com
- Premium Member
- Posts: 353
- Joined: Mon Jan 17, 2011 5:03 am
- Location: Mumbai, India
1. FYI, you can also use DB2 connector for bulk loading.
2. In bulk load, before inserting the records, the bulk load writes a log file for the ongoing processes in the job. If you are having some warnings in your job then for each row and for each column it will write that log into the file. that is why it takes time. Try to reduce the number of warnings in your job.
3. the error code you mentioned has somethind to do with connection, that is for sure.
SQL30081N: A communication error has been detected. Communication protocol being used: protocol. Communication API being used: interface. Location where the error was detected: location. Communication function detecting the error: function. Protocol specific error code(s): rc1, rc2, rc3.
2. In bulk load, before inserting the records, the bulk load writes a log file for the ongoing processes in the job. If you are having some warnings in your job then for each row and for each column it will write that log into the file. that is why it takes time. Try to reduce the number of warnings in your job.
3. the error code you mentioned has somethind to do with connection, that is for sure.
SQL30081N: A communication error has been detected. Communication protocol being used: protocol. Communication API being used: interface. Location where the error was detected: location. Communication function detecting the error: function. Protocol specific error code(s): rc1, rc2, rc3.
Thanx and Regards,
ETL User
ETL User
Issue Resolved.
The job took time due to CC_MSG_LEVEL being enabled at project level.
This generated huge logs in director and job was crashing for huge volume.
Disbaled the env variable CC_MSG_LEVEL and Bulk load went on fly.
Points to be noted for Bulk load:
1. Table should be partitioned
2. Non recoverable load set to YES to prevent locking of tablespaces
3. Exception table should be created similar to target table with additional 2 fields (TS TIMESTAMP, EXCEPT_MSG CLOB(32K))
The job took time due to CC_MSG_LEVEL being enabled at project level.
This generated huge logs in director and job was crashing for huge volume.
Disbaled the env variable CC_MSG_LEVEL and Bulk load went on fly.
Points to be noted for Bulk load:
1. Table should be partitioned
2. Non recoverable load set to YES to prevent locking of tablespaces
3. Exception table should be created similar to target table with additional 2 fields (TS TIMESTAMP, EXCEPT_MSG CLOB(32K))