Here are some steps to extract the database configurations:
1. SSH into the asy52 database server as user db2admin:
ssh \\servername
2. Login as admin user.
3. Gain privileges db2admin:
su - db2admin
4. Enter the password for db2admin.
5. Run this command to change to context to DB2:
db2
6. Run this command:
list active databases
7. For the first active database, run these commands:
connect to <database name>
get db cfg
8. Use winscp to copy these configuration files to email.
Each Jazz database should be optimised every month (or every week if you are heavily using the system). The script below gathers information from:
https://dba.stackexchange.com/questions/30231/do-i-need-to-runstats-after-a-reorg-in-db2
https://jazz.net/forum/questions/18425/weird-behaviour-during-db2-reorg-for-some-jazz-tables
https://bytes.com/topic/db2/answers/184330-difference-between-runstats-reorgchk
Wait until the end of the day (as this will disrupt Jazz and take around an hour to run) and then execute the following commands:
1. Log into the database server.
2. Start > IBM DB2 DB2COPY1 (Default) > DB2 Command Window - Administrator
3. Type the following command (replacing the password with the db2admin password):
set db2password={db2adminPassword}
4. Copy and paste all of the following commands:
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
5. Restart the Database server.
6. Restart the DOORS server.