Hi,

I am working on AIX platform and V8.2 UDB.Can you please help me with solutions for the following.Thanx in advance

1)How do you write a stored procedure for the application team to execute.They need to delete data from a table depending on a particular condition and should not log into the logs.For Ex a table has millions of rows and I want to delete rows which have the date 12/06/2006.How can it be done any idea. I could delete all the data from a table using the alter table activate not logged initially with empty table.But I want to delete for a month.


2)How do you delete data from a parent table not logging into the logs as the data are in millions of rows.And I do not want to delete data from the child just the data form the parent which have foreign keys or constraints.

Thanx
Venky

Do you mean you want to have a situation in which the referential integrity (RI) rules do not apply ? You could create the FK with the NOT ENFORCED option, but I would only do that if I was sure the RI rule still holds.

Why would you want to do that?
That means that it would be impossible afterwards to restore to the current database situation from an earlier backup!

1) MDC (Multi-Dimension Clustering) with the appropriate fixpack (not sure whether its 8 or 10) can probably help you with 1.

In MDC, if your query has an exact match for one of the dimensions, then the deletes will (may) not log the actual rows/records being deleted, but just the data/index page information.

Look up the online DB2 documentation for details -
http://publib.boulder.ibm.com/infoce...n/r0009949.htm

Note that you will need the right fixpack AND the DB2_MDC_ROLLOUT registry variable set to ON (note that this is a ONE-WAY street - it can't be undone).

You can get more info on the DB2 profile registry variable at
http://publib.boulder.ibm.com/infoce...n/r0009949.htm

2) For this I think the only way out would be to NOT enforce your constraint.