It's all about the answers!

Ask a question
QuestionsTagsUsersBadges
Guidelines | FAQ
Follow this question
Question details

×7,479
×4,712
×1,308
×83

question asked: Oct 01 '23, 8:45 a.m.
question was seen: 1,370 times
last updated: Feb 04, 8:28 p.m.

Related questions

Should we turn on automatic database maintenance for DB2?

0
Edward Chan (55●5) | asked Oct 01 '23, 8:45 a.m.

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. 
Is there an example of the commands and XML files that can be used to enable automatic table and index reorganization for DB2?

Accepted answer

0
permanent link
Edward Chan (55●5) | answered Feb 04, 8:28 p.m.

 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 efficiently
db2 -x "select 'runstats on table',substr(rtrim(tabschema)||'.'||rtrim(tabname),1,80),' and indexes all;'from syscat.tables where type = 'T' " > runstats.out
db2 -tvf runstats.out
REM ------------------------------
REM -- Do a reorgchk so DB2 knows how to reorganise the tables
db2 -x "select 'reorgchk update statistics on table',substr(rtrim(tabschema)||'.'||rtrim(tabname),1,80),';' from syscat.tables where type = 'T' " > reorgchk.out
db2 -tvf reorgchk.out
REM ------------------------------
REM -- Do a reorg on all tables but exclude DEADLOCK tables
db2 -x "select 'reorg table',substr(rtrim(tabschema)||'.'||rtrim(tabname),1,80),';'from syscat.tables where type = 'T' and not tabname like '%DEADLOCK%'" > reorg.out
db2 -tvf reorg.out
REM ------------------------------
REM -- Do a reorg on all tables indexes but exclude DEADLOCK tables
db2 -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.out
db2 -tvf reorgindexes.out
REM ------------------------------
REM -- Do a final reorgchk to make sure that stats are now accurate given all the data movement around on pages
db2 reorgchk update statistics on table all
 
REM ===============================================================
REM -- Jazz Team Server (JTS)
db2 CONNECT TO jts USER db2admin USING %db2password%
REM ------------------------------
REM -- Do a runstats so DB2 knows how to work efficiently
db2 -x "select 'runstats on table',substr(rtrim(tabschema)||'.'||rtrim(tabname),1,80),' and indexes all;'from syscat.tables where type = 'T' " > runstats.out
db2 -tvf runstats.out
REM ------------------------------
REM -- Do a reorgchk so DB2 knows how to reorganise the tables
db2 -x "select 'reorgchk update statistics on table',substr(rtrim(tabschema)||'.'||rtrim(tabname),1,80),';' from syscat.tables where type = 'T' " > reorgchk.out
db2 -tvf reorgchk.out
REM ------------------------------
REM -- Do a reorg on all tables but exclude DEADLOCK tables
db2 -x "select 'reorg table',substr(rtrim(tabschema)||'.'||rtrim(tabname),1,80),';'from syscat.tables where type = 'T' and not tabname like '%DEADLOCK%'" > reorg.out
db2 -tvf reorg.out
REM ------------------------------
REM -- Do a reorg on all tables indexes but exclude DEADLOCK tables
db2 -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.out
db2 -tvf reorgindexes.out
REM ------------------------------
REM -- Do a final reorgchk to make sure that stats are now accurate given all the data movement around on pages
db2 reorgchk update statistics on table all
 
REM ===============================================================
REM -- Engineering Test Manager (QM)
db2 CONNECT TO qm USER db2admin USING %db2password%
REM ------------------------------
REM -- Do a runstats so DB2 knows how to work efficiently
db2 -x "select 'runstats on table',substr(rtrim(tabschema)||'.'||rtrim(tabname),1,80),' and indexes all;'from syscat.tables where type = 'T' " > runstats.out
db2 -tvf runstats.out
REM ------------------------------
REM -- Do a reorgchk so DB2 knows how to reorganise the tables
db2 -x "select 'reorgchk update statistics on table',substr(rtrim(tabschema)||'.'||rtrim(tabname),1,80),';' from syscat.tables where type = 'T' " > reorgchk.out
db2 -tvf reorgchk.out
REM ------------------------------
REM -- Do a reorg on all tables but exclude DEADLOCK tables
db2 -x "select 'reorg table',substr(rtrim(tabschema)||'.'||rtrim(tabname),1,80),';'from syscat.tables where type = 'T' and not tabname like '%DEADLOCK%'" > reorg.out
db2 -tvf reorg.out
REM ------------------------------
REM -- Do a reorg on all tables indexes but exclude DEADLOCK tables
db2 -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.out
db2 -tvf reorgindexes.out
REM ------------------------------
REM -- Do a final reorgchk to make sure that stats are now accurate given all the data movement around on pages
db2 reorgchk update statistics on table all
 
