IC60940: TSM 6.1 DB2 DB2SYSC PROCESS CAN USE 100% OF CPU FOR LONG PERIODS OF TIME

IBM Tivoli Storage Manager V6.1 Fix Pack 2 (6.1.2) Server Downloads
IBM Tivoli Storage Manager V6.1 Fix Pack 3 (6.1.3) Server Downloads

APAR status

• Closed as program error.

Error description

• The TSM server may choose to scan a table rather than to use
  an index based on an incorrect decision by the performance
  tuning algorithm. This can cause the db2sysc process to
  consume a large amount of CPU time.
  -
  This problem is likely to be encountered in these conditions:
  o A TSM 6.1 server which is newly deployed, and new data is
    being backed up.
  o DATA DEDUPLICATION is being used on a storage pool with new
    incoming data, and an IDENTIFY process is running
  o Data is being loaded (through normal TSM operation)
    into a new TSM server with empty tables which are affected
    by this performace problem.
  -
  Note that this problem will eventually resolve itself once
  the TSM performance tuning algorithm monitors enough activity
  on the table to initiate an update to the database table
  statistics.  This update to the database table statistics
  will allow the database to use available indices in order to
  better optimize the execution of various SQL statements that
  will be performed.
  -
  Also note that it is possible for a SELECT command to use 100%
  of the CPU under normal conditions.
  -
  For example, a SELECT command from a command line administrative
  client may submit an SQL statement that can not be easily
  optimized using the existing database schema and available
  indices.  In these cases, this CPU usage may be appropriate and
  simply a function of that SQL that was being processed at this
  time.
  TSM Versions Affected:
  TSM 6.1 servers on all platforms
  Customer/L2 Diagnostics:
  Gather this output for support to determine which query is
  running over time:
  -
  DIAG ITEM #1: Snapshot dynamic SQL
  From a db2 command window issue:
    db2 update monitor switches using STATEMENT on global
  After some time, about every 5 minutes over a 20-30 minute
  period:
    db2 get snapshot for dynamic sql on DBNAMEHERE > sqlsnapX.txt
  Where sqlsnapX.txt should be sqlsnap1.txt, sqlsnap2.txt, etc.
  And then turn off the dynamic sql monitoring with:
    db2 update monitor switches using STATEMENT off global
  DIAG ITEM #2:  See what applications (connections) are
  inflight against the database:
  from a db2 command window issue:
     db2 get snapshot for all applications
  Initial Impact:
  Medium
  Additional Keywords:
  db2sysc db2 TSM61 TSMDB2 loop hung consume zz61
  

Local fix

• If this problem persists, statistics can be updated using the
  DB2 RUNSTATS command. Contact TSM support for assistance.
  

Problem summary

• ****************************************************************
  * USERS AFFECTED: Users of Tivoli Storage Manager server       *
  *                 V6.1.0.0 or V6.1.1.0.                        *
  ****************************************************************
  * PROBLEM DESCRIPTION: See ERROR Description.                  *
  ****************************************************************
  * RECOMMENDATION: Apply the level containing this              *
  *                 fix when available. This problem is          *
  *                 currently projected to be fixed              *
  *                 in level 6.1.2.  Note that this              *
  *                 is subject to change at the                  *
  *                 discretion of IBM.                           *
  ****************************************************************
  *
  

Problem conclusion

• The issue has been corrected.
  

Temporary fix

Comments

APAR Information

• APAR is sysrouted FROM one or more of the following:

• APAR is sysrouted TO one or more of the following:

Fix information

• Fixed component name

  TSM SERVER

• Fixed component ID

  5698ISMSV

Applicable component levels

• R61A PSY

     UP

• R61H PSY

     UP

• R61L PSY

     UP

• R61S PSY

     UP

• R61W PSY

     UP