Collecting Data for Tivoli Storage Manager: Server Database Reorganization

Collecting Troubleshooting Data early, even before opening the PMR, helps IBM® Support quickly determine if:

Symptoms match known problems (rediscovery).
There is a non-defect problem that can be identified and resolved.
There is a defect that identifies a workaround to reduce severity.
Locating root cause can speed development of a code fix.

Collecting Data: Table of contents:

	Gathering general information
	Manually Gathering General Information
	Gathering Server Database Reorganization Information
	Submitting Information to IBM Support
	Online Self-Help Resources
	Related Information

Gathering General Information

For supported levels of IBM Tivoli Storage Manager you can use IBM Support Assistant (ISA) to capture general information. Alternatively, you can also manually collect the general information.

Entering general information into an electronically-opened PMR (ESR) eliminates waiting on the phone to provide general information to Level 1 support.

Manually Gathering General Information

From a Tivoli Storage Manager Administrative command line client, enter the following commands:

• QUERY SYSTEM > querysys.txt
• QUERY ACTLOG begind=<mm/dd/yyyy> begint=<hh:mm> endd=<mm/dd/yyyy> endt=<hh:mm> > actlog.txt

- where begind and begint are the beginning date and time for the actlog entries being collected
- where endd and endt are the ending date and time for the actlog entries being collected
- the actlog gather should cover the full time frame of the issue/problem/scenario being diagnosed

Explicitly using the above commands will redirect the output to files called querysys.txt and actlog.txt in the Tivoli Storage Manager servers working directory. The names of these files can be changed and a full path can be specified to place the output in any desired directory using any desired name.

These files along with the following files/info should be included as general information:

• dsmserv.opt
• dsmserv.err
• details of operating system levels
• Tivoli Storage Manager Server specific version (ex: 6.2.3.0)

Manually Gathering Server Database Reorganization Information

If you are experiencing difficulties with server-initiated reorganization, follow the instructions in this section to gather the information that will be required by IBM Software Support: There is a perl script attached to this technote that will automatically collect the docs required to investigate reorg status.

Note: If you are running your server on a Windows box and you plan to use the script, you need to install a perl interpreter to be able to run the script. There are free and commercial interpreters available, see the related URL section for links to some free interpreters.

Run the script from a DB2 command window as instance user:
Start -> Run -> db2cmd

From there, cd to the target directory for the files generated using the script.

1. Verify that you are running V6.1.5.10, 6.2.4, or 6.3.1 or later versions of the Tivoli Storage Manager server.

2. Indicate whether you are running data deduplication.

3. From a DB2 CLP window, run the following commands (for steps #3 thru #8) as the instance user while the Tivoli Storage Manager server is running:


db2 connect to tsmdb1
db2 set schema tsmdb1
db2pd -d tsmdb1 -reorg index > db2pd-reorg-index.txt
db2pd -d tsmdb1 -runstats > db2pd-runstats.txt


4. Determine whether the database was created under Tivoli Storage Manager V6.1 or later versions.

In the following select


db2 "select reclaimable_space_enabled from table(mon_get_tablespace('',-1)) as T1 where tbsp_id in (2,4,5,6)" > reclaimable_space.txt


the reclaimable_space_enabled column will be zero for server V6.1 databases, even if the system was later upgraded to server V6.2 or later.

Note that the mon_get_tablespace does not exist on V6.1 servers.

If the database was created under Tivoli Storage Manager V6.2 or later, the columns will be 1:



5. If you are experiencing unexplained issues with database growth, collect the following information:


db2 reorgchk current statistics on table all > db2reorgchk.txt


Important note: If you do not specify "current statistics," the default is "update statistics," which will run RUNSTATS commands on all tables in the database. This will likely have a huge performance impact and will take many days to complete.


db2pd -d tsmdb1 -tablespace > db2pd-tablespace.txt


After reorganization is run on all the tables, the output from


db2 "select count(*) as \"TableCount\" from global_attributes
where owner='RDB' and name like 'REORG_TB_%'" > table_count.txt


will be at least 130.

After reorganization is run on all the indices on all the tables, the output from


db2 "select count(*) as \"Indices for TableCount\" from global_attributes
where owner='RDB' and name like 'REORG_IX_%'" > index_count.txt


will be at least 130.

The following selects can be used to get the timestamps for table reorganizations and the tables for which indices have been reorganized:


db2 "select cast( substr(name,10,min(30,length(name)-9))
as char(30)) as \"Tablename\",
substr(char(datetime),1,10) as \"Last Reorg\"
from global_attributes
where owner='RDB' and name like 'REORG_TB_%'
and datetime is not NULL order by datetime desc" >
table_last_reorg.txt
db2 "select cast( substr(name,10,min(30,length(name)-9))
as char(30)) as \"Indices for Tablename\",
substr(char(datetime),1,10) as \"Last Reorg\"
from global_attributes
where owner='RDB' and name like 'REORG_IX_%'
and datetime is not NULL order by datetime desc" >
index_last_reorg.txt


