DSXchange

I have to load 28 million records onto DB2 Warehouse.. i was using DB2 Plugin but its taking morethan 10 hrs to load... we got UDBLoad plugin.. but i don't have documentation to use it...can anyone help me how to set up the properties for UDBLoad plugin stage.

nag0143 wrote:I have to load 28 million records onto DB2 Warehouse.. i was using DB2 Plugin but its taking morethan 10 hrs to load... we got UDBLoad plugin.. but i don't have documentation to use it...can anyone help me how to set up the properties for UDBLoad plugin stage.

In short, don't use the UDBLoad stage. Write a job that writes is output to a sequential file and then write your own UNIX script using the db2 load utility (executing it via DataStage).

In the past I even had instances where the regular UDB plugin stage would perform faster than the UDBLoad plugin.

Ogmios

I have the same experience too. I am curious if this is the case for version 6 / 7. Any idea?

dickfong wrote:I have the same experience too. I am curious if this is the case for version 6 / 7. Any idea?

I'm currently working with version 6 and it's still the same as on version 5.

Ogmios

ogmios wrote:

dickfong wrote:I have the same experience too. I am curious if this is the case for version 6 / 7. Any idea?

I'm currently working with version 6 and it's still the same as on version 5.

Ogmios

But can you help me set the properties , i want to try it out anyway

[/quote]

But can you help me set the properties , i want to try it out anyway[/quote]

i am getting invalid pointer to database error... may be i don't have write permissions on the database.

nag0143 wrote:
But can you help me set the properties , i want to try it out anyway

i am getting invalid pointer to database error... may be i don't have write permissions on the database.

Your db2 database has to be cataloged on the system where the DataStage server is running and if your db2 database is on another server you have to play around with directories as the the plugin makes some big assumptions.

Ogmios

ogmios wrote:

nag0143 wrote:
But can you help me set the properties , i want to try it out anyway

i am getting invalid pointer to database error... may be i don't have write permissions on the database.

Your db2 database has to be cataloged on the system where the DataStage server is running and if your db2 database is on another server you have to play around with directories as the the plugin makes some big assumptions.

Ogmios

Ogmios,

yeah my db2 database is on different server... what kind of assumptions should i make in the properties of UDBLoad.

Nag

nag0143 wrote: Ogmios,

yeah my db2 database is on different server... what kind of assumptions should i make in the properties of UDBLoad.

Nag

If I recall correctly you have to enter a "working directory" somewhere in the stage and this directory should exist on both local and remote system (for a test only I would suggest /tmp/).

There's also a switch somewhere to do a "load from client", this should be yes (on a local system you can do db2 load, on a remote system you have to use "db2 load client").

The database should also be cataloged on the db2 instance on the server on which DataStage is running (can you access your remote instance from the command line on your datastage system?).

Ogmios

The important thing to remember is that DataStage is just another client, as far as the database server is concerned.

What this means in practice is that you have to configure any necessary client software and connectivity just the same as if you were configuring any other database client.

There are rules for bulk loaders, too, though these differ between databases. Some will allow the data files to be remote, others will not. Some insist on the bulk loader being initiated on the same machine as the database server (in which case you may need to FTP the data file(s) to that machine), others allow remote execution.

[/quote]

If I recall correctly you have to enter a "working directory" somewhere in the stage and this directory should exist on both local and remote system (for a test only I would suggest /tmp/).

There's also a switch somewhere to do a "load from client", this should be yes (on a local system you can do db2 load, on a remote system you have to use "db2 load client").

The database should also be cataloged on the db2 instance on the server on which DataStage is running (can you access your remote instance from the command line on your datastage system?).

Ogmios[/quote]

I don't see a switch load from client ..i know i have that in version 6/7 but i am working with ds5.1 so... is there any other way to comearound that...
nag

The load client switch seems to be there with version 7 only. Speed? I'm currently loading 1mio rows in about 5 minutes (heavy utilized AIX machine, one processor only...)

Klaus

hi

Then is it not possible to use udbload when db2 and datastage are on different servers as i don't have the option of using load from client in version 5.1 YES (which is available with 7.0)



Thanks
Nag

Nag,

you can still use the udbload stage and use the load deferred option. You can then start the loader from command line or unix script.

Klaus

Klaus,


But why cannot run my job from director?? just for verfication.

Nag