reorgchk_ix_stats and reorgchk_tb_stats question
motormouth
statistics or update statistics option on the reorgchk command?
My testing seems to indicate it is using update statistics, but my
test database is very small in volume, so I'm not sure. I see this
question has been asked in the past, but I didn't see any response.
If it is using update stats, can anyone shed light on how it's
performing the runstats? If I have a profile defined for a table,
will it use that?
Thanks!!
Ian
They do not update statistics. They only read the catalog.
Proof:
$ db2 "select stats_time from syscat.tables where \
tabschema = 'DB2INST1' and tabname = 'EMPLOYEE'"
STATS_TIME
--------------------------
-
1 record(s) selected.
$ db2 "call sysproc.reorgchk_tb_stats('T','DB2INST1.EMPLOYEE')"
Result set 1
--------------
TABLE_SCHEMA
TABLE_NAME
DATAPARTITIONNAME
CARD OVERFLOW
NPAGES FPAGES ACTIVE_BLOCKS
TSIZE F1 F2 F3 REORG
--------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------
-------------------- -------------------- --------------------
-------------------- -------------------- --------------------
----------- ----------- ----------- -----
DB2INST1
EMPLOYEE
-1
-1 -1 -1 -1
-1 -1 -1 -1 ---
1 record(s) selected.
Return Status = 0
$ db2 "select stats_time from syscat.tables where \
tabschema = 'DB2INST1' and tabname = 'EMPLOYEE'"
STATS_TIME
--------------------------
-
1 record(s) selected.
motormouth
That is just what I wanted to hear. They are using the current stats
option. Thanks for confirming this.
Pierre StJ
collect statistics.
Look at the reorgchk command and you will see that the default option
is to runstats to uddate, the other option, not default, is to use
current statistics.
You don't show what command or API is used in the procs. so I assume
you use fefaults, so stats will be collected.
The stats will be run on each oblject as they were run before. I f you
have distribution statistics already collected they will be
recollected. If you use a profile, it will be used automatically.
By the way, this is also the way DB2 will behave if you LOAD ...
STATISTICS YES....
Hope this helps, Pierre.
Pierre StJ
The reorgchk command does update stats.
The call sysproc.reorgchk_tb_stats does NOT seem to behave the same
way.
Apologies, Pierre.
> > --------------------------------------------------------------------------------------------------------------------------------
> > --------------------------------------------------------------------------------------------------------------------------------
> > -------------------- -------------------- --------------------
> > -------------------- -------------------- --------------------
> > ----------- ----------- ----------- -----
> > DB2INST1
> > EMPLOYEE
>
> > -1
> > -1 -1 -1 -1
> > -1 -1 -1 -1 ---
>
> > 1 record(s) selected.
>
> > Return Status = 0
>
> > $ db2 "select stats_time from syscat.tables where \
> > tabschema = 'DB2INST1' and tabname = 'EMPLOYEE'"
>
> > STATS_TIME
> > --------------------------
> > -
>
> > 1 record(s) selected.
>
> That is just what I wanted to hear. They are using the current stats
>
> - Show quoted text -