DB2 Bulk Load stage

[EJRoufs]

Is there a Bulk Load Stage for DB2 in any of the newer versions of DataStage (we're using version 6)? I see a bulk load for UDB, but nothing for DB2. We're currently loading DB2 tables on the mainframe from our DataStage server jobs, and it becomes quite a headache!

[ogmios]

Is there a Bulk Load Stage for DB2 in any of the newer versions of DataStage (we're using version 6)? I see a bulk load for UDB, but nothing for DB2. We're currently loading DB2 tables on the mainframe from our DataStage server jobs, and it becomes quite a headache!

:D UDB is the same as DB2... go figure. But I wouldn't use the UDB bulk loader. Ascential kind of screwed this stage up which makes it sometimes even slower than the actual DB2 plugin. For all of our big loads we created db2load scripts (we are on UNIX) that are each about 10 times faster than the corresponding UDB bulk load.

Ogmios

[EJRoufs]

:D UDB is the same as DB2... go figure. But I wouldn't use the UDB bulk loader. Ascential kind of screwed this stage up which makes it sometimes even slower than the actual DB2 plugin. For all of our big loads we created db2load scripts (we are on UNIX) that are each about 10 times faster than the corresponding UDB bulk load.
Ogmios[/quote]

What if we're not currently using Unix? ;>

[ogmios]

What if we're not currently using Unix? ;>

The Windows UDB bulk stage has the same problem as the UNIX version. You can still write scripts to use the native db2 loader in the proper way. Of course if you then ever switch to DataStage on UNIX you may have to rewrite some things.

The rule of thumb I use is that every load to DB2 under 500.000 rows is done with either the ODBC plugin or the DB2 normal stage. Above 500.000 we use load scripts.

Ogmios.

P.S. UDB is also the same as DB2 under Windows (in DataStage anyway)

[EJRoufs]

P.S. UDB is also the same as DB2 under Windows (in DataStage anyway)

Thanks for all the responses, everyone!

I've never used UDB, but i was under the impression UDB was basically DB2 on a server, and DB2 was on the mainframe. We use Server DataStage, but we load all of our tables on mainframe DB2.

When you are talking about creating "scripts" to load the tables, are you talking about scripts within DataStage? Or some type of batch job in windows itself that DataStage would just kick off?

[ogmios]

We use 2 kind of scripts for loading:

1) Where the file to be loaded is first created by a DataStage job writing to a sequential file, and in DataStage after that job the file is loaded by executing a script with DSExecute. This then has a fixed control file "on board" to load the data. This is usually done when "difficult" transformations are required.

2) Scripts being executed via DSExecute where the unload and load is included in the script itself.

Whatever you do keep execution control in DataStage, else you're operators may get a hell of a time supporting your projects.

Ogmios

[Christina Lim]

1) Where the file to be loaded is first created by a DataStage job writing to a sequential file, and in DataStage after that job the file is loaded by executing a script with DSExecute. This then has a fixed control file "on board" to load the data. This is usually done when "difficult" transformations are required.

Is it possible if you can provide me a more detailed steps to achieve this? I haven't tried something like this before.

I have been using db2 plugin to insert data into the database.
However, i keep getting a table lock. Sumtimes, it is due to stop action to the jobs that access the table, and subsequent drop/create/insert into the table would hang. On other times, it just hang ... without any reason. The worse thing is that I need the dba to restart the database before I can acces the table again. That is very awful.

Appreciate your advise on these. Thanz

[EJRoufs]

Is it possible if you can provide me a more detailed steps to achieve this? I haven't tried something like this before.

I have been using db2 plugin to insert data into the database.
However, i keep getting a table lock. Sumtimes, it is due to stop action to the jobs that access the table, and subsequent drop/create/insert into the table would hang. On other times, it just hang ... without any reason. The worse thing is that I need the dba to restart the database before I can acces the table again. That is very awful.

Appreciate your advise on these. Thanz

I know i got table locks often here, as well, using the DB2 plugin to load the tables. The problem here was that i was blowing out the logs every time i tried to insert more than about 600,000 records. So, i just ended up splitting up the data, and doing loads of about 250,000 records each.

[chucksmith]

Couldn't you use the UDB stage to create the sequential data, control, and batch files, but set the immediate load option to no, then load the data later by running the batch file?

I realize these files must be local to the DB2 server, so a rcp or ftp may be required.

Is the performance difference mentioned earlier related to differences in the DS generated batch/control file and the custom ones?

If this approach gives the same performance, it will mean that you do not have to maintain seperate load scripts, just update the job since it will create them for you.