reorgchk_ix_stats and reorgchk_tb_stats question
in [DB2]
|
From: motormouth on 5 Nov 2009 13:31 Can anyone tell me for certain if these procedures use the current 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!!
From: Ian on 5 Nov 2009 13:56 motormouth wrote: > Can anyone tell me for certain if these procedures use the current > 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!! 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.
From: motormouth on 5 Nov 2009 14:33 On Nov 5, 1:56 pm, Ian <ianb...(a)mobileaudio.com> wrote: > motormouth wrote: > > Can anyone tell me for certain if these procedures use the current > > 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!! > > 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. That is just what I wanted to hear. They are using the current stats option. Thanks for confirming this.
From: Pierre StJ on 6 Nov 2009 12:42 If the proc. has the reorgchk command in it, by default, it will 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. On Nov 5, 1:31 pm, motormouth <metalhog...(a)gmail.com> wrote: > Can anyone tell me for certain if these procedures use the current > 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!!
From: Pierre StJ on 6 Nov 2009 12:49 I guess you can ignore my initial reply. The reorgchk command does update stats. The call sysproc.reorgchk_tb_stats does NOT seem to behave the same way. Apologies, Pierre. On Nov 5, 2:33 pm, motormouth <metalhog...(a)gmail.com> wrote: > On Nov 5, 1:56 pm, Ian <ianb...(a)mobileaudio.com> wrote: > > > > > > > motormouth wrote: > > > Can anyone tell me for certain if these procedures use the current > > > 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!! > > > 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. > > That is just what I wanted to hear. They are using the current stats > option. Thanks for confirming this.- Hide quoted text - > > - Show quoted text -
|
Pages: 1 Prev: redirected restore from DMS/SMS to automatic storage Next: Doubts about CHECK CONSTRAINT |