Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1957
  • 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

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

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