Results 1 to 10 of 10
  1. #1
    Join Date
    Jan 2005
    Posts
    1

    Bulk Delete from UDB DB2 8.1

    Hi,
    We are using UDB DB2 8.1 for our Data warehousing.
    There is a particular table which gets inserted with millions of records each day.
    Suppose there is a problem in the data loaded for a particular day, we have to go ahead and delete all the records for the day based on some day key and load them again.
    we cannot delete all the records at one go since the transaction log gets full which needs DBA attention.
    Currently we are using an unix script which will fetch 5000 rows at one time and delete them.
    But this selection and deletion process takes the whole day.
    Is there any quick way/function/utility for deleting all the records without making the transaction log full.

    Thanks.

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,734
    Make sure the table is originally created with "not logged initially". In the first step of the delete script, alter the table to be "not logged initially", then run the delete, then execute a commit. The script must be run with auto-commit off (use the +c option on the command line processor).
    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 2001
    Location
    UK
    Posts
    4,649
    As you are in V8.1, a table need not be created with Not logged initially option ... The table can be altered for not logged initially anyway ...

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

  4. #4
    Join Date
    Aug 2004
    Location
    London, UK
    Posts
    31

    db2 import from empty.del of del replace into <your table>

    Create an empty file and run the db2 command in the title above. Works for any DB2 version

  5. #5
    Join Date
    May 2003
    Location
    USA
    Posts
    5,734
    I don't think he wants to delete all the rows. Just delete rows with a specified date range ("all the records for the day based on some day key").
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  6. #6
    Join Date
    Aug 2004
    Location
    London, UK
    Posts
    31

    Perhaps

    But it's common practise to load data from source files in to staging tables, verify their contents and then transfer them to the main data warehouse tables.

  7. #7
    Join Date
    May 2003
    Location
    USA
    Posts
    5,734
    Quote Originally Posted by jdey123
    But it's common practise to load data from source files in to staging tables, verify their contents and then transfer them to the main data warehouse tables.
    You are really fishing. Your answer was out of context. Good idea if all the rows are to be deleted, but clearly not relevant here.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  8. #8
    Join Date
    Aug 2004
    Posts
    2

    Another doubt reg bulk delete

    Hi All,
    Thanks for ur response. i appreciate it.
    Is there any other intricacies involved in using this parameter "NOT LOGGED INITIALLY".
    Suppose i alter the table and use it, will it be removed when i do a commit (manual or database) or how do i switch back to logging?

    Thanks.

  9. #9
    Join Date
    May 2003
    Location
    USA
    Posts
    5,734
    As soon as you do a commit, logging will start on all subsequent updates (not logged intially is turned off). The alter, delete, and commit statement must be in the same unit of work, so that is why auto-commit must be turned off.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  10. #10
    Join Date
    Aug 2004
    Posts
    2
    Thank you very much. That was very useful.
    Hope to implement it.

Posting Permissions

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