Runstats, reorgchk, reorg1, and runstatsrunsats can collect table information or index information. Runstats has no optimization function. However, after updating statistics, the DB2 optimizer can use the latest statistics for optimization. Runstatsontabletbschema. tbnam
Runstats, reorgchk, reorg 1, runstats runsats can collect table information or index information. Runstats has no optimization function. However, after updating statistics, the DB2 optimizer can use the latest statistics for optimization. Runstats on table tbschema. tbnam
Runstats, reorgchk, reorg
1. runstats
Runsats can collect table information or index information. Runstats has no optimization function. However, after updating statistics, the DB2 optimizer can use the latest statistics for optimization.
Runstats on table . Collection table . The table name must be Fully qualified.
2. reorg
A. reorg table . The table is reorganized by restructuring rows to eliminate "Fragmentation" data and compress information. The table name must be Fully qualified.
B,
Another function of reorg is to sort the data in a table in the order of an index keyword, which can reduce the number of I/O queries.
REORG execution can be divided into two types: Table Indexing and no index:
A. For example, the table name is DB2INST1. STAFF and the index name is DB2INST1. ISTAFF.
Reorg table db2inst1. staff index db2inst1. istaff use tempspace1
B. We recommend that you USE the USE parameter in REORG to specify the temporary tablespace used for data rescheduling. Otherwise, REORG will
It is executed in the tablespace where the table is located. If the table has multiple indexes, use the most important INDEX for the INDEX parameter value.
Name.
C. No index on the table:
Reorg table db2inst1. staff use tempspace1
Reorg table sysibm. Tables Ables use tempspace1
Http://weiruan85.javaeye.com/blog/317520
3,
Scheduled runstats and reorg for the db2 System
Q: Regular runstats and reorg
A: call sysproc. ADMIN_CMD can be used in db2 v8.2 and later versions,
Here we will mainly talk about using shell or batch processing in versions earlier than v8.2 to implement the same function.
In earlier versions, DDL operation statements cannot be used in stored procedures! (This is depressing for those who just transferred oracle)
Then we can use the built-in configuration of db2 for automatic maintenance, but what java does is a headache! Especially speed and inexplicable errors!
This Code uses the operating system script to implement this function!
1. windows
As follows:
Next cmd file s. cmd
The content is as follows:
Db2 connect to ccp_dm
Db2-x "select 'runstats on table' | rtrim (tabschema) | '. '| tabname |' on all columns 'from sysstat. tables where card =-1 "> tab. SQL
Db2-f tab. SQL
-- The where condition can be modified.
Then there is a custom task: Use windos to customize the task! Run every week or every month!
This part has been tested and passed!
However, here we only mention runstats, which can also be implemented for reorg!
Http://myfriend2010.itpub.net/post/29012/386779
4. reorgchk
C: \ Documents ents and Settings \ Administrator> db2 reorgchk update statistics on table all
Running RUNSTATS ....
Reorgchk on table all determines whether to reorganize the table. This is useful for automatic runstats execution on all tables.
1) REORGCHK for system tables
Db2 reorgchk update statistics on table system
Use the update statistics parameter to specify that the database executes the RUNSTATS command first.
2) REORGCHK for user tables
Db2 reorgchk update statistics on table user
The following are partial execution results.
Db2 reorgchk update statistics on table user
Run RUNSTATS ....
The db2 reorgchk command is one of the most important and often ignored DB2 adjustment commands. The db2 reorgchk command is ignored because it is not a one-time adjustment item. Since updates are performed on the DB2 database, the table statistics are not up-to-date. The db2 reorgchk command updates important statistics used by the DB2 optimizer. We recommend that you repeat the db2 reorgchk command every 10,000 updates.
Before running the db2 reorgchk command, you should stop the IBM Directory Server to prevent any DB2 queries or updates while executing the command. Although this is optional, database queries and updates may be very slow and may time out.
Note that the performance benefit of running the db2 reorgchk command is immediate. You do not have to restart db2 after the DB2 reorgchk command.
In addition to improving performance, the db2 reorgchk command also reports statistics on all tables and indexes in the database. The db2 reorgchk command also reports statistics about the organization of DB2 tables.
--
Reorg table IFS_CORTASK_UR -- uses refactored rows to remove "Fragmentation" data, compress information, and reorganize the table.
Runstats on table . -- Collect tables .
Reorgchk on table all -- determine whether to reorganize the table. This is useful for automatic runstats execution on all tables.
>>> Reorg and runstats -- are single table optimization and initialization commands:
Runstats on table administrator. test;
Reorg table administrator. test;
--
Http://publib.boulder.ibm.com/tividd/td/ITAME/SC32-1351-00/zh_CN/HTML/am51_perftune66.htm
