Database Maintenance

Obtaining database configurations (Linux)

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.

Optimising the database (Windows)

Each Jazz database should be optimised every month (or every week if you are heavily using the system). The script below gathers information from:

• http://www.dbatodba.com/db2/how-to-do/how-to-run-reorg-reorgchk-and-runstat-on-all-tables-in-a-database

• 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://jazz.net/jazz/service/com.ibm.team.workitem.service.internal.rest.IAttachmentRestService/repo/csid/Attachment/RTCDB2TablesWithReorg.txt?itemId=_H1axsGWDEd6HZLaaq4FsDg&amp;id=26643

• https://bytes.com/topic/db2/answers/184330-difference-between-runstats-reorgchk

• https://www.ibm.com/docs/en/sva/7.0.0?topic=udss-updating-system-statistics-by-using-db2-runstats-reorgchk-commands

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.