Wednesday, November 18, 2020

Deleting "n" Rows From a Db2 Table

I regularly receive database- and Db2-related questions via e-mail. And that is great, but I don't always get a chance to respond to everything. If you've sent me a question and I haven't replied, I apologize. But every now and then, I will use one of the e-mail questions in my in-box and write about it in the blog. Today's question is this:

How do you delete N rows from a Db2 table?

Also, how do you retrieve bottom N rows from a Db2 table without sorting the table on key?

And here is my response:

First things first, you need to refresh your knowledge of "relational" database systems and Db2. There really is no such thing as the "top" or "bottom" N rows in a table. Tables are sets of data that have no inherent logical order.

With regard to the result set though, there is a top and a bottom. You can use the FETCH FIRST N ROWS ONLY clause to retrieve only the first N rows, but to retrieve only the bottom N rows is a bit more difficult. For that, you would have to use scrollable cursors.

A scrollable cursor allows you to move back and forth through the results set without first having to read/retrieve all of the rows before. I suggest that you read up on scrollable cursors in the Db2 SQL Reference manual and the Db2 Application Programming manual. All Db2 manuals can be downloaded in Adobe PDF format for free over the IBM web site.

Basically, you would want to FETCH LAST from the scrollable cursor and then loop through with a FETCH PRIOR statement executing the loop N-1 times. That would give you the "bottom" N of any results set -- sorted or not.

As for your other question, I am confused as to why you would want to delete N rows from a table. Doesn't it matter what the data in the rows is? My guess is that you are asking how you would limit a DELETE to a subset of the rows that would apply to the WHERE condition of the DELETE. The answer is, you cannot, at least not without writing some code.

You would have to open a cursor with the same WHERE conditions specifying FOR UPDATE OF. Then you would FETCH and DELETE WHERE CURRENT OF cursor for that row in a loop that occurs N times. Of course, that means you have to write a program to embed that SQL in.

Hope this answer helps...

Tuesday, November 03, 2020

The Tao of Db2!

A couple of years ago now, I published a series of blog posts here on the Db2 Portal blog under the title: The Tao of Db2. The general idea of the series was to offer some high-level, in-the-trenches guidance for Db2 database administrators. But I put the guidance into a series of sketches where a retiring DBA was helping to train his replacement.

I bring this up today because those posts were written to be about "timeless" issues, not specific features and functionality of the DBMS. And I think it is still worthwhile to share those vignettes today, but there are fifteen of them... so today I am publishing this one post that contains links to all 15 of The Tao of Db2 posts!

  1. The Tao of DB2 - Part 1: Achieving Balance and Understanding with DB2
  2. The Tao of DB2 - Part 2: Beyond the Manuals
  3. The Tao of DB2 - Part 3: The New Big Project
  4. The Tao of DB2 - Part 4: Protect the Data!
  5. The Tao of DB2 - Part 5: Build a Backup and Recovery Plan
  6. The Tao of DB2 - Part 6: That Storage Stuff
  7. The Tao of DB2 - Part 7: Dealing with Performance Issues
  8. The Tao of DB2 - Part 8: REORG and Statistics
  9. The Tao of DB2 - Part 9: The Five R's
  10. The Tao of DB2 - Part 10: Dispelling a DB2 Performance Myth
  11. The Tao of DB2 - Part 11: Calm Inaction: a Difficult Lesson to Learn
  12. The Tao of DB2 - Part 12: Dealing with Change
  13. The Tao of DB2 - Part 13: Constantly Learning More
  14. The Tao of DB2 - Part 14: A Short Lesson on Wisdom
  15. The Tao of DB2 - Part 15: Putting it all together!
I hope you enjoy these 15 stories on learning to be a good Db2 DBA and find them to be helpful, or at least entertaining...