Differences

This shows you the differences between two versions of the page.

Link to this comparison view

Both sides previous revisionPrevious revision
Next revision
Previous revision
en:services:storage_services:backup:tsm:admin:dbreorg [2018/11/28 11:43] – [Create a temporary table space to use during the reorganization] bnachtwen:services:storage_services:backup:tsm:admin:dbreorg [2018/11/28 11:47] (current) – [preparation on windows] bnachtw
Line 1: Line 1:
 +====== 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.//
 +