REM ===============================================================
REM -- Engineering Workflow Management (CCM)
db2 CONNECT TO ccm USER db2admin USING %db2password%
REM ------------------------------
REM -- Do a runstats so DB2 knows how to work efficiently
db2 -x "select 'runstats on table',substr(rtrim(tabschema)||'.'||rtrim(tabname),1,80),' and indexes all;'from syscat.tables where type = 'T' " > runstats.out
db2 -tvf runstats.out
REM ------------------------------
REM -- Do a reorgchk so DB2 knows how to reorganise the tables
db2 -x "select 'reorgchk update statistics on table',substr(rtrim(tabschema)||'.'||rtrim(tabname),1,80),';' from syscat.tables where type = 'T' " > reorgchk.out
db2 -tvf reorgchk.out
REM ------------------------------
REM -- Do a reorg on all tables but exclude DEADLOCK tables
db2 -x "select 'reorg table',substr(rtrim(tabschema)||'.'||rtrim(tabname),1,80),';'from syscat.tables where type = 'T' and not tabname like '%DEADLOCK%'" > reorg.out
db2 -tvf reorg.out
REM ------------------------------
REM -- Do a reorg on all tables indexes but exclude DEADLOCK tables
db2 -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.out
db2 -tvf reorgindexes.out
REM ------------------------------
REM -- Do a final reorgchk to make sure that stats are now accurate given all the data movement around on pages
db2 reorgchk update statistics on table all
 
REM ===============================================================
REM -- Data Collection Component (DCC)
db2 CONNECT TO dcc USER db2admin USING %db2password%
REM ------------------------------
REM -- Do a runstats so DB2 knows how to work efficiently
db2 -x "select 'runstats on table',substr(rtrim(tabschema)||'.'||rtrim(tabname),1,80),' and indexes all;'from syscat.tables where type = 'T' " > runstats.out
db2 -tvf runstats.out
REM ------------------------------
REM -- Do a reorgchk so DB2 knows how to reorganise the tables
db2 -x "select 'reorgchk update statistics on table',substr(rtrim(tabschema)||'.'||rtrim(tabname),1,80),';' from syscat.tables where type = 'T' " > reorgchk.out
db2 -tvf reorgchk.out
REM ------------------------------
REM -- Do a reorg on all tables but exclude DEADLOCK tables
db2 -x "select 'reorg table',substr(rtrim(tabschema)||'.'||rtrim(tabname),1,80),';'from syscat.tables where type = 'T' and not tabname like '%DEADLOCK%'" > reorg.out
db2 -tvf reorg.out
REM ------------------------------
REM -- Do a reorg on all tables indexes but exclude DEADLOCK tables
db2 -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.out
db2 -tvf reorgindexes.out
REM ------------------------------
REM -- Do a final reorgchk to make sure that stats are now accurate given all the data movement around on pages
db2 reorgchk update statistics on table all
 
REM Lifecycle Query Engine (LQE)
db2 CONNECT TO lqe USER db2admin USING %db2password%
REM ------------------------------
REM -- Do a runstats so DB2 knows how to work efficiently
db2 -x "select 'runstats on table',substr(rtrim(tabschema)||'.'||rtrim(tabname),1,80),' and indexes all;'from syscat.tables where type = 'T' " > runstats.out
db2 -tvf runstats.out
REM ------------------------------
REM -- Do a reorgchk so DB2 knows how to reorganise the tables
db2 -x "select 'reorgchk update statistics on table',substr(rtrim(tabschema)||'.'||rtrim(tabname),1,80),';' from syscat.tables where type = 'T' " > reorgchk.out
db2 -tvf reorgchk.out
REM ------------------------------
REM -- Do a reorg on all tables but exclude DEADLOCK tables
db2 -x "select 'reorg table',substr(rtrim(tabschema)||'.'||rtrim(tabname),1,80),';'from syscat.tables where type = 'T' and not tabname like '%DEADLOCK%'" > reorg.out
db2 -tvf reorg.out
REM ------------------------------
REM -- Do a reorg on all tables indexes but exclude DEADLOCK tables
db2 -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.out
db2 -tvf reorgindexes.out
REM ------------------------------
REM -- Do a final reorgchk to make sure that stats are now accurate given all the data movement around on pages
db2 reorgchk update statistics on table all
 
