• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1959
  • Last Modified:

Best solution to delete large table(s) in DB2

Hi,

Here is the question.
I load a large ammount of data in one single table, in DB2.
This happens daily. If I want to load the new data, I have a few options:
One is to delete each row, the other one is to drop the whole table.
Each has it's own characteristics.
If I decide to delete, keeping the lock on row is not good. It takes a long time.
I could change the lock on the whole table, then delete.

If I drop the table and recreate it, it is fast.
However the indexes are lost too.

This does not seem to matter on a single table.

However, if I want to generalize the process that loads data to a database, and create a mechanism that can do so by being initialized different configuration settings, I need to provide for most complex soloutions like: more then one table is being involved, there are relationships between tables, and maybe only some of those tables need to lose the data.
Etc.

Any ideea ?

0
simi
Asked:
simi
  • 3
1 Solution
 
jrb1Commented:
Well, other databases have better options...but how about this:

LOAD REPLACE FROM MYEMPTYFILE OF DEL INTO MYTABLE

where MYEMPTYFILE is an empty file.

That and a couple of other options here:

http://www.db2times.com/modules.php?name=News&file=article&sid=21
0
 
simiAuthor Commented:
Forgot to mention, the program that does this in written in java.

I am not sure, jrb1, that I can use the solution from within the java program.
Is this solution available only if you use the DB2 utilities ?
0
 
jrb1Commented:
Well, you could issue this SQL:

ALTER TABLE MYTABLE ACTIVATE NOT LOGGED INITIALLY WITH EMPTY TABLE

If the table wasn't created with the "NOT LOGGED INITIALLY" option, I'd recreate it (and any constraints, etc.)  Then the alter will take care of it.
0
 
jrb1Commented:
No comment has been added to this question in more than 21 days, so it is now classified as abandoned..
I will leave the following recommendation for this question in the Cleanup topic area:
   Accept: jrb1

Any objections should be posted here in the next 4 days. After that time, the question will be closed.

John Bush
EE Cleanup Volunteer
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now