DB2 truncate and insert problem

[Xanadu]

hello
I am facing this problem....
I have to pull 40 million rows from source into staging.(full refresh)
(In earlier run, it inserted around 20-25 million rows and aborted..communication error..)
Now I have to remove those existing rows and insert all the 40 million new ones...
I have the following options :

1) Truncate and insert : DB2 doesn't have a truncate option - so it calls "delete from tablename" .... delete rites everything to transaction logs and then deletes - so first problem is the transaction logs get full.
Another problem is that the query is timing out (I increased the time-out in db config now)
2) Another option is to drop table and re-create it - but I dont want to table to be created in default table space. This belongs to a new table space. I know I can over-come this problem by user-defined sql but there is another problem - There are about 200 triggers that are associated with the table. So I dont want to drop and re-create the table.

So problem still exists..
Any directions please...
thanks in adv
-Xanadu

[rasi]

Quick way to delete records in db2 table is to use the bulk load with the update option by sending empty file. This way it clears the table without having any transactions log.

If you want to insert 40 millions rows I will suggest you to send the output to sequential file and then use the DB2 Bulk load. This is much faster and efficient way to do

Thanks
Siva

[dhiraj]

Hey Xanadu,

You could land your target in to sequential file and then use the db2 bulk load stage to bulk load the file using the DB2 bulk load stage. set the load type property to REPLACE. If you do not want to land your target in to seq file then bulk load an empty file first and then run your job which does the insert.

IHTH,

Dhiraj

[ogmios]

Hey Xanadu,

You could land your target in to sequential file and then use the db2 bulk load stage to bulk load the file using the DB2 bulk load stage. set the load type property to REPLACE. If you do not want to land your target in to seq file then bulk load an empty file first and then run your job which does the insert.

IHTH,

Dhiraj

Bulk load outside of DataStage is many times faster than using the DataStage bulk load stage.

Ogmios

[s_r_chandru]

Hi,
I agree to use the Bulk Load Utility to populate data to db2 tables.But One Concern in using it is that it monopolize the Tablespace.So take causion in using it preferrably test it (Becoz say the load fails inbetween populating data then the tablespace state will be in quiesce mode you need to unquiesce the table Inorder to use tablespace) Also allocate sufficient space for the tablespace to hold up 40m record.Better to use Database Managed Tablespace becoz the overhead is less compare to SMS tablespace.
Hope it give's some direction in your approach.
-Chandru

[oanielsen]

Here is a little Korn Shell script that I use to delete a db2 table quickly. I call it load_blank.ksh


if [ $# -ne 3 ]
then
print "You must enter the instance and database and table name"
exit
fi

INSTANCE=$1
DATABASE=$2
TABLE=$3

db2 -v "connect to $DATABASE";

db2 -v "load from /dev/null of del messages FilePath/$TABLE.msg tempfiles path /${INSTANCE}_tempfiles/$TABLE replace into $INSTANCE.$TABLE nonrecoverable indexing mode autoselect";
db2 -v terminate

cat FilePath/log/$TABLE.msg
rm FilePath/log/$TABLE.msg

[Xanadu]

Thanks Oniel and others.
I am using this command to truncate the table.

import from <empty file> of del replace into <table name>

Thnks
-Xan