Differences

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

Link to this comparison view

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