REM LDX
db2 CONNECT TO ldx USER db2admin USING %db2password%
REM ------------------------------
REM -- Do a runstats so DB2 knows how to work efficiently
db2 -x "select 'runstats on table',substr(rtrim(tabschema)||'.'||rtrim(tabname),1,80),' and indexes all;'from syscat.tables where type = 'T' " > runstats.out
db2 -tvf runstats.out
REM ------------------------------
REM -- Do a reorgchk so DB2 knows how to reorganise the tables
db2 -x "select 'reorgchk update statistics on table',substr(rtrim(tabschema)||'.'||rtrim(tabname),1,80),';' from syscat.tables where type = 'T' " > reorgchk.out
db2 -tvf reorgchk.out
REM ------------------------------
REM -- Do a reorg on all tables but exclude DEADLOCK tables
db2 -x "select 'reorg table',substr(rtrim(tabschema)||'.'||rtrim(tabname),1,80),';'from syscat.tables where type = 'T' and not tabname like '%DEADLOCK%'" > reorg.out
db2 -tvf reorg.out
REM ------------------------------
REM -- Do a reorg on all tables indexes but exclude DEADLOCK tables
db2 -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.out
db2 -tvf reorgindexes.out
REM ------------------------------
REM -- Do a final reorgchk to make sure that stats are now accurate given all the data movement around on pages
db2 reorgchk update statistics on table all
 
REM ===============================================================
REM -- Data Warehouse (DW)
db2 CONNECT TO dw USER db2admin USING %db2password%
REM ------------------------------
REM -- Do a runstats so DB2 knows how to work efficiently
db2 -x "select 'runstats on table',substr(rtrim(tabschema)||'.'||rtrim(tabname),1,80),' and indexes all;'from syscat.tables where type = 'T' " > runstats.out
db2 -tvf runstats.out
REM ------------------------------
REM -- Do a reorgchk so DB2 knows how to reorganise the tables
db2 -x "select 'reorgchk update statistics on table',substr(rtrim(tabschema)||'.'||rtrim(tabname),1,80),';' from syscat.tables where type = 'T' " > reorgchk.out
db2 -tvf reorgchk.out
REM ------------------------------
REM -- Do a reorg on all tables but exclude DEADLOCK tables
db2 -x "select 'reorg table',substr(rtrim(tabschema)||'.'||rtrim(tabname),1,80),';'from syscat.tables where type = 'T' and not tabname like '%DEADLOCK%'" > reorg.out
db2 -tvf reorg.out
REM ------------------------------
REM -- Do a reorg on all tables indexes but exclude DEADLOCK tables
db2 -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.out
db2 -tvf reorgindexes.out
REM ------------------------------
REM -- Do a final reorgchk to make sure that stats are now accurate given all the data movement around on pages
db2 reorgchk update statistics on table all
 
REM ===============================================================
REM -- Global Configuration (GC)
db2 CONNECT TO gc USER db2admin USING %db2password%
REM ------------------------------
REM -- Do a runstats so DB2 knows how to work efficiently
db2 -x "select 'runstats on table',substr(rtrim(tabschema)||'.'||rtrim(tabname),1,80),' and indexes all;'from syscat.tables where type = 'T' " > runstats.out
db2 -tvf runstats.out
REM ------------------------------
REM -- Do a reorgchk so DB2 knows how to reorganise the tables
db2 -x "select 'reorgchk update statistics on table',substr(rtrim(tabschema)||'.'||rtrim(tabname),1,80),';' from syscat.tables where type = 'T' " > reorgchk.out
db2 -tvf reorgchk.out
REM ------------------------------
REM -- Do a reorg on all tables but exclude DEADLOCK tables
db2 -x "select 'reorg table',substr(rtrim(tabschema)||'.'||rtrim(tabname),1,80),';'from syscat.tables where type = 'T' and not tabname like '%DEADLOCK%'" > reorg.out
db2 -tvf reorg.out
REM ------------------------------
REM -- Do a reorg on all tables indexes but exclude DEADLOCK tables
db2 -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.out
db2 -tvf reorgindexes.out
REM ------------------------------
REM -- Do a final reorgchk to make sure that stats are now accurate given all the data movement around on pages
REM -- Do a final reorgchk
db2 reorgchk update statistics on table all


Ralph Schoon selected this answer as the correct answer

One other answer

Most liked answers ↑|Newest answers|Oldest answers

0
permanent link
Daniel Moul (5.0k●13●18) | answered Oct 02 '23, 12:06 p.m.
FORUM MODERATOR / JAZZ DEVELOPER

 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
Edward Chan commented Oct 03 '23, 1:10 a.m.

Question to DOORS Next admins, has anyone implemented automatic database maintenance for DB2? What were the pros and cons?

Ian Barnard commented Oct 03 '23, 8:11 a.m. | edited Oct 03 '23, 9:02 a.m.
FORUM ADMINISTRATOR / FORUM MODERATOR / JAZZ DEVELOPER

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.

Your answer

Register or to post your answer.


Dashboards and work items are no longer publicly available, so some links may be invalid. We now provide similar information through other means. Learn more here.