Revision: Runstats vs Reorg

In case you are new, we run REORG & then RUNSTAT when we do mass inserts/updates/deletes on DB or individual tables,so that the query runs faster . REORG and RUNSTAT are DB command.

RUNSTATS

1) Updates datadictionary stats. When a query is fired it reads the data dictionary table (dictionary managed) and calculates the cost,etc.

2) Gathers summary information about the characteristics of the data in table spaces and indexes. This information is recorded in the DB2 catalog, and is used by DB2 to select access paths to data during the bind process. It is available to the database administrator for evaluating database design, and determining when table spaces or indexes should be reorganized .

REORG

1) Put all the contiginous blocks together,so data reads / inserts will happen faster as it need not scan the pages. (example is defragmentation in windows)

2) Reorganized a table space to improve access performance and reclaim fragmented space. In addition, the utility can reorganize a single partition of either a partitioned index ora partitioned table space. If you specify REORG UNLOAD ONLY or REORG UNLOAD PAUSE, the REORG utility unloads data in a format acceptable to the LOAD utility of the same DB2 table space.

REORGCHK

do a reorgchk on a certain schema, which will runstat all tables in the schema, whereas doing runstats alone,
then you need to do it table by table.


TIPS

1) Run REORG & then RUNSTAT.

2) It is not necessary to run reorg for all tables :use REORGCHK ON TABLE ALL find which tables require reoganization and run REORG & then RUNSTAT for that specified tables.


EXAMPLE
REORG INDEXES ALL FOR TABLE MYSCHEMA.TABLEA ALLOW WRITE ACCESS;
RUNSTATS ON TABLE MYSCHEMA.TABLEA WITH DISTRIBUTION AND DETAILED INDEXES ALL;
RUNSTATS ON TABLE MYSCHEMA.TABLEA ON ALL COLUMNS ALLOW WRITE ACCESS;


ref