kailas.asm
山とシステム管理屋のメモサイト
開始行:
reorgはデータベース内の不要な領域除去やデータの再配置、イ...
:統計情報|オプティマイザーがデータに到達するために最適な...
#contentsx
***データベースの統計情報の更新および統計情報の確認 [#r84...
reorgが必要なテーブルは、reorgchkの結果欄、REORGに「*」が...
db2 => reorgchk update statistics on table all
db2 => reorgchk current statistics on table all
Table statistics:
F1: 100 * OVERFLOW / CARD < 5
F2: 100 * (Effective Space Utilization of Data Pages) > 70
F3: 100 * (Required Pages / Total Pages) > 80
SCHEMA NAME CARD OV NP FP A...
--------------------------------------------------------...
Table: SCHEMA.TABLENAME
SCHEMA TABLENAME 2.7e+07 0 4e+05 4e+05 ...
〜中略〜
Table: SYSIBM.SYSXMLSTATS
SYSIBM SYSXMLSTATS - - - - ...
--------------------------------------------------------...
Index statistics:
F4: CLUSTERRATIO or normalized CLUSTERFACTOR > 80
F5: 100 * (KEYS * (ISIZE + 9) + (CARD - KEYS) * 5) / ((N...
F6: (100 - PCTFREE) * ( FLOOR[ (100 - min(10, pctfree)) ...
F7: 100 * (NUMRIDS DELETED / (NUMRIDS DELETED + CARD)) <...
F8: 100 * (NUM EMPTY LEAFS / NLEAF) < 20
SCHEMA NAME CARD LEAF ELEAF LVLS ISI...
--------------------------------------------------------...
Table: SCHEMA.TABLE
SYSIBM SQL080811073550260 3e+07 2e+05 0 4 ...
〜中略〜
Table: SYSIBM.SYSXMLSTATS
SYSIBM IBM184 - - - - ...
--------------------------------------------------------...
CLUSTERRATIO or normalized CLUSTERFACTOR (F4) will indic...
for indexes that are not in the same sequence as the bas...
indexes are defined on a table, one or more indexes may ...
REORG. Specify the most important index for REORG seque...
Tables defined using the ORGANIZE BY clause and the corr...
indexes have a '*' suffix to their names. The cardinalit...
is equal to the Active blocks statistic of the table.
***tableのreorg [#he12305d]
db2 => reorg table SCHEMA.TABLENAME use 利用するテンポラ...
DB20000I The REORG command completed successfully.
***INDEXの再生成 [#cd28386f]
db2 => reorg indexes all for table SCHEMA.TABLENAME
DB20000I The REORG command completed successfully.
***統計情報の再収集 [#ve74310e]
db2 => runstats on table SCHEMA.TABLENAME on key columns...
DB20000I The RUNSTATS command completed successfully.
***再バインドの実施 [#c5281681]
$ db2rbind DBNAME -l LOGFILE ALL
Rebind done successfully for database 'DBNAME'.
----
#counter
終了行:
reorgはデータベース内の不要な領域除去やデータの再配置、イ...
:統計情報|オプティマイザーがデータに到達するために最適な...
#contentsx
***データベースの統計情報の更新および統計情報の確認 [#r84...
reorgが必要なテーブルは、reorgchkの結果欄、REORGに「*」が...
db2 => reorgchk update statistics on table all
db2 => reorgchk current statistics on table all
Table statistics:
F1: 100 * OVERFLOW / CARD < 5
F2: 100 * (Effective Space Utilization of Data Pages) > 70
F3: 100 * (Required Pages / Total Pages) > 80
SCHEMA NAME CARD OV NP FP A...
--------------------------------------------------------...
Table: SCHEMA.TABLENAME
SCHEMA TABLENAME 2.7e+07 0 4e+05 4e+05 ...
〜中略〜
Table: SYSIBM.SYSXMLSTATS
SYSIBM SYSXMLSTATS - - - - ...
--------------------------------------------------------...
Index statistics:
F4: CLUSTERRATIO or normalized CLUSTERFACTOR > 80
F5: 100 * (KEYS * (ISIZE + 9) + (CARD - KEYS) * 5) / ((N...
F6: (100 - PCTFREE) * ( FLOOR[ (100 - min(10, pctfree)) ...
F7: 100 * (NUMRIDS DELETED / (NUMRIDS DELETED + CARD)) <...
F8: 100 * (NUM EMPTY LEAFS / NLEAF) < 20
SCHEMA NAME CARD LEAF ELEAF LVLS ISI...
--------------------------------------------------------...
Table: SCHEMA.TABLE
SYSIBM SQL080811073550260 3e+07 2e+05 0 4 ...
〜中略〜
Table: SYSIBM.SYSXMLSTATS
SYSIBM IBM184 - - - - ...
--------------------------------------------------------...
CLUSTERRATIO or normalized CLUSTERFACTOR (F4) will indic...
for indexes that are not in the same sequence as the bas...
indexes are defined on a table, one or more indexes may ...
REORG. Specify the most important index for REORG seque...
Tables defined using the ORGANIZE BY clause and the corr...
indexes have a '*' suffix to their names. The cardinalit...
is equal to the Active blocks statistic of the table.
***tableのreorg [#he12305d]
db2 => reorg table SCHEMA.TABLENAME use 利用するテンポラ...
DB20000I The REORG command completed successfully.
***INDEXの再生成 [#cd28386f]
db2 => reorg indexes all for table SCHEMA.TABLENAME
DB20000I The REORG command completed successfully.
***統計情報の再収集 [#ve74310e]
db2 => runstats on table SCHEMA.TABLENAME on key columns...
DB20000I The RUNSTATS command completed successfully.
***再バインドの実施 [#c5281681]
$ db2rbind DBNAME -l LOGFILE ALL
Rebind done successfully for database 'DBNAME'.
----
#counter
ページ名: