DB2 UDB Bulk Stage - Performance Issue
Moderators: chulett, rschirm, roy
DB2 UDB Bulk Stage - Performance Issue
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
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
The IBM docs state that 3107 is a warning,
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.
which doesn't help muchSQL3107W There is at least one warning message in the message file.
![Sad :(](./images/smilies/icon_sad.gif)
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.
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?
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
Regards
Siva
Listening to the Learned
"The most precious wealth is the wealth acquired by the ear Indeed, of all wealth that wealth is the crown." - Thirukural By Thiruvalluvar
Siva
Listening to the Learned
"The most precious wealth is the wealth acquired by the ear Indeed, of all wealth that wealth is the crown." - Thirukural By Thiruvalluvar
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.
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.
The Brute
-
- Charter Member
- Posts: 560
- Joined: Wed Jul 13, 2005 5:36 am
- Location: Ohio
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?
Anyone having any suggestions?
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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).
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).
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
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.ArndW wrote:The IBM docs state that 3107 is a warning,which doesn't help muchSQL3107W There is at least one warning message in the message file.Does you log file have anything else in it?
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,
Creativity is allowing yourself to make mistakes. Art is knowing which ones to keep.
-
- Charter Member
- Posts: 560
- Joined: Wed Jul 13, 2005 5:36 am
- Location: Ohio
-
- Charter Member
- Posts: 560
- Joined: Wed Jul 13, 2005 5:36 am
- Location: Ohio
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.
After making these changes. Run for maybe 100K records and see if it works. Start with a low number.
Creativity is allowing yourself to make mistakes. Art is knowing which ones to keep.
-
- Charter Member
- Posts: 560
- Joined: Wed Jul 13, 2005 5:36 am
- Location: Ohio