DSXchange

Hi.
Does anyone know what command are sent to the DB2 database when we use the DB2 UDB Enterprise stage with this setting:

Write Method: Write
Write Mode: Truncate

Does it send an "Import Replace" command to DB2?
Does it send a Delete statement?... Or an Import /dev/null?

We want to reload a very huge table... In Oracle, I'd issue a Truncate statement... but there is no Truncate statement in DB2... this is why I ask how this stage works!!!

Thanks.

You load from Dev/null into the DB2 table to truncate.
It shouldnt be a problem.

When you use the truncate option, DataStage is actually sending a delete statement to the database. If the table is small (under a million), the work is really negligible. However, if you are dealing with even a medium sized table of a few million the time adds up quickly. Get to a large table and you are probably going to have transaction log issues deleting all the records.

What we have done is truncate the table before the load/write process. We run all of our DataStage jobs from Unix scripts. For the most part, all of the scripts have similar tasks:

1. Truncate the table
2. Drop the indexes
3. Run the DataStage job to populate the table
4. Rebuild indexes
5. Collect statistics on the table

There are several different ways to truncate the table. As DSguru28 mentioned, you can load the table from /dev/null (either load or import will work). You can also do a import or load from an empty file with replace mode. What we do is create the tables with 'not logged initially' so that we can truncate the tables with the following command:
Now, just a quick note. We do not use 'not logged initially' with all of our tables. We tend to load from DataStage either into a work table with SQL to push data into production tables, or DataStage is loading a production table that is replaced each time. In those cases, 'not logged initially' allows us to truncate the table instantly with none of the overhead issues incurred by import and load. The benefit of using import or load to handle truncation is that they can run against any table - you don't have to create the table in any particular manner.

Given the size of your table you are definitely right to truncate it first BEFORE DataStage. If you will always be loading 100% of the table (not doing insert and/or update), then you should consider using the 'not logged initially' and the truncate option listed above. However, if you are going to be doing inserts and updates to this table and just need to reload it now, you should use one of the import/load options.

Hope this helps!

Brad.

i forgot to mention one point in load replace.
Make sure the table you are dealing with has its own table space.
If there are several other tables in the same table space as the table in question, then a load replace will wipe out all other tables in that table space.
Be very careful. Sorry for not bringing that to your attention earlier.

DSguru2B wrote:i forgot to mention one point in load replace.
Make sure the table you are dealing with has its own table space.
If there are several other tables in the same table space as the table in question, then a load replace will wipe out all other tables in that table space.
Be very careful. Sorry for not bringing that to your attention earlier.

What version of DB2 are you working with? What command are you running to do the /dev/null load?

We have never had this issue. In older DB2 versions the whole space is locked for the duration of the load, but other tables were not impacted once the lock was dropped. However, as of v8 (maybe earlier, but not sure), only the table itself is locked. In fact, now you can even allow users to read the table while it is being loaded (as long as it is not a complete replace). But either way, we've never had a situation where we couldn't truncate a table (with any method) that was in a space with multiple tables.

Could you elaborate on your warning? Thanks!

Brad.

i was referring to the table space.
Multiple tables can be assigned to a single table space. A load replace has a wiping effect on all the tables specified to that one single table space.

I just checked with my dba.
If the database is UDB DB2, then a load replace will not wipe away all the tables.
But for DB2 it will

DSguru2B wrote:I just checked with my dba.
If the database is UDB DB2, then a load replace will not wipe away all the tables.
But for DB2 it will

That makes sense - we haven't run into it because we are using UDB. Thanks for the warning and explanation!

Brad.