DB2 UDB Bulk Stage - Performance Issue

Bryceson · Post by **Bryceson** » Tue Feb 07, 2006 3:32 pm

Hi all,

I have a job that is reading complex flat file (ASCII format) about 13+ millions rows and loading them to a DB2 Table using DB2 Bulk Stage. It takes forever to load and I also get this warning message

"LOADPSTREELEDGERSTAGE..TargetPSLedgerStage: ERROR occurred : loading table, SQLCODE 3107"

Has any one run into something like this before?? How could I speed up the loading. Any input/suggestions and knowledge will be appreciated.

Thanks in Advance,

Bryceson

ArndW · Post by **ArndW** » Tue Feb 07, 2006 3:42 pm

The IBM docs state that 3107 is a warning,

SQL3107W There is at least one warning message in the message file.

which doesn't help much

Does you log file have anything else in it?

If you were to partition your table you would get some really significant speed increases on PX - but that might not be possible in your case. The bulk loader should work quite fast, do you have other tables that load fast compared to this one, or is it symptomatic of your DB/2 load in general?

I've seen PX DB/2 loads with speeds 60k rows/second. Going to a partitioned table with no triggers/indices.

Bryceson · Post by **Bryceson** » Tue Feb 07, 2006 3:52 pm

ArndW,

Nothing significant in the log. The table I am loading has one index on the indentity column. This is the only table that I am loading that much data. Any other suggestion?? PX could be an option going forward if this doesn't get improvement.

Thanks,

Bryceson

ArndW · Post by **ArndW** » Tue Feb 07, 2006 3:57 pm

Ummm - this was posted in the PX forum - does that mean this is a server job?

What rows/second speeds are you getting in the load (and just as a crosscheck how many rows/seconds does your CFF stage output if you redirect it to /dev/null?

Bryceson · Post by **Bryceson** » Tue Feb 07, 2006 4:03 pm

ArndW,

Yes, This is a Server job!! Last time I ran this job with 100,000 rows Limit it took 15min/14sec, by the time it was done (110 rows/sec) the speed keep on going dwon.

Thanks . . . Bryceson

ArndW · Post by **ArndW** » Tue Feb 07, 2006 4:43 pm

Have you runstated (is that a verb?) the table? Could you check the speed of your CFF stage when it doesn't write to anything by putting in a transform with a "1=2" consraint? It should write many thousands of rows per second. What bulk load options have you specified for the DB/2 load? Approximately how many bytes is your data - so you can estimate a Mb/Min load rate?

rasi · Post by **rasi** » Tue Feb 07, 2006 7:16 pm

Runstat before load and after load will help. How many columns does your target table has?. Can you change your design to split 13 Million records into small subset and run parallel this will cut down the time

tardifma · Post by **tardifma** » Thu Feb 09, 2006 2:11 pm

Hi.
Maybe you could check the buffer pool size in DB2. I had a similar problem before, everything on the PX side was fine, but it seemed that DB2 was unable to take more than 10 000 rows/sec... which was very bad... Then we increased the Buffer pool size and the performance increased significantly (more than 60000 rows/sec)...

Hope this will help.

Krazykoolrohit · Post by **Krazykoolrohit** » Fri Jun 09, 2006 3:56 pm

Even i am facing the same issue. i have only 6000 rows and they get transfered with a speed of 2000 rows per sec in 1-2 minutes. after that the speed of the job keeps on decreasing till it reaches 11 rows per second. and the job takes 2 hours to decrease its speed and finish.

Anyone having any suggestions?

ray.wurlod · Post by **ray.wurlod** » Fri Jun 09, 2006 5:03 pm

Generally a fast initial rate followed by degradation indicates that some kind of resource (buffer, for example) becomes full and then a slower process becomes involved (for instance transfer with handshaking rather than direct transfer).

It might also mean that row processing has finished but the clock is still running (for example because an after-job subroutine is still executing, or a memory buffer is being flushed to disk).

DSguru2B · Post by **DSguru2B** » Fri Jun 09, 2006 9:58 pm

ArndW wrote:The IBM docs state that 3107 is a warning,
SQL3107W There is at least one warning message in the message file.
which doesn't help much Does you log file have anything else in it?

The warning message will not be present inyour director log. It will be present in the message file that the bulk loader creates. In the properties, there is a place where you have to give the path of the message file. Look into that message file what error message is it giving.
I used to get this kind of behaviour in which the job would blaze thousands of records and then the numbers would stop (all the records transfered) and the speed would start decreasing. The job would seem to hang. I spoke to my DBA about it and he asked me to specify the commit level to every 10k. I did that and it worked.
Also your incoming data should be sorted on the keys if it has index on it. That really helps.
So try a couple of things,
1) Specify the commit size to every 10k
2) Sort the incoming data on the key
3) Keep checking the message file that it creates, thats the file that will tell you whats going on with the bulk loader.
Regards,

Krazykoolrohit · Post by **Krazykoolrohit** » Sat Jun 10, 2006 2:13 pm

Hi,

Two questions. Where can i find commit in server job Bulk load stage? and where do the bulk logs get created.

The solutions yet not working for me. I have my data sorted on the keys.

Krazykoolrohit · Post by **Krazykoolrohit** » Mon Jun 12, 2006 10:08 am

am still stuck

DSguru2B · Post by **DSguru2B** » Mon Jun 12, 2006 10:11 am

The commit size is provided in the property 'Save Count'. And the property where you have to give the fully qualified name of the message file is 'Local Message File Name'. Set indexing mode to AUTO_SELECT.

After making these changes. Run for maybe 100K records and see if it works. Start with a low number.

Krazykoolrohit · Post by **Krazykoolrohit** » Mon Jun 12, 2006 11:10 am

not working:-(