IBM Support

Collecting Data for IBM Spectrum Protect: Server Database Reorganization

Technote (troubleshooting)


Collecting Data documents aid in problem determination and save time resolving Problem Management Records (PMRs).

Resolving the problem

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:

For supported levels of IBM Spectrum Protect 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.

From an IBM Spectrum Protect Administrative command line client, enter the following command:
  • QUERY SYSTEM > querysys.txt
  • QUERY ACTLOG BEGIND=-30 > actlog-30.txt

Explicitly using the above commands will redirect the output to files called querysys.txt and actlog-30.txt in the IBM Spectrum Protect 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
  • IBM Spectrum Protect Server specific version (ex:

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

Change the current directory to the target directory for the files generated using the script.

Start the script, e.g. if the script is in the current directory:

1. Verify that you are running 6.3.6, or later versions of the IBM Spectrum Protect 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 IBM Spectrum Protect 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
db2pd -d tsmdb1 -logs > db2pd-logs.txt
db2pd -d tsmdb1 -applications > db2pd-app.out
db2pd -d tsmdb1 -transactions > db2pd-txn.out
db2pd -d tsmdb1 -tcbstats > db2pd-tcbstats.txt

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

In the following select

db2 "select cast(TBSP_NAME as char(30)), reclaimable_space_enabled from table(mon_get_tablespace('',-1)) where TBSP_NAME in ('USERSPACE1','IDXSPACE1','LARGESPACE1','LARGEIDXSPACE1')" > 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

Make sure not being exposed to IC82886:

db2 "select INDEXTYPE from sysibm.sysindexes where name='BFBF_NDX'" > IC82886.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" >
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" >

Command to report how much logical and physical space being occupied by the largest tables:

db2 "select,cast(rows_in_table as bigint),cast(table_used_mb as
bigint),cast(table_alloc_mb as bigint),cast(index_used_mb as
bigint),cast(index_alloc_mb as bigint) from ( select
substr(tabname,1,28) as name,bigint(card) as rows_in_table,
bigint(float(t.npages)/(1024/(b.pagesize/1024))) as table_used_mb
from syscat.tables t, syscat.tablespaces b where t.tbspace=b.tbspace
and t.tabschema='TSMDB1' ) as tu, ( select substr(tabname,1,28)
as name,bigint(sum(i.nleaf)*(b.pagesize/1024)/1024) as index_used_mb
from syscat.indexes i, syscat.tablespaces b where
i.tbspaceid=b.tbspaceid and i.tabschema='TSMDB1' group by tabname,
pagesize ) as iu, ( select substr(tabname,1,28) as name,
bigint(data_object_p_size/1024) as table_alloc_mb,
bigint(index_object_p_size/1024) as index_alloc_mb from
sysibmadm.admintabinfo ) as ta where and and (table_alloc_mb+index_alloc_mb)>5
order by table_alloc_mb desc,index_alloc_mb desc, with ur" > table_logical_physical_space.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
'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

Item to check for negative colcard

db2 "select tabname, colname, colcard from sysstat.columns where tabschema='TSMDB1' and colcard < -1" > colcard.txt

Verify tablespaces for large tables:

db2 "select substr(TABSCHEMA,1,20) as schema , substr(TABNAME,1,20) as table, TBSPACEID, substr(TBSPACE,1,20) as tablespace, substr(index_tbspace,1,20) as indexspace from syscat.tables where tabschema='TSMDB1' and TABNAME in ('BACKUP_OBJECTS','ARCHIVE_OBJECTS', 'BF_BITFILE_EXTENTS', 'BF_AGGREGATED_BITFILES') order by 3" > V71-tablespaces.txt

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

q actlog begindate=today-30 > actlog-30.txt

Those queries are used to get the IBM Spectrum Protect 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
If you are running a V6 server
    "db2support -d tsmdb1 -c -s -g"

If you are running a server > V6

"db2support -d tsmdb1 -c -s -F"

command from a system shell.

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.

  • Review up-to-date product information at the IBM Spectrum Protect Product Support page.
  • Utilize the IBM Electronic Service Request tool to access the IBM Spectrum Protect 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. IBM Spectrum Protect 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 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

Database Reorg Technote
Strawberry Perl for Windows

Perl script to collect the reorg information requested:
9b30101681ff3b330b5a8c64954da426 *

4131731625 25201

Product Alias/Synonym


Document information

More support for: Tivoli Storage Manager

Software version: All Supported Versions

Operating system(s): Platform Independent

Reference #: 1590928

Modified date: 07 August 2015

Translate this page: