Clean up your table! Why, when and how to REORG a Db2 LUW table (introduction)

Why do you tidy your room sometimes? How do you know when it’s the time to do it? Do you do it on a weekly basis or when it’s too cluttered?
This is how I do it.

Every week I check if a clearing is needed. And what kind of clearing.
I consider few things:

• Is there much stuff that I am not going to use anymore?
• Maybe there are entire shelves with useless things?
• Are the things used together (like tape and scissors) in the same place or I need to travel across the whole room to gather these things?
• Are my things sorted in the order I want (e.g. T-shirts according to their colours)?

If anything is not right, then I know, I need to do some cleaning or reorganisation.

Sometimes, I only throw away the whole content of shelves that contain only rubbish — it’s the quickest operation to regain some storage.

If I need to free more storage or every shelf seems to be half clattered with things to get rid off, then I have take out one thing after another and maybe move some useful stuff between shelves, to gain an empty shelf for new stuff.

Sometimes, I don’t find any useless things, but I feel like I am spending the whole day collecting tools to repair my bicycle. In such case I just reorder things. It may be necessary to get another box to store all my tools. I want to have it together, so some other stuff needs to be moved as well.

And finally, if I don’t have any useless stuff and I already have all the tools in a proper toolbox, all clothes in the wardrobe, there is one last thing. The order of things. It’s good to have clothes you use often on the top, not at the bottom of the shelf. And this order may change over time (gloves should be easy to collect during winters, not necessarily during summers). Also, I like to have all pencils ordered according to their colours.

You may think it doesn’t make sense to sort the pencils, because sorting them takes much more time than the time needed to find the correct colour. You’re right! Sometimes reordering things is a wrong decision! You gain less than you pay. But I have thousands of pencils. Each has a different colour. It means, it makes sense in my case.

You may be wondering why am I telling you about all of my tidying habits… Guess:
a) I find it important to share it with a wider audience
b) I just feel good to be expressing myself this way on this blog
c) It has something to do with databases

I suspect I don’t have to mark the correct answer. The „room“ is (of course) a table in a database and a shelf/box is a data page. So, let’s dive into the Db2 world.

How to tell if a table REORG (and what kind of REORG) is needed?

There are many different ways to check it. The best one (especially for newbies) is REORGCHK. It is a simple command that returns some statistics divided into 2 sections:

• one regarding tables,
• one regarding indexes.

REORGCHK command output: table statistics

As you can see, the table statistics have a form of… a table. Each row represents one table (or partition). In all columns, except for the last one, there are some statistics. The last column (‘REORG’) stores just 3 characters, each of them either ‘-‘ or ‘*’. At least one ‘*’ in the given row means that the REORG of the table should be considered.

Ok, but how to get such an output? The syntax of REORGCHK is so simple that I can paste the whole diagram from the IBM Documentation:

REORGCHK command: syntax diagram

And the most basic (but also very useful) version of the command is: „REORGCHK on table all“ — this command will display statistics for all tables in the database.

This post only touches the REORGCHK topic. I am going to write more about this tool and other methods to check if reorg is needed soon.

How to reorganise a table?

So, what if you decide to reorganise one of the tables? Well, you have two options:

• do a classic offline REORG,
• do an inplace REORG.

If you are allowed to make your table unavailable for a while (depending on the table size it might be seconds or days), do the classic REORG — it is faster and more precise.

If the table must be awake 24/7, then there is only one option — the inplace REORG. Inplace REORG can be either:

• full
• or partial (e.g. CLEANUP OVERFLOWS)

Partial REORGs correspond with particular issues and don’t change things that are already fine. Thanks to that they’re quicker, but on the other hand they are not so thorough as full reorgs (even the addressed issue is worse solved).

Here are the commands to run sample REORGs:

• full classic reorg: “REORG TABLE <table-name>”
• full inplace reorg: “REORG TABLE <table-name> INPLACE”
• partial inplace reorg: “REORG TABLE <table-name> INPLACE CLEANUP OVERFLOWS”

Again, this post merely touches the this topic. More about REORGs soon.

To sum up, in order to keep your tables well organised:

1. run „REORGCHK on table all“
2. analyse the output
3. run “REORG TABLE <table-name> (INPLACE)”

See? It’s simple! I wish my room were a database.