Results 1 to 14 of 14
  1. #1
    Join Date
    Jun 2003
    Posts
    24

    Unanswered: Deletion of 5 lac records from a table

    Hi,

    Is there a query which can be utilized to delete appx 5 lacs record within an hour. The normal query "delete from <table name> where colname = 'X' " takes lot of time to delete 5 lcd record.

    Thanks,
    Gulshan

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    I don't know what a lac or lcd is, but it must be a lot. I suspect that there is some waiting on the log buffer and active log. Tuning of these parameters would probably help (significantly increase their size).

    If there was some way to break the SQL delete into multiple SQL statements that delete smaller chunks and do intermediate commits after each statement, that might help some. You could also run them in parallel.

    Another way to get intermediate commits, is to open a cursor on the rows to be deleted using the WITH HOLD option, and then delete where current of cursor. Then you can do a commit every 1000 rows without losing position on the cursor. Not absolutely sure if this would speed things up, but would definitely help on recovery if the delete terminated.

    Another technique (if the remaining rows are fewer than the ones deleted) is to extract the remaining rows onto a file and load replace them back into the table. This would effectively perform a reorg at the same time.
    Last edited by Marcus_A; 09-24-03 at 03:23.

  3. #3
    Join Date
    Sep 2002
    Posts
    456

    Re: Deletion of 5 lac records from a table

    Export the data you wanted to keep after deletion using the where clause in export command. Load a null file with replace option and then import or load the file exported previously.

    dollar

    Originally posted by gulshan_gandhi
    Hi,

    Is there a query which can be utilized to delete appx 5 lacs record within an hour. The normal query "delete from <table name> where colname = 'X' " takes lot of time to delete 5 lcd record.

    Thanks,
    Gulshan

  4. #4
    Join Date
    Aug 2003
    Posts
    21

    Re: Deletion of 5 lac records from a table

    gulshan , not many outside the indian sub-continent use lakh or lac . To make it universally compliant you should mention numbers in their thousands and millions and not in lakhs and crores. hope you don't mind this comment

  5. #5
    Join Date
    Jun 2003
    Posts
    24

    Re: Deletion of 5 lac records from a table

    Agreed and Thanks

  6. #6
    Join Date
    Jun 2003
    Posts
    24
    The sqluload api of db2 has done the magic. The only question is before loading the tabke using this api, it asks to queue the tablespace where table resides . I used sqluvqdp api to queue the tablespace.
    But can some one clarify why Quiescing is required before loading the table?

  7. #7
    Join Date
    Jul 2003
    Location
    Austin, TX, USA
    Posts
    278
    Quiescing is the action of forcing all users of the system/database when you need to perform administrative taks.

    Let's say I am performing a load which needs me to access the tables which are frequently accessed by users, then quiescing the same would allow me to gain exclusive access to that tablespace/table till I complete my actions so that my load goes through smoothly as well i am assured that no intermediate actions are performed on the table.

    Hope this explains..

    Nitin.

  8. #8
    Join Date
    Jun 2003
    Posts
    24
    Thanks Nitin, it was helpful

  9. #9
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    Gulshan .. IMHO, you must use the IMPORT API, a blank DEL file as the source ...

    LOAD has implications on recovery ...

    Cheers
    Sathyaram
    Last edited by sathyaram_s; 09-26-03 at 05:11.
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  10. #10
    Join Date
    Sep 2003
    Posts
    36
    Originally posted by sathyaram_s
    Gulshan .. IMHO, you must use the IMPORT API, a blank DEL file as the source ...

    LOAD has implications on recovery ...

    Cheers
    Sathyaram

    Sathya...u mean a dummy load ..........
    Last edited by sahana; 09-26-03 at 08:17.

  11. #11
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    A dummy IMPORT rather than a LOAD ..

    dummy in the sense, by using the REPLACE option and a blank file , you clear down the table data ...

    Cheers
    Sathyaram


    Originally posted by sahana
    Sathya...u mean a dummy load ..........
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  12. #12
    Join Date
    Sep 2002
    Posts
    456
    What would be the benifit of dummy IMPORT over dummy LOAD? Aren't they same!!!

    dollar

    Originally posted by sathyaram_s
    A dummy IMPORT rather than a LOAD ..

    dummy in the sense, by using the REPLACE option and a blank file , you clear down the table data ...

    Cheers
    Sathyaram

  13. #13
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    Functionally, they are the same ...

    In case you have to recover and rollforward the tablespace, your LOAD .. REPLACE.. NONRECOVERABLE will mark the table as inaccessibe .. You loose all data you inserted after that point in time ...
    With IMPORT .. REPLACE your table will be 'intact' even after the rollforward ..

    Try the following on a small test database ... Your database should have LOGRETAIN ON

    1) Create a table say tab1
    2) Populate it with a few rows
    3) backup your database
    4) IMPORT ... REPLACE null file into the table
    5) Insert a row into the table
    6) Resotre the backup image and rollforward
    7) Connect and access the table... You will get the row you inserted after the IMPORT

    To test LOAD, do the same, this time, step 4 will LOAD ... REPLACE nonrecoverable ... In step 7, your table will be inaccessible ...

    HTH

    Sathyaram


    Originally posted by dollar489
    What would be the benifit of dummy IMPORT over dummy LOAD? Aren't they same!!!

    dollar
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  14. #14
    Join Date
    Mar 2003
    Posts
    343
    Yes but if you have all the load files, you can rollforward to the minimum point in time for the database and then load all the data back in. I mean there are ways around that.

Posting Permissions

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