Automate your REORGs in Db2 LUW (simple script inside)

Author:

Platform:

Published on:

Kacper Kubica

LUW

August 13, 2025

Running REORGs is one of the cyclic maintenance jobs you run against your tables. The best time to reorganise a table is the lowest traffic time, which is usually night or weekend (or both). It means that executing a REORG manually is quite problematic. Why not just include REORGs of all tables in a script and insert it to the crontab, so that every table is reorganised, let’s say, every sunday?

Hmm… nice try, but REORGs last long and usually most tables don’t require a REORG, ergo reorganising all the tables on the server is not a good idea (euphemistically speaking). It means, we need an “intelligent” script that will reorganise only those tables that require it.

If you want to grasp basics about REORGing, take a look at the following articles of mine:

Now, let’s build the script! I am going to start simply, by creating a script that checks if any of my tables has an asterisk in the REORG column of the table statistics in the REORGCHK output.

The goal of my script is just to show you how to retrieve required information from Db2 database. This is not a production script. The real script should be dynamic, it means it shouldn’t have hardcoded schemas and so on. It should check what tables and what schemas reside in the database and then analyse them one by one. For my case, I am assuming that PDUG_2024 is the only schema that will ever exist in my environment. The script is going to be written in bash with many SQL statements and consist of two parts:

collecting the list of tables to reorganise,
reorganising tables one by one.

Due to the fact that I want to use REORGCHK output as the input to further analysis instead of just displaying it on the screen, I use REORGCHK_TB_STATS procedure (instead of REORGCHK tool). Here is how I am going to call it:

db2 “call sysproc.reorgchk_tb_stats(’S’,’PDUG_2024’)“

The parameters it takes are the following:

firstly ’S’, which means that I want to check an entire schema (alternatively you can pass letter ’T’, which stands for ‘table’);
and secondly ‘PDUG_2024’, which is the name of my schema

As the result, I have the REORGCHK’s table statistics saved in a form of a table. This table is called ‘TB_STATS’ and it resides in schema ‘SESSION’. Therefore, after calling the procedure, I can check its results by SELECTing the TB_STATS table, like this:

db2 “select * from SESSION.TB_STATS“

Due to the fact that I need only table names and values from the REORG column, I shaped my SELECT query as follows:

db2 “select substr(table_name,1,32) as TABLE, REORG from SESSION.TB_STATS“

Moreover, I am interested only in the tables that require a reorg, therefore I filtered rows based on the REORG column value. Also, I need only table names — REORG column is needless if the tables are already filtered. So, my final query looks like this:

db2 “select substr(table_name,1,32) as TABLE from SESSION.TB_STATS where REORG like ‘%*%’”

The result of this SELECT query would be following:

OK, we’ve got a query that gives us a one-column table with names of tables that require a REORG. What’s next? Now, we need to save the output of the query in a form of a temporary table, for example:

db2 “create table PDUG_2024.TABLES_TO_REORG as (select substr(table_name,1,32) as TABLE from SESSION.TB_STATS where REORG like ‘%*%’) with data“

Now I need a loop that retrieves table names sequentially and insert each of them to the REORG statement. Hmm… how am I supposed to retrieve each record one by one if I don’t have them numbered?

One of the solutions to this problem is to create another column with their unique IDs. In order not to make life too hard, the IDs will start from ‘1’ and grow by 1. What’s the easiest way to add the ID column? The ROW_NUMBER() function. My entire (not very short) SQL expression looks like this:

db2 “create table PDUG_2024.TABLES_TO_REORG as (select ROW_NUMBER() over (order by table_name asc) as ID, substr(table_name,1,32) as TABLE from SESSION.TB_STATS where REORG like ‘%*%’) with data“

As you can see below, the IDs are assigned to the tables in the alphabetical order.

Great! Having the list of tables to reorganise with their IDs, let’s run the REORGs. As I mentioned, I am going to use a loop. To do so, I need to know how many iterations are needed. This information is retrieved by the following statement:

