Delete first 1000 rows in a DB2 table using FETCH FIRST

23 April,2015 by Jack Vamvas

Question: How can I delete rows in batches ?  I’m getting the SQL0964C The transaction log for the database is full  error, and don’t have the authority to increase the LOGSECOND parameter?

How can I use SQL code to DELETE in batches of 1000?

Answer:  The FETCH FIRST commands can limit the amount of rows returned. Combining FETCH FIRST with DELETE  allows you to manage the batch commit process.

Check this example. The SELECT combined with FETCH FIRST statement returns batches of 1000. The DELETE command covers the recordset generated by the SELECT statement

 

DELETE FROM (SELECT * FROM DB1.MY_DB WHERE LOG_DATE < CURRENT TIMESTAMP - 3 DAY FETCH FIRST 1000 ROWS ONLY)
DB20000I  The SQL command completed successfully.


Read More on data management

ADM1823E active log file full - DBA DB2

DM1823E - Log file has reached its saturation point - DBA DB2

DB2 event monitor and snapshot monitor - DBA DB2 - DBA-DB2.com

 

Author: Jack Vamvas(http://www.dba-db2.com)

Share:

Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Working...
Your comment could not be posted. Error type:
Your comment has been saved. Comments are moderated and will not appear until approved by the author. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.

Working...

Post a comment on Delete first 1000 rows in a DB2 table using FETCH FIRST

Comments are moderated, and will not appear until the author has approved them.


dba-db2.com | DB2 Performance Tuning | DBA DB2:Everything | FAQ | Contact | Copyright