Q
Problem solve Get help with specific problems with your technologies, process and projects.

When do we need to reorganize a table? Part I

When do we need to reorganize a table? We are currently under version 5 and next month we are going for version 7.

Determining when to reorganize a tablespace can be difficult at times.
Fortunately, as long as you have up-to-date RUNSTATS in the DB2 Catalog, you
will have a lot of help. The first step is to gather the pertinent
information from the DB2 Catalog using SQL. You can use the following query
to monitor all tablespaces in each database:

SELECT   T.DBNAME, T.NAME, T.IMPLICIT, T.LOCKMAX, T.BPOOL, T.LOCKRULE,
         T.ERASERULE, T.CLOSERULE, T.PARTITIONS, T.TYPE, T.SEGSIZE,
T.DSSIZE,
         T.NTABLES, T.NACTIVEF, T.PGSIZE, T.MAXROWS, T.ENCODING_SCHEME,
         P.CARDF, P.FARINDREF, P.NEARINDREF, P.PERCACTIVE, P.PERCDROP,
         P.COMPRESS, P.PAGESAVE, P.FREEPAGE, P.PCTFREE, P.STORNAME,
         P.VCATNAME, P.STATSTIME, P.PARTITION, P.GBPCACHE
FROM     SYSIBM.SYSTABLESPACE   T,
         SYSIBM.SYSTABLEPART    P
WHERE    T.NAME = P.TSNAME
AND      T.DBNAME = P.DBNAME
ORDER BY T.DBNAME, T.NAME, P.PARTITION;

Having reported on physical tablespace statistics, the next step is to
analyze physical index statistics. The physical index statistics query
provides a report of all indexes grouped by owner, along with the physical
definitions and aggregate statistics supporting each index:

SELECT   I.CREATOR, I.NAME, I.INDEXTYPE, I.UNIQUERULE, I.CLUSTERING,
         I.CLUSTERED, I.CLUSTERRATIOF*100, P.PQTY, P.SECQTYI, 
         I.FIRSTKEYCARDF, I.FULLKEYCARDF, I.NLEAF, I.NLEVELS, I.PGSIZE,
         I.ERASERULE, I.CLOSERULE, P.CARDF,
         P.FAROFFPOSF, P.LEAFDIST, P.NEAROFFPOSF, P.FREEPAGE,
         P.PCTFREE, P.STORNAME, P.VCATNAME, P.STATSTIME, P.PARTITION
FROM     SYSIBM.SYSINDEXES    I,
         SYSIBM.SYSINDEXPART  P
WHERE    I.NAME = P.IXNAME
AND      I.CREATOR = P.IXCREATOR
ORDER BY I.CREATOR, I.NAME, P.PARTITION;

These queries are quite useful in determining how frequently to reorganize.
The PERCDROP column for tablespaces indicates the percentage of space
occupied by rows from dropped tables. Non-segmented tablespaces cannot
reclaim this space until they are reorganized.

The PAGESAVE column for tablespaces indicates the percentage of pages saved
(per partition) by using ESA compression.
Both the tablespace and index queries display the STATSTIME column. It is
crucial because STATSTIME provides a timestamp indicating when RUNSTATS was
run to produce the statistical information being reported.

Far-off and near-off pages indicate the degree of tablespace or index
disorganization. For non-segmented tablespaces, a page is near off if the
difference between the page and the next one is between 2 and 15 pages
inclusive. For segmented tablespaces, a page is considered near off the
present page if the difference between the two pages is between 2 and the
SEGSIZE*2. A page is far off if the difference is 16 or greater. NEAROFFPOSF
for an index indicates the number of times a different near-off page must be
accessed when accessing all the tablespace rows in indexed order. The
definition of FAROFFPOSF is the same except that far-off page is substituted
for near-off page.
Answer continued in part II of this question

This was last published in November 2002

Dig Deeper on IBM DB2 management

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Please create a username to comment.

-ADS BY GOOGLE

SearchBusinessAnalytics

SearchAWS

SearchContentManagement

SearchOracle

SearchSAP

SearchSQLServer

Close