Results 1 to 11 of 11
  1. #1
    Join Date
    Aug 2011
    Posts
    10

    Unanswered: deleting large number of rows in a table that has 20 child tables

    Hi there,

    Thanks to forum for everything.
    I have a problem here for ur valuable response,


    I have a table with almost one million rows, the table has 21 child tables. I need to delete two lac rows from the master table. Is there any solution, so that I dont face any transaction log full error???

    thank you.

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Write a stored procedure with a cursor on the main parent table, and then commit after each logical unit of work (which includes deleting all the child tables for a parent). You will need to define the cursor WITH HOLD so it will not close when you do a commit.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  3. #3
    Join Date
    Aug 2011
    Posts
    10
    Quote Originally Posted by Marcus_A View Post
    Write a stored procedure with a cursor on the main parent table, and then commit after each logical unit of work (which includes deleting all the child tables for a parent). You will need to define the cursor WITH HOLD so it will not close when you do a commit.
    thank you, but can u plz elaborate it?

  4. #4
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by jagdis_vishnu View Post
    thank you, but can u plz elaborate it?
    Actaully writing such a stored procedure is a fee-based activity IMO. If you or your company are interested, then send me a private message.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  5. #5
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    Try using the search button. This question has been asked and answered dozens of times. If you search hard enough you will find an example.

    If you have difficulties when creating stored procedure, come back to the forum.

    Or, of course you have the option to PM Marcus.

  6. #6
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    that is really a good idea ..
    ...is a fee-based activity IMO. If you or your company are interested, then send me a private message..
    I will use it a lot in the future.. in this forum anyhow..
    Best Regards, Guy Przytula
    Database Software Consultant
    Good DBAs are not formed in a week or a month. They are created little by little, day by day. Protracted and patient effort is needed to develop good DBAs.
    Spoon feeding : To treat (another) in a way that discourages independent thought or action, as by overindulgence.
    DB2 UDB LUW Certified V7-V8-V9-V9.7-V10.1-V10.5 DB Admin - Advanced DBA -Dprop..
    Information Server Datastage Certified
    http://www.infocura.be

  7. #7
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    Quote Originally Posted by sathyaram_s View Post
    Or, of course you have the option to PM Marcus.
    ..... or Guy
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  8. #8
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    I will also provide forum searches for people (on a fee basis) for those who don't have time to do it themselves.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  9. #9
    Join Date
    Apr 2008
    Posts
    39
    If it is a one time job .. then this could be a idea. Select rows in chunks
    and delete them again and again.

    delete form (select * from table name fetch first 15000 rows only).

    you can right a small shell script also.

    Thanks
    Naval K

  10. #10
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by Naval29 View Post
    If it is a one time job .. then this could be a idea. Select rows in chunks
    and delete them again and again.

    delete form (select * from table name fetch first 15000 rows only).

    you can right a small shell script also.

    Thanks
    Naval K
    The reason why I recommended a stored procedure is that the OP said there was one parent table with 21 child tables, and I assume that the logical UOW integrety needs to be maintained. A properly written SP will also have the following additional advantages:

    • not filling up the transaction log since there would be intermediate commits at the logical UOW level
    • doing intermediate commits reduces lock contention during the deletes, since only those rows deleted since the last commit point will be locked
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  11. #11
    Join Date
    Sep 2008
    Location
    Toronto,Canada
    Posts
    655
    Quote Originally Posted by Naval29 View Post
    If it is a one time job .. then this could be a idea. Select rows in chunks
    and delete them again and again.

    delete form (select * from table name fetch first 15000 rows only).

    you can right a small shell script also.

    Thanks
    Naval K

    This is second best option to SP. I had used it for all one time jobs. Some daily jobs run via SP.

    Make sure the chunk is small enough to let the database keep going easy.

    One of my prune job using shell script took 11 hours on a very busy gaming database.

    A template SP can be provided if needed !!


    Regards

    DBFinder

Tags for this Thread

Posting Permissions

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