DB2 Load row limit

Daddy Doma · Post by **Daddy Doma** » Mon Jul 11, 2005 6:46 pm

G'day,

I am trying to load a DB2 database with a volume of rows in the order of 7.5 million.

When using DB2 'load' (as opposed to 'write') not all my rows are being inserted into the database. From what I can tell, only the first 70000 rows (exactly) are loaded. DataStage statistics, and the Director log, indicate that all 7.5 million rows have been passed to the stage.

Note: When using 'write' to insert rows there is no problem. However the job runs too slowly for our needs.

Has anyone encountered this problem using DB2 load and what was your final solution?

Zac.

ray.wurlod · Post by **ray.wurlod** » Mon Jul 11, 2005 7:16 pm

Is there some limiting setting for the DB2 bulk loader? DataStage has sent all the rows; one can only assume that there's something in DB2 blocking them. Ask your DBA to check.

DSguru2B · Post by **DSguru2B** » Mon Jul 11, 2005 9:43 pm

Hi,
Try using Db2 Enterprise stage instead of Db2 bulk load stage. I have been through this problem and for some reason the Db2 EE stage worked fine with the load option.

Daddy Doma · Post by **Daddy Doma** » Mon Jul 11, 2005 10:47 pm

We are already using the DB2 Enterprise stage. There were previous problems on this project with DB2 Bulk Load.

I think Ray may be on the right track, and this is likely a DB2 restriction instead of DS. Relevant DBA is being hunted as we speak...

ArndW · Post by **ArndW** » Tue Jul 12, 2005 1:45 am

Doma,

this might be the equivalent of the rollback segment filling up. What is your commit frequency for this job? If you check the generated sequential data file(s) are they at a logical limitation size such as 2Gb (perhaps the DB/2 read program uses only a small pointer).