DB2 UDB Bulk Stage - Performance Issue

DSguru2B · Post by **DSguru2B** » Tue Jun 13, 2006 11:45 am

DSguru2B wrote: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.

Did you try that

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

ya the message file is locked up by some job. and its not opening (its size is huge) I will try opening the file and contact DBA now. will post out the detailed result.

DSguru2B · Post by **DSguru2B** » Tue Jun 13, 2006 12:49 pm

Delete the message file. Or in the properties of the bulk loader, give a new file name so that it creates a new file. Run your job for just 5 records so that the message file remains small and we can see the key messages in it.

Krazykoolrohit · Post by **Krazykoolrohit** » Wed Jun 14, 2006 7:12 am

Hi everyone,

This is the snapshot of the bulk load log file. These are the lines from the begining and the rest of the log is basically a repetetion of the same error messages for different records.

Is the delay because of many warnings? but then the speed of the load should be low and it should not show a green link.

SQL3501W The table space(s) in which the table resides will not be placed in
backup pending state since forward recovery is disabled for the database.

SQL3109N The utility is beginning to load data from file
"/Ascential/Datastage/Projects/UNIFI/Data/seq1/inpdata.dat".

SQL3500W The utility is beginning the "LOAD" phase at time "06-13-2006
20:38:03.141108".

SQL3519W Begin Load Consistency Point. Input record count = "0".

SQL3520W Load Consistency Point was successful.

SQL3137W Row "F0-1" is too short. At least one input value being loaded to a
non-nullable column is missing. The row was not loaded.

SQL3185W The previous error occurred while processing data from row "F0-1" of
the input file.

SQL3137W Row "F1-1" is too short. At least one input value being loaded to a
non-nullable column is missing. The row was not loaded.

SQL3116W The field value in row "F0-2" and column "1" is missing, but the
target column is not nullable.

SQL3185W The previous error occurred while processing data from row "F1-1" of
the input file.

ArndW · Post by **ArndW** » Wed Jun 14, 2006 7:14 am

The delay is because of all the warnings and of course your data isn't being loaded either. Fix your data and the job will run much faster.

Krazykoolrohit · Post by **Krazykoolrohit** » Wed Jun 14, 2006 7:14 am

This is the ending:
SQL3227W Record token "F0-111993" refers to user record number "223523".

SQL3110N The utility has completed processing. "223523" rows were read from
the input file.

SQL3519W Begin Load Consistency Point. Input record count = "223523".

SQL3520W Load Consistency Point was successful.

SQL3515W The utility has finished the "LOAD" phase at time "06-14-2006
00:13:54.781999".

SQL3500W The utility is beginning the "BUILD" phase at time "06-14-2006
00:13:54.842354".

SQL3213I The indexing mode is "REBUILD".

SQL3515W The utility has finished the "BUILD" phase at time "06-14-2006
00:13:55.053210".

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

Krazykoolrohit · Post by **Krazykoolrohit** » Wed Jun 14, 2006 7:16 am

Ok ArndW,

will try and fix it today. I hope the slowness is because of this issue because i have tried all other solutions to improove the performance. thanx for the advice

Krazykoolrohit · Post by **Krazykoolrohit** » Mon Jun 19, 2006 9:47 am

Hi,

I fixed all the errors and now the job is loading data into the tables and not logging any warnings.

but they are running vrey slow some of them at 30 -80 rows per second.

Is there any bechmark speed to which i should try and optimize my jobs? i mean somwhere around 1000 rows per socond. something ideal.

I have tried sorting, caching, primary key check with the tables. anything else i should do to make them work fast?

DSguru2B · Post by **DSguru2B** » Mon Jun 19, 2006 9:56 am

If you are loading the table using the DB2 load stage then 80-100 rows/sec is extremely slow. You can get better performance with the DB2 API stage with that. Ideally, with the load stage, you should be getting about 15k - 35k rows/sec.

Krazykoolrohit · Post by **Krazykoolrohit** » Mon Jun 19, 2006 10:05 am

any suggestions to improove performance? i have tried all that have been suggested till now.

DSguru2B · Post by **DSguru2B** » Mon Jun 19, 2006 10:09 am

You are sure there are no warning messages in the message file?
What about the directors log?
How many columns are you passing?

Krazykoolrohit · Post by **Krazykoolrohit** » Mon Jun 19, 2006 10:16 am

there are typcally around 200 columns per table. I am sure there are no logs. The rows are loading but slow. From what i have seen, the loggin starts once all the records have been loaded and then the speed decreases.

there are just few warnings in manager as i am reading a BIT as integer. nothing more

Any help?

DSguru2B · Post by **DSguru2B** » Mon Jun 19, 2006 1:04 pm

Ok. I think its the number of bytes you are sending is the bottleneck. Also towards the end of the message file, it gives you the time when the load was finished, when the load utility starts rebuilding the indices and when it finishes. See where is the bulk of the time going.
Also i would advise to build the load ready file and initiate the load utility via the command prompt. How much time does it take then will give you more insight.

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

The issue is with Bulk stage only. The job does nothing except bulk loading the file to DB2 table. any parameters which i need to check?

i have increased row buffer size and save count

DSguru2B · Post by **DSguru2B** » Mon Jun 19, 2006 1:16 pm

The bulk stage does nothing but to create the three files that i mentioned earlier. Those files just invoke DB2's load utility. All that DataStage is doing here is building the BAT file and firing the load utility. Thats why i asked you to try to load the data using command prompt to invoke the load utility. That test was to find out where the fault lies.
Also, did you look at the actual load times and the index build times in the message file?
Also check your Pagesize for the table. The lower it is the slower the load will be.