Troubleshooting
Problem
Collecting Data documents aid in problem determination and save time resolving Problem Cases (previously called PMRs).
Resolving The Problem
Collecting Troubleshooting Data early, even before opening the Case, 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 |
Entering general information into an electronically-opened Problem Case (previously called PMRs) eliminates waiting on the phone to provide general information to support.
| Manually Gathering General Information |
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: 7.1.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
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:
perl serverReorgInfo.pl
1. Verify that you are running 6.3.6, 7.1.3.0 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" >
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_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
('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
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 server activity information:
q actlog begindate=today-30 > actlog-30.txt
q actlog begindate=today-9999 msgno=317 > actlog-msg317.txt
q actlog begindate=today-9999 msgno=318 > actlog-msg318.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.
| Submitting Information to IBM Support |
After a Case is open, you can submit diagnostic troubleshooting data to IBM.
When data is uploaded to the Case, the status will be automatically be updated to acknowledge that data has been received. If you are still working with older Problem Management Records, you will want to update the PMR to indicate that you have sent the data.
| Online Self-Help Resources |
-
Review up-to-date product information at the IBM Spectrum Protect page.
- Sign into your account on the Spectrum Protect Product page, to access additional resources or to open a Case with the Support team.
| Related Information |
Related Information
Product Synonym
TSM
Was this topic helpful?
Document Information
Modified date:
25 October 2019
UID
swg21590928