Collecting Data for Tivoli Storage Manager: Server Database Reorganization

Technote (troubleshooting)


Problem(Abstract)

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:

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 command:
  • QUERY SYSTEM > querysys.txt

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 6.2.6, 6.3.4 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
db2pd -d tsmdb1 -logs > db2pd-logs.txt
db2pd -d tsmdb1 -applications > db2pd-app.out
db2pd -d tsmdb1 -transactions > db2pd-txn.out


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


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


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


db2 "select tu.name,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 tu.name=iu.name and
tu.name=ta.name and (table_alloc_mb+index_alloc_mb)>5
order by table_alloc_mb desc,index_alloc_mb desc,
tu.name with ur" > table_logical_physiscal_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
('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


Item to check for negative colcard


db2 "select tabname, colname, colcard from sysstat.columns where tabschema='TSMDB1' and colcard < -1" > colcard.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 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
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.


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

Related information

Database Reorg Technote
ActivePerl
Cygwin
Strawberry Perl for Windows

Perl script to collect the reorg information requested: serverReorgInfo.plserverReorgInfo.pl
cksum serverReorgInfo.pl
33261038 19051 serverReorgInfo.pl

md5sum serverReorgInfo.pl
9ee56c3e6c7c7dfefd5209b64a5c0a01 serverReorgInfo.pl

Perl script to analyze reorg formulas: analyze_DB2_formulas.planalyze_DB2_formulas.pl

cksum analyze_DB2_formulas.pl
1478217500 43994 analyze_DB2_formulas.pl

md5sum analyze_DB2_formulas.pl
df39b96a1ac3390b67640a2f7089e153 *analyze_DB2_formulas.pl

Product Alias/Synonym

TSM

Rate this page:

(0 users)Average rating

Document information


More support for:

Tivoli Storage Manager
Server

Software version:

All Supported Versions

Operating system(s):

All Platforms

Reference #:

1590928

Modified date:

2014-11-27

Translate my page

Machine Translation

Content navigation