 |
|
| Computers Forum Index » Computer - Databases - IBM DB2 » reorgchk_ix_stats and reorgchk_tb_stats question... |
|
Page 1 of 1 |
|
| Author |
Message |
| motormouth... |
 Posted: Thu Nov 05, 2009 6:31 pm |
|
|
|
Guest
|
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!! |
|
|
| Back to top |
|
|
|
| motormouth... |
| Posted: Thu Nov 05, 2009 7:33 pm |
|
|
|
Guest
|
On Nov 5, 1:56 pm, Ian <ianb... at (no spam) mobileaudio.com> wrote:
Quote: 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. |
|
|
| Back to top |
|
|
|
| Ian... |
| Posted: Thu Nov 05, 2009 11:56 pm |
|
|
|
Guest
|
motormouth wrote:
Quote: 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. |
|
|
| Back to top |
|
|
|
| Pierre StJ... |
| Posted: Fri Nov 06, 2009 5:42 pm |
|
|
|
Guest
|
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... at (no spam) gmail.com> wrote:
Quote: 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!! |
|
|
| Back to top |
|
|
|
| Pierre StJ... |
| Posted: Fri Nov 06, 2009 5:49 pm |
|
|
|
Guest
|
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... at (no spam) gmail.com> wrote:
Quote: On Nov 5, 1:56 pm, Ian <ianb... at (no spam) 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 - |
|
|
| Back to top |
|
|
|
|
|
All times are GMT
The time now is Tue Dec 01, 2009 12:08 pm
|
|