DB2 offline Reorg

due to TSM-7.1.1-Website an offline reorganization of the internal DB2 is recommended when

• there is an unacceptable level of database growth or
• there is degradation in server performance.

steps to do:

The DB2 commandline needs to be run as that user who runs the DB2 and TSM/ISP instance. Administrative permissions / privileges are not suffiecient!

so create a shortcut of the menue entry “DB2 Commandline” and within its Properties add the following string in the Target before the CLI-call: C:\Windows\System32\runas.exe /user:<Instance-User-Name>

so for example it should look like:

C:\Windows\System32\runas.exe /user:sm113 "C:\Program Files\Tivoli\TSM\db2\BIN\DB2CW.BAT"

Notice:
You'll be asked for the user's password!

the commands must be issued with the userspace of the instance owner so

• sudo every command: sudo <instance owner> – or
• become instance-user using a login shell: sudo -i <instance owner>

then cd to the instance's home config directory

When the server is running, determine the amount of temporary space that is required to organize a table. The temporary space that is required is twice the value of the table size. To determine the table size, issue the following commands

 db2 connect to tsmdb1
 db2 set schema tsmdb1

and for each tabular:

db2 call sysproc.reorgchk_tb_stats('T','tsmdb1.<tabname>>')
db2 select tsize from session.tb_stats  

so directing the output to files with skipping all the blanks looks like:

mkdir c:\DB2Stats
db2 call sysproc.reorgchk_tb_stats('T','tsmdb1.AF_BITFILES')
db2 "EXPORT TO c:\DB2Stats\AF_BITFILES.csv OF DEL MODIFIED BY NOCHARDEL  select 'AF_BITFILES: ' || TRIM(tsize) as AF_BITFILES from session.tb_stats"
db2 call sysproc.reorgchk_tb_stats('T','tsmdb1.AS_VOLUME_STATUS')
db2 "EXPORT TO c:\DB2Stats\AS_VOLUME_STATUS.csv OF DEL MODIFIED BY NOCHARDEL  select 'AS_VOLUME_STATUS: ' || TRIM(tsize) as AS_VOLUME_STATUS from session.tb_stats"
db2 call sysproc.reorgchk_tb_stats('T','tsmdb1.BF_AGGREGATED_BITFILES')
db2 "EXPORT TO c:\DB2Stats\BF_AGGREGATED_BITFILES.csv OF DEL MODIFIED BY NOCHARDEL  select 'BF_AGGREGATED_BITFILES: ' || TRIM(tsize) as BF_AGGREGATED_BITFILES from session.tb_stats"
db2 call sysproc.reorgchk_tb_stats('T','tsmdb1.BF_BITFILE_EXTENTS')
db2 "EXPORT TO c:\DB2Stats\BF_BITFILE_EXTENTS.csv OF DEL MODIFIED BY NOCHARDEL  select 'BF_BITFILE_EXTENTS: ' || TRIM(tsize) as BF_BITFILE_EXTENTS from session.tb_stats"
db2 call sysproc.reorgchk_tb_stats('T','tsmdb1.BF_DEREFERENCED_CHUNKS')
db2 "EXPORT TO c:\DB2Stats\BF_DEREFERENCED_CHUNKS.csv OF DEL MODIFIED BY NOCHARDEL  select 'BF_DEREFERENCED_CHUNKS: ' || TRIM(tsize) as BF_DEREFERENCED_CHUNKS from session.tb_stats"
db2 call sysproc.reorgchk_tb_stats('T','tsmdb1.BF_QUEUED_CHUNKS')
db2 "EXPORT TO c:\DB2Stats\BF_QUEUED_CHUNKS.csv OF DEL MODIFIED BY NOCHARDEL  select 'BF_QUEUED_CHUNKS: ' || TRIM(tsize) as BF_QUEUED_CHUNKS from session.tb_stats"
db2 call sysproc.reorgchk_tb_stats('T','tsmdb1.GROUP_LEADERS')
db2 "EXPORT TO c:\DB2Stats\GROUP_LEADERS.csv OF DEL MODIFIED BY NOCHARDEL  select 'GROUP_LEADERS: ' || TRIM(tsize) as GROUP_LEADERS from session.tb_stats"
db2 call sysproc.reorgchk_tb_stats('T','tsmdb1.BACKUP_OBJECTS')
db2 "EXPORT TO c:\DB2Stats\BACKUP_OBJECTS.csv OF DEL MODIFIED BY NOCHARDEL  select 'BACKUP_OBJECTS: ' || TRIM(tsize) as BACKUP_OBJECTS from session.tb_stats"
db2 call sysproc.reorgchk_tb_stats('T','tsmdb1.ARCHIVE_OBJECTS')
db2 "EXPORT TO c:\DB2Stats\ARCHIVE_OBJECTS.csv OF DEL MODIFIED BY NOCHARDEL  select 'ARCHIVE_OBJECTS: ' || TRIM(tsize) as ARCHIVE_OBJECTS from session.tb_stats"

Create a temporary table space to use during the reorganization. Issue the following commands:

db2 "CREATE SYSTEM TEMPORARY 
TABLESPACE REORG PAGESIZE pagesize 
MANAGED BY SYSTEM USING ('<path>') BUFFERPOOL bufferpool 
DROPPED TABLE RECOVERY OFF Copy

The <path> that the database instance user owns indicates a directory. The directory is at least twice the value of the tsize table size, and is on the fastest reliable available disk.