DB2 UDB Bulk Stage - Performance Issue

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

Are you doing INSERT or REPLACE?
Try with an even smaller count. Like say maybe just 10K records.
Also when the job gets hung. How do you kill the job?
DB2 is a real pain. Stopping the job by director will still leave an alive thread to the database.
Do this, call you DBA, ask him how many threads are open from you. Ask him to kill all those threads. Test your job again with 10K records. If the job hangs again, dont stop it from the director, ask your DBA to kill that thread. This way your job will finish.
What is your job design?
The best design, IMHO, is a sequential file going straight to the load stage.
If it still doesnt work, get your DBA involved. Fire your job and ask the DBA to monitor what is your thread doing at the database level.
Also look at the message file, it has precious info that helps in debugging and getting the load utility to work.

ArndW · Post by **ArndW** » Mon Jun 12, 2006 11:18 am

One of the questions that was asked that you haven't answered is what speed are you getting when you take the DB/2 bulk load out of the equation and just write to a sequential file.

You have been given lots of suggestions from various people and all you've stated is "not working" or "am still stuck". You will need to specify what you've tried and what the results (if any) were.

I'll speak for myself but perhaps the comments will apply to other posters as well. The communication is going in only one direction and it is not worth extra effort to keep on trying to get information from you in order to try to narrow down the problem.

How fast can you bulk load into DB/2 from files you create yourself outside of DataStage?

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

i set the save count to 10000 and now the speed reduces after it reaches 10000. it reduces to 10 and then another 10000 rows are laoded.

DSguru2B · Post by **DSguru2B** » Mon Jun 12, 2006 12:36 pm

ArndW is right. Try to perform all the tests outlined by other posters. Also, dont forget to tell us what do you see in the message file other than the commit info.

Krazykoolrohit · Post by **Krazykoolrohit** » Mon Jun 12, 2006 12:55 pm

Hi am sorry for no details from my side.

I just changed two parameters Load intermediate to NO and Load from client to NO and the jobs running fine now. Please let me know if i messed up something.

Maybe there were threads hanging in DB2 which got killed by admin (i didnt request him to). Please let me know if you all need any more information.

DSguru2B · Post by **DSguru2B** » Mon Jun 12, 2006 1:01 pm

O no. The job is running fine but did you actually take a look at the table whether the records got loaded ? I seriously doubt it.
Load immediate (not intermediate) setting to NO means the data gets copied to INPUTDATA.DAT for delayed loading. Then the bat file is used to load the data.
Setting the Load From CLient to NO means that the load cannot take place from remote client. I bet your datastage server and DB2 server are on different platforms.
Setting those two options to NO will not make your bulk loader work.

Krazykoolrohit · Post by **Krazykoolrohit** » Mon Jun 12, 2006 1:49 pm

Thanx a lot DSguru2B and others.

I guess you are right. I have raised a ticket and trying to get that information you requested (weather the data is getting loaded or not).I should have it in some time.

But what i noticed is that changing 'Intermediate files' to NO solves the issue and i didnt had to change 'Load From Client' value.

So if i keep 'Load From Client' as YES and 'Intermediate files' as 'NO' and run the job. Is this fine? (its job is working fine with this setting)

DSguru2B · Post by **DSguru2B** » Mon Jun 12, 2006 1:55 pm

Krazykoolrohit wrote:
But what i noticed is that changing 'Intermediate files'

What option is that. The only option i see over here with the word 'Intermediate' is 'Remove Intermediate Datafile'. That option is to remove the INPUTDATA.DAT file that it creates once the load is done.

Whether the job works or not, that is upto you to decide. If it finishes successfully, without hanging up and the data gets loaded, and your DBA doesnt scream at you after a few days then you should be fine.

Krazykoolrohit · Post by **Krazykoolrohit** » Mon Jun 12, 2006 2:11 pm

Its "Load Intermediate" am sorry for the typo.

well fine then i will wait for the DBA to shout if he did.

thanx a lot.

DSguru2B · Post by **DSguru2B** » Mon Jun 12, 2006 2:23 pm

Krazykoolrohit wrote:Its "Load Intermediate" am sorry for the typo.

Again a typo. Its Load Immediate and not Intermediate. You need to rest buddy. Tomorrow's another day

Why your job finishes successfully when Load Immediate is set to No is because it doesnt load. It actually just builds the INPUTDATA.DAT file, the CMD.CLP file (command file) and the ULOAD.BAT (batch file) so that the load can be done later on by firing the batch file.

bcarlson · Post by **bcarlson** » Mon Jun 12, 2006 2:33 pm

Bryceson wrote: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

I think moving it to PX would be a great idea. we are using PX against DB2 UDB v8.2.x (a 48-node system). We have jobs that load between 15 and 30+ million records in 3-15 minutes (depending on system load). If you do move it, make sure you use the DB2 Enterprise Stage - better performance than the DB2 API stage.

Whether you move it to PX or not, consider loading your data with no indexes. Especially if you are replacing the data. Loading data with indexes present slows it down. Building the index at the end is usually pretty fast.

Brad.

Krazykoolrohit · Post by **Krazykoolrohit** » Mon Jun 12, 2006 2:46 pm

So you want me to drop the index and then recreate it after load? I know this is a good idea but i dont have the privilages to.

Also we may change to PX but we need to resolve this first before our audit:-)

thanx a lot for the suggestion anyways.

DSguru2B · Post by **DSguru2B** » Mon Jun 12, 2006 2:49 pm

AHA. The answer to your problem might be right there.
How the load works is that it drops the indices and rebuilds them. If you dont have privilege to do that then there is a good possiblilty that your job hangs. They really dont seem to match but ask your DBA to give you that privilege for just a trial run.
Did you look at the message file that it creates. All this info will be present there.

Krazykoolrohit · Post by **Krazykoolrohit** » Mon Jun 12, 2006 3:09 pm

My DBA has gone home. So i guess i would go too. I will catch him tommorow morning and check with the index permissions and stuff. I think this may be the issue as my jobs are indeed hanging not in DS manager but are locking the table forever.

I am not able to see the messages as the file is too large to open. i tried renaming it but it seems that the file has some exclusive locks on it. I guess my job is the culprit and even if it shows a finished status, its locking the table and the file.

thanx anyways for all the support. will get back to ya tommorow.

Krazykoolrohit · Post by **Krazykoolrohit** » Tue Jun 13, 2006 11:42 am

My DBA says that the id has create drop access to the indexes. Any other suggestions.