Should we turn on automatic database maintenance for DB2?
There is an IBM page for DB2 called Enabling automatic table and index reorganization. Is this worthwhile setting this and what are the consequences?
We don't have a Database Admin and so a set-and-forget solution would be best for us.
I note that the Jazz manual is unhelpful because it doesn't give any example commands:
Database reorg and runstats: This is a normal database administration task to be completed by a DBA. Whenever a large amount of data is added to a database, a reorg needs to be run and statistics (runstats) needs to be executed against the database tables.
Accepted answer
Thanks - unfortunately we do not have the funds to employ a Database Admin for a single application.
After a lot of reading and experimenting I have found that the following works for us (YMMV):
REM ===============================================================REM -- DOORS NEXT (RM)db2 CONNECT TO rm USER db2admin USING %db2password%REM ------------------------------REM -- Do a runstats so DB2 knows how to work efficientlydb2 -x "select 'runstats on table',substr(rtrim(tabschema)||'.'||rtrim(tabname),1,80),' and indexes all;'from syscat.tables where type = 'T' " > runstats.outdb2 -tvf runstats.outREM ------------------------------REM -- Do a reorgchk so DB2 knows how to reorganise the tablesdb2 -x "select 'reorgchk update statistics on table',substr(rtrim(tabschema)||'.'||rtrim(tabname),1,80),';' from syscat.tables where type = 'T' " > reorgchk.outdb2 -tvf reorgchk.outREM ------------------------------REM -- Do a reorg on all tables but exclude DEADLOCK tablesdb2 -x "select 'reorg table',substr(rtrim(tabschema)||'.'||rtrim(tabname),1,80),';'from syscat.tables where type = 'T' and not tabname like '%DEADLOCK%'" > reorg.outdb2 -tvf reorg.outREM ------------------------------REM -- Do a reorg on all tables indexes but exclude DEADLOCK tablesdb2 -x "select 'reorg indexes all for table',substr(rtrim(tabschema)||'.'||rtrim(tabname),1,80),';'from syscat.tables where type = 'T' and not tabname like '%DEADLOCK%'" > reorgindexes.outdb2 -tvf reorgindexes.outREM ------------------------------REM -- Do a final reorgchk to make sure that stats are now accurate given all the data movement around on pagesdb2 reorgchk update statistics on table allREM ===============================================================REM -- Jazz Team Server (JTS)db2 CONNECT TO jts USER db2admin USING %db2password%REM ------------------------------REM -- Do a runstats so DB2 knows how to work efficientlydb2 -x "select 'runstats on table',substr(rtrim(tabschema)||'.'||rtrim(tabname),1,80),' and indexes all;'from syscat.tables where type = 'T' " > runstats.outdb2 -tvf runstats.outREM ------------------------------REM -- Do a reorgchk so DB2 knows how to reorganise the tablesdb2 -x "select 'reorgchk update statistics on table',substr(rtrim(tabschema)||'.'||rtrim(tabname),1,80),';' from syscat.tables where type = 'T' " > reorgchk.outdb2 -tvf reorgchk.outREM ------------------------------REM -- Do a reorg on all tables but exclude DEADLOCK tablesdb2 -x "select 'reorg table',substr(rtrim(tabschema)||'.'||rtrim(tabname),1,80),';'from syscat.tables where type = 'T' and not tabname like '%DEADLOCK%'" > reorg.outdb2 -tvf reorg.outREM ------------------------------REM -- Do a reorg on all tables indexes but exclude DEADLOCK tablesdb2 -x "select 'reorg indexes all for table',substr(rtrim(tabschema)||'.'||rtrim(tabname),1,80),';'from syscat.tables where type = 'T' and not tabname like '%DEADLOCK%'" > reorgindexes.outdb2 -tvf reorgindexes.outREM ------------------------------REM -- Do a final reorgchk to make sure that stats are now accurate given all the data movement around on pagesdb2 reorgchk update statistics on table allREM ===============================================================REM -- Engineering Test Manager (QM)db2 CONNECT TO qm USER db2admin USING %db2password%REM ------------------------------REM -- Do a runstats so DB2 knows how to work efficientlydb2 -x "select 'runstats on table',substr(rtrim(tabschema)||'.'||rtrim(tabname),1,80),' and indexes all;'from syscat.tables where type = 'T' " > runstats.outdb2 -tvf runstats.outREM ------------------------------REM -- Do a reorgchk so DB2 knows how to reorganise the tablesdb2 -x "select 'reorgchk update statistics on table',substr(rtrim(tabschema)||'.'||rtrim(tabname),1,80),';' from syscat.tables where type = 'T' " > reorgchk.outdb2 -tvf reorgchk.outREM ------------------------------REM -- Do a reorg on all tables but exclude DEADLOCK tablesdb2 -x "select 'reorg table',substr(rtrim(tabschema)||'.'||rtrim(tabname),1,80),';'from syscat.tables where type = 'T' and not tabname like '%DEADLOCK%'" > reorg.outdb2 -tvf reorg.outREM ------------------------------REM -- Do a reorg on all tables indexes but exclude DEADLOCK tablesdb2 -x "select 'reorg indexes all for table',substr(rtrim(tabschema)||'.'||rtrim(tabname),1,80),';'from syscat.tables where type = 'T' and not tabname like '%DEADLOCK%'" > reorgindexes.outdb2 -tvf reorgindexes.outREM ------------------------------REM -- Do a final reorgchk to make sure that stats are now accurate given all the data movement around on pagesdb2 reorgchk update statistics on table allREM ===============================================================REM -- Engineering Workflow Management (CCM)db2 CONNECT TO ccm USER db2admin USING %db2password%REM ------------------------------REM -- Do a runstats so DB2 knows how to work efficientlydb2 -x "select 'runstats on table',substr(rtrim(tabschema)||'.'||rtrim(tabname),1,80),' and indexes all;'from syscat.tables where type = 'T' " > runstats.outdb2 -tvf runstats.outREM ------------------------------REM -- Do a reorgchk so DB2 knows how to reorganise the tablesdb2 -x "select 'reorgchk update statistics on table',substr(rtrim(tabschema)||'.'||rtrim(tabname),1,80),';' from syscat.tables where type = 'T' " > reorgchk.outdb2 -tvf reorgchk.outREM ------------------------------REM -- Do a reorg on all tables but exclude DEADLOCK tablesdb2 -x "select 'reorg table',substr(rtrim(tabschema)||'.'||rtrim(tabname),1,80),';'from syscat.tables where type = 'T' and not tabname like '%DEADLOCK%'" > reorg.outdb2 -tvf reorg.outREM ------------------------------REM -- Do a reorg on all tables indexes but exclude DEADLOCK tablesdb2 -x "select 'reorg indexes all for table',substr(rtrim(tabschema)||'.'||rtrim(tabname),1,80),';'from syscat.tables where type = 'T' and not tabname like '%DEADLOCK%'" > reorgindexes.outdb2 -tvf reorgindexes.outREM ------------------------------REM -- Do a final reorgchk to make sure that stats are now accurate given all the data movement around on pagesdb2 reorgchk update statistics on table allREM ===============================================================REM -- Data Collection Component (DCC)db2 CONNECT TO dcc USER db2admin USING %db2password%REM ------------------------------REM -- Do a runstats so DB2 knows how to work efficientlydb2 -x "select 'runstats on table',substr(rtrim(tabschema)||'.'||rtrim(tabname),1,80),' and indexes all;'from syscat.tables where type = 'T' " > runstats.outdb2 -tvf runstats.outREM ------------------------------REM -- Do a reorgchk so DB2 knows how to reorganise the tablesdb2 -x "select 'reorgchk update statistics on table',substr(rtrim(tabschema)||'.'||rtrim(tabname),1,80),';' from syscat.tables where type = 'T' " > reorgchk.outdb2 -tvf reorgchk.outREM ------------------------------REM -- Do a reorg on all tables but exclude DEADLOCK tablesdb2 -x "select 'reorg table',substr(rtrim(tabschema)||'.'||rtrim(tabname),1,80),';'from syscat.tables where type = 'T' and not tabname like '%DEADLOCK%'" > reorg.outdb2 -tvf reorg.outREM ------------------------------REM -- Do a reorg on all tables indexes but exclude DEADLOCK tablesdb2 -x "select 'reorg indexes all for table',substr(rtrim(tabschema)||'.'||rtrim(tabname),1,80),';'from syscat.tables where type = 'T' and not tabname like '%DEADLOCK%'" > reorgindexes.outdb2 -tvf reorgindexes.outREM ------------------------------REM -- Do a final reorgchk to make sure that stats are now accurate given all the data movement around on pagesdb2 reorgchk update statistics on table allREM Lifecycle Query Engine (LQE)db2 CONNECT TO lqe USER db2admin USING %db2password%REM ------------------------------REM -- Do a runstats so DB2 knows how to work efficientlydb2 -x "select 'runstats on table',substr(rtrim(tabschema)||'.'||rtrim(tabname),1,80),' and indexes all;'from syscat.tables where type = 'T' " > runstats.outdb2 -tvf runstats.outREM ------------------------------REM -- Do a reorgchk so DB2 knows how to reorganise the tablesdb2 -x "select 'reorgchk update statistics on table',substr(rtrim(tabschema)||'.'||rtrim(tabname),1,80),';' from syscat.tables where type = 'T' " > reorgchk.outdb2 -tvf reorgchk.outREM ------------------------------REM -- Do a reorg on all tables but exclude DEADLOCK tablesdb2 -x "select 'reorg table',substr(rtrim(tabschema)||'.'||rtrim(tabname),1,80),';'from syscat.tables where type = 'T' and not tabname like '%DEADLOCK%'" > reorg.outdb2 -tvf reorg.outREM ------------------------------REM -- Do a reorg on all tables indexes but exclude DEADLOCK tablesdb2 -x "select 'reorg indexes all for table',substr(rtrim(tabschema)||'.'||rtrim(tabname),1,80),';'from syscat.tables where type = 'T' and not tabname like '%DEADLOCK%'" > reorgindexes.outdb2 -tvf reorgindexes.outREM ------------------------------REM -- Do a final reorgchk to make sure that stats are now accurate given all the data movement around on pagesdb2 reorgchk update statistics on table allREM LDXdb2 CONNECT TO ldx USER db2admin USING %db2password%REM ------------------------------REM -- Do a runstats so DB2 knows how to work efficientlydb2 -x "select 'runstats on table',substr(rtrim(tabschema)||'.'||rtrim(tabname),1,80),' and indexes all;'from syscat.tables where type = 'T' " > runstats.outdb2 -tvf runstats.outREM ------------------------------REM -- Do a reorgchk so DB2 knows how to reorganise the tablesdb2 -x "select 'reorgchk update statistics on table',substr(rtrim(tabschema)||'.'||rtrim(tabname),1,80),';' from syscat.tables where type = 'T' " > reorgchk.outdb2 -tvf reorgchk.outREM ------------------------------REM -- Do a reorg on all tables but exclude DEADLOCK tablesdb2 -x "select 'reorg table',substr(rtrim(tabschema)||'.'||rtrim(tabname),1,80),';'from syscat.tables where type = 'T' and not tabname like '%DEADLOCK%'" > reorg.outdb2 -tvf reorg.outREM ------------------------------REM -- Do a reorg on all tables indexes but exclude DEADLOCK tablesdb2 -x "select 'reorg indexes all for table',substr(rtrim(tabschema)||'.'||rtrim(tabname),1,80),';'from syscat.tables where type = 'T' and not tabname like '%DEADLOCK%'" > reorgindexes.outdb2 -tvf reorgindexes.outREM ------------------------------REM -- Do a final reorgchk to make sure that stats are now accurate given all the data movement around on pagesdb2 reorgchk update statistics on table allREM ===============================================================REM -- Data Warehouse (DW)db2 CONNECT TO dw USER db2admin USING %db2password%REM ------------------------------REM -- Do a runstats so DB2 knows how to work efficientlydb2 -x "select 'runstats on table',substr(rtrim(tabschema)||'.'||rtrim(tabname),1,80),' and indexes all;'from syscat.tables where type = 'T' " > runstats.outdb2 -tvf runstats.outREM ------------------------------REM -- Do a reorgchk so DB2 knows how to reorganise the tablesdb2 -x "select 'reorgchk update statistics on table',substr(rtrim(tabschema)||'.'||rtrim(tabname),1,80),';' from syscat.tables where type = 'T' " > reorgchk.outdb2 -tvf reorgchk.outREM ------------------------------REM -- Do a reorg on all tables but exclude DEADLOCK tablesdb2 -x "select 'reorg table',substr(rtrim(tabschema)||'.'||rtrim(tabname),1,80),';'from syscat.tables where type = 'T' and not tabname like '%DEADLOCK%'" > reorg.outdb2 -tvf reorg.outREM ------------------------------REM -- Do a reorg on all tables indexes but exclude DEADLOCK tablesdb2 -x "select 'reorg indexes all for table',substr(rtrim(tabschema)||'.'||rtrim(tabname),1,80),';'from syscat.tables where type = 'T' and not tabname like '%DEADLOCK%'" > reorgindexes.outdb2 -tvf reorgindexes.outREM ------------------------------REM -- Do a final reorgchk to make sure that stats are now accurate given all the data movement around on pagesdb2 reorgchk update statistics on table allREM ===============================================================REM -- Global Configuration (GC)db2 CONNECT TO gc USER db2admin USING %db2password%REM ------------------------------REM -- Do a runstats so DB2 knows how to work efficientlydb2 -x "select 'runstats on table',substr(rtrim(tabschema)||'.'||rtrim(tabname),1,80),' and indexes all;'from syscat.tables where type = 'T' " > runstats.outdb2 -tvf runstats.outREM ------------------------------REM -- Do a reorgchk so DB2 knows how to reorganise the tablesdb2 -x "select 'reorgchk update statistics on table',substr(rtrim(tabschema)||'.'||rtrim(tabname),1,80),';' from syscat.tables where type = 'T' " > reorgchk.outdb2 -tvf reorgchk.outREM ------------------------------REM -- Do a reorg on all tables but exclude DEADLOCK tablesdb2 -x "select 'reorg table',substr(rtrim(tabschema)||'.'||rtrim(tabname),1,80),';'from syscat.tables where type = 'T' and not tabname like '%DEADLOCK%'" > reorg.outdb2 -tvf reorg.outREM ------------------------------REM -- Do a reorg on all tables indexes but exclude DEADLOCK tablesdb2 -x "select 'reorg indexes all for table',substr(rtrim(tabschema)||'.'||rtrim(tabname),1,80),';'from syscat.tables where type = 'T' and not tabname like '%DEADLOCK%'" > reorgindexes.outdb2 -tvf reorgindexes.outREM ------------------------------REM -- Do a final reorgchk to make sure that stats are now accurate given all the data movement around on pagesREM -- Do a final reorgchkdb2 reorgchk update statistics on table all
One other answer
Since this is a Db2 feature, the first place I recommend looking is the Db2 docs.
See https://www.ibm.com/docs/en/db2/11.5?topic=organization-automatic-table-index-maintenance and if this doesn't give you what you need you could open an ELM support case asking for more guidance.
Comments
Question to DOORS Next admins, has anyone implemented automatic database maintenance for DB2? What were the pros and cons?
Read this article, then look under the heading 'DB2 tuning' for some suggestions. RequirementsManagement70Performance < Deployment < TWiki (jazz.net)
NOTE that IBM Support is not a replacement for a Database Administrator - if you need help with configuring your ELM deployment you could engage a partner or IBM Expert Labs; your IBM account manager can help you with this.