Table of Contents
Tuning parameters for TSM
Enable table reorg for TSM DB2 database
TSM will do itsef a reorganization of his DB by setting the following parameters in dsmserv.opt (or using tsm command setopt)
ALLOWREORGTABLE YES ALLOWREORGINDEX YES DB_DB2_KEEPTABLELOCK YES (depend on version: 6.3.5, 7.1.1) REORGBEGINTIME 16:00 REORGDURATION 4
http://www-01.ibm.com/support/docview.wss?uid=swg21452146
Check if reorg is needed or not:
[tsminst1@arwen]/home/tsminst1$ db2 connect to tsmdb1 Database Connection Information Database server = DB2/AIX64 9.7.6 SQL authorization ID = TSMINST1 Local database alias = TSMDB1 [tsminst1@arwen]/home/tsminst1$ db2 reorgchk current statistics on table all > reorgchk.txt
Here is a sample output of reorgchk.txt, tables and index which needs to be reorganized, have a * at the end of line, based on DB2 parameters (for more info check the DB2 doc)
Table statistics:
F1: 100 * OVERFLOW / CARD < 5
F2: 100 * (Effective Space Utilization of Data Pages) > 70
F3: 100 * (Required Pages / Total Pages) > 80
SCHEMA.NAME CARD OV NP FP ACTBLK TSIZE F1 F2 F3 REORG
----------------------------------------------------------------------------------------
Table: SYSIBM.SYSATTRIBUTES - - - - - - - - - ---
Table: SYSIBM.SYSAUDITPOLICIES 0 0 0 1 - 0 0 - 0 ---
Table: SYSIBM.SYSAUDITUSE - - - - - - - - - ---
Table: SYSIBM.SYSBUFFERPOOLNODES - - - - - - - - - ---
Table: SYSIBM.SYSBUFFERPOOLS 6 0 1 1 - 312 0 - 100 ---
Table: SYSIBM.SYSCHECKS 649 0 8 8 - 125906 0 100 100 ---
......
Table: SYSTOOLS.HMON_ATM_INFO 317 228 3 10 - 149941 71 100 37 *-*
Table: SYSTOOLS.HMON_COLLECTION 0 0 0 1 - 0 0 - 0 ---
Table: SYSTOOLS.POLICY 5 0 1 1 - 740 0 - 100 ---
Table: TSMDB1.ACTIVITY_LOG 1186983 0 10627 24736 - 1.60e+08 0 41 42 -**
Table: TSMDB1.VOLUMES_SQL 125 0 1 32 - 31375 0 3 3 ---
----------------------------------------------------------------------------------------
Index statistics:
F4: CLUSTERRATIO or normalized CLUSTERFACTOR > 80
F5: 100 * (Space used on leaf pages / Space available on non-empty leaf pages) > MIN(50, (100 - PCTFREE))
F6: (100 - PCTFREE) * (Amount of space available in an index with one less level / Amount of space required for all keys) < 100
F7: 100 * (Number of pseudo-deleted RIDs / Total number of RIDs) < 20
F8: 100 * (Number of pseudo-empty leaf pages / Total number of leaf pages) < 20
SCHEMA.NAME INDCARD LEAF ELEAF LVLS NDEL KEYS LEAF_RECSIZE NLEAF_RECSIZE LEAF_PAGE_OVERHEAD NLEAF_PAGE_OVERHEAD PCT_PAGES_SAVED F4 F5 F6 F7 F8 REORG
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Table: SYSIBM.SYSATTRIBUTES
Index: SYSIBM.INDATTRIBUTES01
- - - - - - - - - - - - - - - - -----
Index: SYSIBM.INDATTRIBUTES02
- - - - - - - - - - - - - - - - -----
Index: SYSIBM.INDCOLDIST01
84990 467 0 3 60501 84990 40 40 2132 2132 0 98 60 81 41 0 ---*-
Table: SYSIBM.SYSEVENTMONITORS
Index: SYSIBM.INDEVENTMONITORS01
0 1 1 1 0 0 2 2 3714 3714 0 100 - - 0 100 ----*
Index: SYSIBM.INDEVENTMONITORS02
0 1 1 1 0 0 4 4 3056 3056 0 100 - - 0 100 ----*
To know if reorg has run and how long:
[tsminst1@arwen]/home/tsminst1$ db2 connect to tsmdb1 [tsminst1@arwen]/home/tsminst1$ db2 "select varchar(table_name,60) as table, reorg_status, reorg_start, reorg_end, case when reorg_max_counter = 0 then 0 else (reorg_current_counter * 100)/reorg_max_counter end as percent, reorg_current_counter, reorg_max_counter, reorg_phase, reorg_max_phase from table(snapshot_tbreorg('TSMDB1',-2)) as x order by reorg_start" TABLE REORG_STATUS REORG_START REORG_END PERCENT REORG_CURRENT_COUNTER REORG_MAX_COUNTER REORG_PHASE REORG_MAX_PHASE --------------------------------------- ------------ -------------------------- -------------------------- -------------------- --------------------- -------------------- -------------------- --------------- ACTIVITY_LOG 4 2013-11-26-19.12.47.300393 2013-11-26-19.12.47.660850 2 39 1739 0 2 RESTORE_SRVOBJ 4 2013-12-02-16.13.31.808573 2013-12-02-16.13.31.883961 0 0 152 0 2 BF_AGGREGATED_BITFILES 4 2013-12-03-17.04.30.838410 2013-12-03-17.50.45.918528 73 118872 160927 0 1 ARCHIVE_OBJECTS 4 2013-12-05-17.51.36.487305 2013-12-05-18.03.22.058372 12 12762 106097 0 1 AF_BITFILES 4 2013-12-06-19.07.49.134711 2013-12-06-19.07.53.875272 66 1149 1723 0 2 AF_SEGMENTS 4 2013-12-07-16.03.26.315507 2013-12-07-16.03.36.132769 63 1036 1619 0 2 BF_BITFILE_EXTENTS 4 2013-12-07-16.28.26.437290 2013-12-07-16.28.50.222909 0 0 104736 0 2 BF_AGGREGATE_ATTRIBUTES 4 2013-12-07-19.03.37.097947 2013-12-07-19.03.37.492821 61 407 661 0 2 DS_OVERFLOW 4 2013-12-08-16.29.19.442246 2013-12-08-16.29.21.349468 73 281 383 0 2 DF_CACHEDBITFILES 4 2013-12-13-16.34.33.956126 2013-12-13-16.34.34.078450 37 48 128 0 2 10 record(s) selected. ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- CLUSTERRATIO or normalized CLUSTERFACTOR (F4) will indicate REORG is necessary for indexes that are not in the same sequence as the base table. When multiple indexes are defined on a table, one or more indexes may be flagged as needing REORG. Specify the most important index for REORG sequencing. Tables defined using the ORGANIZE BY clause and the corresponding dimension indexes have a '*' suffix to their names. The cardinality of a dimension index is equal to the Active blocks statistic of the table.
To obtain the status of table reorganization while it is in progress, ensure that you are logged in with the DB2 instance user ID. Then, take the following steps:
db2 connect to tsmdb1
In a DB2 CLP window, run the following command:
db2pd -d tsmdb1 -reorg index > db2pd-reorg-index.txt
It is not possible to obtain the status of index reorganization from DB2. The server issues ANR0317I and ANR0318I. The server trace class TBREORG can be used.
Start reorg: db2 reorg table TSMDB1.ACTIVITY_LOG inplace
Increase parallel IO on TSM DB
Update a DB2 parameter, for example parallel IO if you use RAID 5, commonly disks on a storage array on the SAN:
[tsminst1@tsmaix]/tsm/tsm1/home$ db2set DB2_PARALLEL_IO=*:5
Releasing space in DB2 9.7 table spaces
If you installed Tivoli Storage Manager V6.2 or V6.3, you have DB2 9.7 table spaces with reclaimable space enabled. These instructions are also applicable to V6.2 and V6.3 servers that are upgraded to V7.1.
Ensure that you are logged in with the DB2 instance user ID. Then, run the following commands to determine whether the server has DB2 9.7 or DB2 9.5 table spaces. For DB2 9.7 table spaces, the value in the reclaimable_space_enabled column of the following select is 1. The value is 0 for DB2 9.5 table spaces.
db2 connect to tsmdb1 db2 set schema tsmdb1 db2 "select reclaimable_space_enabled from table(mon_get_tablespace('',-1)) where ( TBSP_NAME='USERSPACE1' or TBSP_NAME='IDXSPACE1' or TBSP_NAME='LARGESPACE1' or TBSP_NAME='LARGEIDXSPACE1' )"
To release space from DB2 9.7 table spaces, issue the following commands:
db2 connect to tsmdb1 db2 set schema tsmdb1
To reduce the size of the DB2 database, issue the following commands. After you run the commands, the file system that contains the DB2 database shows more free space. The following db2 ALTER commands start separate asynchronous processes in DB2. Running more than one ALTER TABLESPACE command is not recommended because they can conflict with each other and result in command failures, for example, DB21034E/SQL0290N pairs.
db2 ALTER TABLESPACE USERSPACE1 REDUCE MAX db2 ALTER TABLESPACE IDXSPACE1 REDUCE MAX db2 ALTER TABLESPACE LARGESPACE1 REDUCE MAX db2 ALTER TABLESPACE LARGEIDXSPACE1 REDUCE MAX
You can monitor the progress of each command (for example, USERSPACE1) by examining the num_extents_left column of the MON_GET_EXTENT_MOVEMENT_STATUS procedure as follows:
db2 connect to tsmdb1 db2 set schema tsmdb1 db2 "select num_extents_left from table(sysproc.MON_GET_EXTENT_MOVEMENT_STATUS('USERSPACE1',-1)) "
Depending on the tablespace reduction that you are monitoring, replace USERSPACE1 with IDXSPACE1, LARGESPACE1, or LARGEIDXSPACE1.