NUMBER_OF_TABLES_TO_REORG=$(db2 -x „select count(*) from TABLES_TO_REORG“);

The ‘-x’ parameter is necessary. Otherwise the output would be a string, not a single integer. Now, let’s start building the loop!

At first, the counter:

CNT=1;

Then, the “while” condition comes. The loop is repeated until the counter is smaller or equal to the number of tables to reorg:

while [ $CNT -le $NUMBER_OF_TABLES_TO_REORG ];

The next step is retrieving the table name that has an ID equaling the counter:

TABLE_NAME=$(db2 -x “select TABLE from TABLES_TO_REORG where ID=$CNT”);

The SELECT output often leaves some whitespace characters inside the string variable, therefore in the next step the “TABLE_NAME” variable is cleared from possible whitespace:

TABLE_NAME=”$(echo -e “${TABLE_NAME}” | tr -d ‘[:space:]’)”;

Now, having the table name saved, let’s prepare the REORG TABLE command! This is done by the following assignment:

REORG_TABLE_STRING=”reorg table PDUG_2024.${TABLE_NAME}“;

And finally, I can execute the REORG command:

db2 “$REORG_TABLE_STRING”;

Not to forget: I want my counter to be increased by 1 each iteration, so that I have all my tables reorganised starting from ID=1 and ending with ID=NUMBER_OF_TABLES_TO_REORG. Therefore I increase it at the end of the loop:

((CNT++));

At last, the while loop must end with simple:

done

Except for the main part of the script I need some preparation and clean-up. By preparation I mean connecting to my database…

db2 “connect to testdb“;

…and declaring variables I am going to use:

declare NUMBER_OF_TABLES_TO_REORG int; declare CNT int; declare REORG_TABLE_STRING string; declare TABLE_NAME string;

The clean-up is just a one line of code. I drop the temporary table I created for the sake of the script:

db2 “drop table TABLES_TO_REORG”;

And of course there will be a ‘shebang’ at the beginning, because it’s a bash script. In my case it looks like this:

#!/bin/bash

The full script looks as follows:

#!/bin/bash db2 “connect to testdb”; declare NUMBER_OF_TABLES_TO_REORG int; declare CNT int; declare REORG_TABLE_STRING string; declare TABLE_NAME string; db2 “call sysproc.reorgchk_tb_stats(‘S’,’PDUG_2024')”; db2 “create table TABLES_TO_REORG as (select ROW_NUMBER() over (order by table_name asc) as ID, substr(table_name,1,32) as TABLE from SESSION.TB_STATS where REORG like ‘%*%’) with data”; NUMBER_OF_TABLES_TO_REORG=$(db2 -x “select count(*) from TABLES_TO_REORG”); CNT=1; while [ $CNT -le $NUMBER_OF_TABLES_TO_REORG ]; do TABLE_NAME=$(db2 -x “select TABLE from TABLES_TO_REORG where ID=$CNT”); TABLE_NAME=”$(echo -e “${TABLE_NAME}” | tr -d ‘[:space:]’)”; REORG_TABLE_STRING=”reorg table PDUG_2024.${TABLE_NAME}”; db2 “$REORG_TABLE_STRING”; ((CNT++)); done db2 “drop table TABLES_TO_REORG”;

Let’s test it!

Here is the REORGCHK output, table statistics containing all the tables from my PDUG_2024 schema:

‍

Exactly six of them have at least one ‘*’ in the REORG column. Now, let’s run the script.

./automated_reorg.sh > automated_reorg.out

And run the REORGCHK again:

‍

Look! It worked!

***

Is my script good?

NO!

It is not. It is just a whatever script to show you how to automate your reorgs. What’s wrong with it?

It has hardcoded database and schema names.
It takes very straightforward method of deciding if a given table needs a reorg (just an ‘*’ in the REORG column)
It is not properly tested
and probably much more…

Even though it’s good enough for my blog and my conference speeches, take it only as a guideline or something-to-start-with to implement automated reorgs for your environment.

Was it easy to write the script?
NO again!
I am terrible at bash :D

‍