Using SQL in DB2 to Write REORG Statements

DB2’s automated maintenance does not currently allow us to automate inplace reorgs. Inplace reorgs are more online than other reorgs, and in my experience are heavily used. This means that many DBAs wrap some sort of scripting around reorgs. Working in a variety of client environments, in some of which I may not be allowed to place or use complicated scripting, I tend to use SQL to help me quickly write commands. This works well for reorgs using the REORGCHK_TB_STATS and REORGCHK_IX_STATS built in procedures.

Reorgs are a critical factor in performance, and different environments may require different syntax. Most reorgs reqire share or short exclusive locks, even the inplace ones, so be aware of your syntax.

Calling the Stored Procedures

These built-in stored procedures have been available since fixpack 7 of DB2 8.2 for Linux, Unix, and windows. They do not update statistics, so make sure your statistics are current before running them.

My preferred syntax for calling them is:

db2 "call reorgchk_tb_stats('T', 'ALL')"
db2 "call reorgchk_ix_stats('T', 'ALL')"

Each of these stored procedures creates and populates a session-specific table:

SESSION.TB_STATS
SESSION.IX_STATS

The data in these tables is all the data you would see in a traditional reorgchk, but in a version that is easy to query. It is visible only to the connection that called the stored proceedure(s), and requires the existence of the SYSTOOLSTMPSPACE tablespace – it will create the tablespace if it does not exist.

You can also specify either a single table or a single schema for the stored procedures to collect data on – the above syntax covers all tables, including system tables. See the IBM DB2 Knowledge Center for full details:

reorgchk_tb_stats 

reorgchk_ix_stats

Using SQL to Write Reorg Statements

There is controversy in the DB2 community on what criteria to use to decide when to reorg. For this article, I am not recommending what criteria you should use, but simply taking DB2’s suggestions. I do add a couple of controls to make sure I’m not constantly reorging tiny tables which only show reorg flags because their small size leads to a geometry that will always show a flag, even immediately after being reorged.

One of the beauties of using SQL to do this is that it is cross-platform. I don’t have to come up with a scripting language that works on both Linux/UNIX and Windows or write scripts in different languages.

Here is the SQL that can be used to come up with basic, sane table reorg statements:

select 	'reorg table ' 
		|| rtrim(substr(table_schema,1,11)) || '.' || rtrim(substr(table_name,1,31)) 
		|| case 
			when exists (select 1 
							from syscat.indexes i 
							where i.tabschema=rc.table_schema 
								and i.tabname=rc.table_name 
								and i.indextype='CLUS') 
			then 	' INDEX' 
					|| (select 	rtrim(indschema) 
								|| '.' 
								|| rtrim(indname) 
						from syscat.indexes i 
						where i.tabschema=rc.table_schema 
							and i.tabname=rc.table_name 
							and i.indextype='CLUS') 
			ELSE '' 
			END 
		|| ' INPLACE ALLOW WRITE ACCESS;' 
from session.tb_stats rc 
where 	reorg like '%**%' 
		and card >10 
		and npages>10 
with ur;

What this SQL does is to write a basic reorg table statement for any tables with flags on any of the three table-level formulas(reorg like ‘%**%’). If one of the tables to be reorged has a clustering index, then syntax is included to reorg the table on the clustering index. It does not reorg tables that both have 10 or fewer rows and have 10 or fewer pages. SESSION.TB_STATS has columns for each of the three formulas as well, so it would be easy to specify your own custom value for one of the formulas to decide which tables to reorg.

The syntax is similar for index reorgs:

select 	'reorg indexes all for ' 
		|| rtrim(substr(table_schema,1,11)) || '.' || rtrim(substr(table_name,1,31)) 
		|| ' allow write access ' 
		|| case 
			when substr(reorg,4,1) = '*' 
				then 'CLEANUP ONLY' 
			when substr(reorg,5,1) = '*' 
				then 'CLEANUP ONLY PAGES' 
			else '' end 
		|| ';' 
from session.ix_stats 
where 	reorg like '%**%' 
		and indcard >10 
		and nleaf > 10 
with ur;

In this case, the SQL looks for tables with any flags, and then builds appropriate reorg statements based on which flags are seen. It’s not quite as smart as it should be around F4, but again there is the opportunity to chose actions based on individual formulas if desired instead of just on flags. Again, it eliminates particularly small indexes that may have flags due only to the geometry of small tables.

Caveats

This SQL will generate only the reorg statements. You would not want to execute the statements from an SQL script because inplace reorgs are asynchronous, and all of the inplace reorg commands would be started at the same time – which could easily overwhelm your server. So be cautious about how you execute the resulting statements.

Summary

It’s amazing what you can do with SQL alone, and DB2 keeps making it easier and easier to adminster DB2 with SQL. This SQL can help you generate appropriate reorg statements when you do not have a script available.