Commands to determine index fragmentation that isn't detected by reorgchk and get the status of when runstats ran last:


db2 "select substr(tabname,1,25),substr(indname,1,20), sequential_pages,
nleaf, density from syscat.indexes where tabname in
('BACKUP_OBJECTS', 'BF_AGGREGATED_BITFILES','ARCHIVE_OBJECTS',
'BF_BITFILE_EXTENTS') order by tabname" > index_frags.txt
db2 "select stats_time,SUBSTR(TABNAME,1,40) from syscat.tables where
tabschema='TSMDB1' AND stats_time is not null order by
stats_time desc" > runstats_time.txt


Items that will help detect lock-wait conditions:


db2 get snapshot for all applications >application.txt

db2 "select application_handle, elapsed_time_sec,
substr( stmt_text, 1, 512) as stmt_text from sysibmadm.mon_current_sql where elapsed_time_sec > 600" > application_handle.txt

db2pd -d tsmdb1 -wlocks >wlocks.out

db2 "SELECT agent_id FROM sysibmadm.applications
"WHERE appl_name='db2reorg' AND appl_status='LOCKWAIT' " > agent_id.txt


6. From a dsmadmc client, obtain the last 30 days of reorganization activity:


q actlog begindate=today-30 enddate=today search=anr029 > anr029.txt
q actlog begindate=today-30 enddate=today search=anr031 > anr031.txt
q actlog begindate=today-30 enddate=today search=anr033 > anr033.txt


Those queries are used to get the Tivoli Storage Manager view of table and index reorganization activity, and RUNSTATS activity.

7. Collect a trace collected while the reorganization window (REORGBEGINTIME + REORDURATION hours) is active, and ensure that a database backup is not running because reorganizations cannot run when a database backup is running. From a dsmadmc client, issue the following commands:


trace dis *
trace ena TBREORG
trace begin <valid_path_and_filename>


-> collect the trace for at least 1 hour


trace flush
trace end
trace dis *


8. As the instance user collect the output for the

"db2support -d tsmdb1 -c -s -g"

command from a system shell.

Submitting Information to IBM Support

After a PMR is open, you can submit diagnostic troubleshooting data to IBM.

If using ESR, update the PMR to indicate that data has been sent.

Online Self-Help Resources

• Review up-to-date product information at the Tivoli Storage Manager Product Support page.
• Utilize the IBM Electronic Service Request tool to access the Tivoli Storage Manager Support team when requiring assistance from IBM.
• Use the IBM Support Assistant (ISA), this free cross product tool assists you in increasing your capacity for self-help. The Tivoli Storage Manager server has a plugin for the ISA tool.
• Install and use the IBM Support Toolbar. This is a stand-alone application that allows you to easily search IBM.com for all types of software support content plus organizes the major areas of not only Software support, but the individual brand support sites into a concise application.

Related Information

• Exchanging information with IBM Technical Support
• Latest Tivoli Storage Manager Server and Client Downloads - Fixpacks and Interim Fixed
• Additional information regarding Database Reorg and Performance Considerations

Perl script to collect the reorg information requested: serverReorgInfo.pl