DB2/UDB Load Stage

DSguru2B · Post by **DSguru2B** » Wed Mar 15, 2006 3:18 pm

I would like to know more about the db2 load stage. Has anyone ever used it before?
I have a requirement to BCP 10 million records from sql server to udb. My architecture team wants me to run a proof of concept to get a feel of the performance via the db2 load stage.
Personally, i do not like the idea as i have heard different things about the db2 load stage.
If anyone has used this stage before, please come forward and throw some light on it.
Any Help will be appreciated.
Thanks guys

kcbland · Post by **kcbland** » Wed Mar 15, 2006 3:34 pm

It's fast as long on you don't mind table level locking.

DSguru2B · Post by **DSguru2B** » Wed Mar 15, 2006 3:38 pm

I dont mind table level locking at all. As far as I know, only I will be loading that table. No other application will be doing so.

So i guess i am fine with that, my main concern is, by using the DB2/UDB Load stage, will i be doing more harm then good?
To be really honest, i have never used this stage before, and that stage has so many options to set before i can use it. I went through the manual, but i could only grab general information from there.
i would really appreciate it if someone could warn me of some possible consequence that this stage might carry with it and guide me in setting the min. settings in order to get a proof of concept done. I can add additional functionalities later on.
Thanks Ken.

ogmios · Post by **ogmios** » Wed Mar 15, 2006 3:46 pm

From personal experience, the DB2 load stage is much slower then if you would write your own UNIX load statement via a shell script. So although the stage is nicer to use, we use shellscripts to load data for the raw speed.

Also if the DB2 database you want to load to is on a different server than the Datastage server a certain path has to exist on both datastage and the remote DB2 server. Else you will get an error message and the load will fail.

Ogmios

DSguru2B · Post by **DSguru2B** » Wed Mar 15, 2006 3:52 pm

Hmm, tell me more.
You are right, our DS server is on the Sun and UDB on AIX box. So if i understood you correctly, additional setting is required on both the servers for the db2/udb load stage to function. Now that doesnt sound good

Can you identify those settings for me?
Thanks for your help,

DSguru2B · Post by **DSguru2B** » Wed Mar 15, 2006 3:59 pm

I tried running a job which reads data from the sql server and loads it into UDB via the db2/udb load stage. the design is simple and straight forward.

Code: Select all


SQL --------> XFM ----------> DB2/UDB Load Stage

The job is aborting. The director is giving me the following fatal error message.

Code: Select all


CopyOfCGIProducer_DW_POL_PAYMT..DB2_UDB_Load_27: Invalid pointer to stage user data
Internal Error: Function 'get_stage_prop' failed
Error opening file in 'Directory for Data and Command Files'

ogmios · Post by **ogmios** » Wed Mar 15, 2006 4:01 pm

Right now without DataStage access... will try to look it up tomorrow.

The "problem" with the DataStage DB2 Load stage is that the control file it generates is not very flexible and that it uses a kind of spool directory, and this requires the same directory to exist on the remote db2 server.

Also get your DBA's involved in your situation. If the versions of DB2 are not exactly the same on both platforms, e.g. v8 on UNIX and v6 on AIX, the DBA's may need to install special patches on the AIX to be able to get the UNIX version to work with it.

Ogmios

DSguru2B · Post by **DSguru2B** » Wed Mar 15, 2006 4:05 pm

Sure, thanks for your time ogmios. Really appreciate it.
The error message i posted in my previous post, is this due to the fact that i have different servers for DS and UDB, or this error is because of some property that i failed to specify correctly

Thanks once again for all your help guys

ogmios · Post by **ogmios** » Wed Mar 15, 2006 4:20 pm

DSguru2B wrote:Sure, thanks for your time ogmios. Really appreciate it.
The error message i posted in my previous post, is this due to the fact that i have different servers for DS and UDB, or this error is because of some property that i failed to specify correctly
Thanks once again for all your help guys

Guessing now... probably you don't have a property set right (right click on the DB2 load stage and do properties). Or you don't have the file system permissions on the directory which is specified in one of the properties.

DSguru2B · Post by **DSguru2B** » Thu Mar 16, 2006 8:03 am

the path i have specified for the "directory for Data and command files" is on the AIX box where udb is.
i am not exactly sure what to specify in the LOB path, i gave the same path as above.
load method is NamedPipe
rest i left it as default.

dont know whats going wrong

DSguru2B · Post by **DSguru2B** » Thu Mar 16, 2006 9:38 am

hey ogmios, i got it.
the problem was that i was specifying the directory path on the AIX box. (dont ask me why i was doing that

)
then i changed it to path on my DS server.
it works now. Got a real boost in performance (A whopping 15,000 rows/sec).
My architecture team is going to like that.
Thank you so much ogmios.

DSguru2B · Post by **DSguru2B** » Thu Mar 16, 2006 11:41 am

Hi guys,
Now i am bumping into another problem. I got the db2/udb load stage to work. But towards the end, the performance starts decreasing and the job never seems to finish.
i ran a test to just load 1000 rows, in the performance stats, it shows that all the rows are transfered, but the job doesnt finish.
i have no indexes on the target table.
What could it be

DSguru2B · Post by **DSguru2B** » Thu Mar 16, 2006 12:11 pm

I got it guys. The director wasnt giving me any warning or error message because i was forcing the job to abort. Couldnt find out the real problem. Then i looked into the msg.txt that the bulk loader creates. In that i found the problem

Code: Select all


SQL0551N  "XXX" does not have the privilege to perform operation "INSERT"
on object "CGIDW.POLICY_PAYMENTS".  SQLSTATE=42501

It was all a wild goose chase. The real problem lied in not having the right privilege.

Thanks guys.