Is there a way to reorg ALL tables and indexes in a db besides running reorg on each table one by one and then tables indexes? Our db has more than 700 tables so running reorgs on all of them one by one would be time consuming (not to say ridiculous ). Same with runstats...

May be there is a script doing it (reading table names of the db from system catalog and than doing reorgs and runstats on them)? I checked the library (searched on reorg) and did not find anything.

Thanks in advance

In my opinion, it will indeed be ridiculous, but for a different reason. It is highly unlikely that you need to reorg all 700 tables at once. Besides, only you can decide which will be the best clustering index, if any, for each table - you cannot automate that decision.

You would have to analyze the results of REORGCHK and make appropriate decisions. Nobody said being a DBA is easy...

As for statistics, you could use REORGCHK UPDATE STATISTICS FOR SCHEMA ... to, well, update statistics for all tables in a schema.

See if this hepls............

Help - WebSphere Commerce version 5.6.1.4

try this sql and save the result as a file and then "db2 -tf filename"

select 'REORG INDEXES ALL FOR TABLE '||tabschema||'.'||tabname||';' from syscat.tables where tabschema='xxx' and type='T'
union all
select 'REORG TABLE '||tabschema||'.'||tabname||';' from syscat.tables where tabschema='xxx' and type='T';

I would not run the script as mentioned, yes use the SQL to generate the list of objects to reorg. Reorg is an asynchronous command, so you need to add logic to ensure the prior reorg is complete prior to submitting the next one. Otherwise you are reorging your entire db at the same time, if your machine can handle that workload, then by all means go for it.

Only inplace reorg is asynchronos, AFAIK

SCHEMA NAME CARD LEAF ELEAF LVLS ISIZE NDEL KEYS F4 F5 F6 F7 F8 REORG
Table: DBA.XXS_XXXXXXXXX
DBA xxxXNTYPEIX 7560 13 0 2 4 0 64 100 79 9 0 0 -----
IPLF xxxXNIX 7560 92 0 3 34 0 7560 75 89 86 0 0 *----
IPLF TXNPOSTIX 7560 12 0 2 12 0 41 95 87 9 0 0 -----
SYSIBM SQL050621141422620 7560 97 0 3 37 0 7560 75 90 75 0 0 *----

Table: DBA.XX_XXXXXX
DBA XXXTXNIX11 235 6 0 2 72 0 235 95 95 18 0 0 -----
DBA XXXTXNIX22 235 1 0 1 42 0 25 100 - - 0 0 -----
DBA XXXTXNIX33 235 2 0 2 42 0 105 50 150 59 0 0 *----

So in the above example should I do anything with IPLF.xxxXNIX, SYSIBM.SQL050621141422620, DBA.XXXTXNIX33 indexes if there is a * in the REORG col? What is SYSIBM.SQL050621141422620 anyway?

it is a key/index that has been created implicitely for you
eg alter table .. primary key ... will create an index for you..if not exist