If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > Database Server Software > DB2 > Bulk Delete from UDB DB2 8.1

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old
Registered User
 
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.
Reply With Quote
  #2 (permalink)  
Old
Registered User
 
Join Date: May 2003
Location: USA
Posts: 5,702
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
Reply With Quote
  #3 (permalink)  
Old
Super Moderator
 
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.
Reply With Quote
  #4 (permalink)  
Old
Registered User
 
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
Reply With Quote
  #5 (permalink)  
Old
Registered User
 
Join Date: May 2003
Location: USA
Posts: 5,702
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
Reply With Quote
  #6 (permalink)  
Old
Registered User
 
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.
Reply With Quote
  #7 (permalink)  
Old
Registered User
 
Join Date: May 2003
Location: USA
Posts: 5,702
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
Reply With Quote
  #8 (permalink)  
Old
Registered User
 
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.
Reply With Quote
  #9 (permalink)  
Old
Registered User
 
Join Date: May 2003
Location: USA
Posts: 5,702
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
Reply With Quote
  #10 (permalink)  
Old
Registered User
 
Join Date: Aug 2004
Posts: 2
Thank you very much. That was very useful.
Hope to implement it.
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On