Table of Contents
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:
preparation
Windows
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!
Linux
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
Determine temporary space
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:
Windows
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"
Linux
Create a temporary table space to use during the reorganization
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.