Results 1 to 4 of 4
  1. #1
    Join Date
    Jul 2005
    Posts
    47

    Unanswered: Deleting data from table

    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

  2. #2
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    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.
    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.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  3. #3
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    Quote Originally Posted by venky5436
    ... and should not log into the logs.
    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!
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

  4. #4
    Join Date
    Mar 2004
    Posts
    46
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •