====== DB2 offline Reorg ======
due to [[https://www.ibm.com/support/knowledgecenter/en/SSGSG7_7.1.1/com.ibm.itsm.srv.doc/t_db_reorg_table_index_offline.html|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:<code>C:\Windows\System32\runas.exe /user:sm113 "C:\Program Files\Tivoli\TSM\db2\BIN\DB2CW.BAT"</code>
\\ 
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 FIXME

===== 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//
<code>
 db2 connect to tsmdb1
 db2 set schema tsmdb1
</code>\\
and for each tabular:
<code>
db2 call sysproc.reorgchk_tb_stats('T','tsmdb1.<tabname>>')
db2 select tsize from session.tb_stats  
</code>\\
so directing the output to files with skipping all the blanks looks like:
==== Windows ====

<code>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"
</code>
==== Linux ====
FIXME
===== Create a temporary table space to use during the reorganization =====
//Create a temporary table space to use during the reorganization. Issue the following commands://
<code>
db2 "CREATE SYSTEM TEMPORARY 
TABLESPACE REORG PAGESIZE pagesize 
MANAGED BY SYSTEM USING ('<path>') BUFFERPOOL bufferpool 
DROPPED TABLE RECOVERY OFF Copy
</code>\\
//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.//