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
Dig Deeper on IBM DB2 management
Related Q&A from Craig S. Mullins
Our expert suggests that the best way to pass the SAP BW exam is to have hands-on job experience. But that's not all he has to say about ... Continue Reading
To export data from a DB2 table to a flat file, you need to run an export specifying the proper file format. The export utility exports data from a ... Continue Reading
Craig Mullins recommends two specific resources for learning how to create and support Binary Large Objects (BLOB) in DB2. Continue Reading
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.