*
The moose likes JDBC and the fly likes Delete operation as small chunks on records? Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Murach's Java Servlets and JSP this week in the Servlets forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Delete operation as small chunks on records?" Watch "Delete operation as small chunks on records?" New topic
Author

Delete operation as small chunks on records?

ramakanth boga
Greenhorn

Joined: Jun 30, 2006
Posts: 3
I have a table consists of 10000 records. i want to delete these records, if i tried to delete those records at a time, database fail out of log space.
So, I want to delete these 10000 records as smaller chunks.
i am using DB2.

Can anyone help me to delete the records in smaller chunks?

thanks,
ramakanth
[ July 01, 2006: Message edited by: Bear Bibeault ]
Jeanne Boyarsky
internet detective
Marshal

Joined: May 26, 2003
Posts: 30068
    
149

Ramakanth,
In general:
You could add a where clause to your delete statement on the primary key. For example,
delete from table where myPrimaryKey < X. Then increment X in groups of whatever the database can handle.

In db2:
There is a way of telling the database to commit after a certain number of operations for a bulk operation. I've used it when importing a large data file from the control center. (There's a textfield for the # of records to commit after.) I'm sure this translates into SQL somehow. Try looking in the db2 document for the syntax.


[Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
Blogging on Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, OCAJP, OCPJP beta, TOGAF part 1 and part 2
ramakanth boga
Greenhorn

Joined: Jun 30, 2006
Posts: 3
thanks for the response.
delete from table where myPrimaryKey < X. the value of X is needs to increment untill it reaches the end. do have any syntax to increment the value of X.

bye
ramakanth boga
Jeanne Boyarsky
internet detective
Marshal

Joined: May 26, 2003
Posts: 30068
    
149

Ramakanth,
The value of X depends on the logic for your primary key.

For example, assuming you are using a numeric primary key: You could first do a query to find the maximum value of that key. Then you could increment X in groups of 1000 until you get to that maximum.
ramakanth boga
Greenhorn

Joined: Jun 30, 2006
Posts: 3
thanks for the response. i'll try to do it if any problems comes then i c u.
ramakanth
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Delete operation as small chunks on records?
 
Similar Threads
database fail out of log space
hibernate in a batch program suggestion
how to delete data from huge table.
Could you please go through the problem and suggest solution to solve this problem
Clean up after sessions end