We have a requirement to delete rows in the order of millions from multiple tables as a batch job (note that we are not deleting all the rows, we are deleting based on a timestamp stored in an indexed column). Obviously a normal DELETE takes forever (because of logging, referential constraint checking etc.). I know in the LUW world, we have ALTER TABLE NOT LOGGED INITIALLY but I can't seem to find the an equivalent SQL statement for DB2 v8 z/OS. Any one has any ideas on how to do this really fast? Also, any ideas on how to avoid the referential checks when deleting the rows? Please let me know.
A:
Do the foreign keys already have indexes as well?
How do you have your delete action set?
CASCADE, NULL, NO ACTIONUse SET INTEGRITY to temporarily disable constraints on the batch process. http://www.ibm.com/developerworks/data/library/techarticle/dm-0401melnyk/index.html
http://publib.boulder.ibm.com/infocenter/db2luw/v8/index.jsp?topic=/com.ibm.db2.udb.doc/admin/r
jasonk
2010-04-05 19:49:02
Yes, the foreign keys already have indexes. DELETE action is NO ACTION. I'll look into SET INTEGRITY. The bigger issue here is being able to temporarily not LOG the DELETE operation. Is it possible to do that on DB2 z/OS v8? Thanks for your help!
Azeem
2010-04-06 13:38:11
Are you sure it's the LOG portion that's the detail and not the referential integrity checks?Can you post the SQL being used for possible performance improvements?
jasonk
2010-04-06 15:32:03
Are you sure the syntax on the initial log skip was correct?'ALTER TABLE [tablename] ACTIVATE NOT LOGGED INITIALLY'
jasonk
2010-04-06 15:38:13
@jasonk, sorry, yes I did mean ACTIVATE NOT LOGGED INITIALLY. However, it doesn't look like DB2 v8 supports that.
Azeem
2010-04-07 04:01:45
on the LOG portion, I've update the original post to include the SQL.
Azeem
2010-04-07 04:02:07
Here is the SQL: DELETE FROM table_a TA WHERE NOT EXISTS (SELECT UID FROM table_b TB where TB.UID = TA.UID) -- sorry my edits did not go through.
Azeem
2010-04-07 04:06:19
+1
A:
In the past I have solved this kind of problem by exporting the data and re-loading it with a replace style command. For example:
EXPORT to myfile.ixf OF ixf
SELECT *
FROM my_table
WHERE last_modified < CURRENT TIMESTAMP - 30 DAYS;
Then you can LOAD it back in, replacing the old stuff.
LOAD FROM myfile.ixf OF ixf
REPLACE INTO my_table
NONRECOVERABLE INDEXING MODE INCREMENTAL;
I'm not sure whether this will be faster or not for you (probably it depends on whether you're deleting more than you're keeping).
Scott Jones
2010-04-15 05:36:32
Scott, thanks for your answer. Yes, we looked into a variant of this (using REORG DISCARD) but DB2 v8 for z/OS has a restriction on the SELECT you can issue on the table (even when doing a LOAD/UNLOAD). Anyway, it turned out to be rather easy to resolve. I'll post it as an answer shortly.
Azeem
2010-04-15 15:50:00
A:
We modified the tablespace so the lock would occur at the tablespace level instead of at the page level. Once we changed that DB2 only required one lock to do the DELETE and we didn't have any issues with locking. As for the logging, we just asked the customer to be aware of the amount of logging required (as there did not seem to be a solution to get around the logging issue). As for the constraints, we just dropped and recreated them after the delete.
Thanks all for your help.
Azeem
2010-04-15 15:51:44