IT17642: SLOW EXPIRATION AFTER UPGRADE TO 6.3.6.000 SERVER

8.1.1.100-IBM-SPOC-WindowsX64
8.1.1.100-IBM-SPOC-Linuxx86_64
8.1.1.100-IBM-SPOC-Linuxs390x
8.1.1.100-IBM-SPOC-AIX
8.1.1.000-IBM-SPSRV-WindowsX64
8.1.1.000-IBM-SPSRV-Linuxs390x
8.1.1.000-IBM-SPSRV-AIX
8.1.1.000-IBM-SPSRV-Linuxx86_64
6.3.6.100-TIV-TSMALL-SolarisSPARC
6.3.6.100-TIV-TSMSTA-WindowsI32
6.3.6.100-TIV-TSMALL-WindowsX64
6.3.6.100-TIV-TSMALL-Linuxx86_64
6.3.6.100-TIV-TSMALL-Linuxs390x
6.3.6.100-TIV-TSMALL-Linuxppc64
6.3.6.100-TIV-TSMALL-HP-UX
6.3.6.100-TIV-TSMALL-AIX
7.1.7.100-TIV-TSMSRV-WIN
7.1.7.100-TIV-TSMSRV-SolarisSPARC
7.1.7.100-TIV-TSMSRV-Linuxx86_64
7.1.7.100-TIV-TSMSRV-Linuxs390x
7.1.7.100-TIV-TSMSRV-Linuxppc64
7.1.7.100-TIV-TSMSRV-HP-UX
7.1.7.100-TIV-TSMSRV-AIX
IBM Tivoli Storage Manager server V6.3.6.x interim fix downloads
IBM Spectrum Protect Server V8.1 Fix Pack 1 (V8.1.1) Downloads
IBM Spectrum Protect Server V7.1.7.X interim fix downloads
IBM Spectrum Protect Server V7.1 Fix Pack 8 (7.1.8.000) Downloads

APAR status

• Closed as program error.

Error description

• After upgrading the server to 6.3.6.000 (or higher)  and
  7.1.3(or higher) expiration might "hang" on a node while
  expiring backup data only.   Expiration is not actually hung it
  is still processing but very slowly due to a non-optimized
  SQL/Select.   A change to this SQL/Select occurred between
  6.3.5.0 and 6.3.6.0.   This code change was implemented in
  servers 6.3.6.0+ and 7.1.3.0 to 7.1.7.   There have been no
  reported problems at 7.1.3 or above.
  
  Customer/L2 Diagnostics (if applicable)
  From the server activity log - review expiration entries.
  Expiration processes by node/filespace.  If it is seen that 1
  node does not complete expiration in a timely manner your server
  may be experiencing this issue.  By default Expiration restarts
  where it left off.  If it starts on the same node repeatedly
  this may also be another indicator you are hitting this issue.
  
  Servermon.pl output can be gathered for 1 hour and needs to
  collect the db2 information.   From the *db2.txt output search
  for the "ELAPSED_TIME_SEC" section. If you are experiencing this
  issue you will find the following select and a high Elapsed
  execution time - in this example 25310 seconds (which is a
  little over 7 hrs):
  
   ELAPSED_TIME_SEC STATE STMT_TEXT
   ---------------- -----
  ----------------------------------------------------------------
  -------------------------
  25310 EXEC  SELECT IMBK.OBJID, IMBK.BFSIZE, CASE WHEN
  IMBK.GROUPTYPE IS NOT NULL AND BITAND(IMBK.GROUPTYPE,458752)>0
  THEN 1 END AS ISIMGLLEAD, CASE WHEN IMBK.GROUPTYPE IS NOT NULL
  AND BITAND(IMBK.GROUPTYPE,7)>0 THEN 1 END AS ISIMGLMEMB FROM
  TSMDB1.BACKUP_OBJECTS IMBK WHERE IMBK.NODEID=? AND IMBK.FSID=?
  AND IMBK.OBJTYPE=? AND IMBK.STATE=2 AND IMBK.MCID=? AND (
  BITAND(IMBK.GROUPTYPE,CAST(? AS INT))=0 OR IMBK.GROUPTYPE=131072
  OR IMBK.GROUPTYPE IS NULL ) AND ( IMBK.DEACDATE<='1956-10-11
  00:00:00' OR ( IMBK.DEACDATE<=? AND
  
     1 record(s) selected.
  
  *NOTE*
  The ELAPSED_TIME_SEC select has been truncated in the DB2
  collection and this is how it will appear in the output.
  
  Platforms affected:
  Windows, Unix/Linux 6.3.6, 7.1.3 and higher
  
  Initial Impact: Medium
  
  Additional Keywords: IT07612
  
   |MDVREGR 6.3.6.0| |MDVREGR 7.1.3.0|
  

Local fix

• 1) Contact Tivoli Storage Manager Support for a "Diagnostic"
  server (6.3.6.000 only)  which has been made available to
  correct this problem until the fix is available
  2) If you can not apply the DIAG server the work-around would be
  to run expiration:
       excluding the "slow" node(s) and  restarting expiration at
  the first node.   This can be done by adding the 2 following
  undocumented parameters to the Expire Inventory command:
      restart=no
      excludenodes=node1,node2,etc (where node1, node2 are the
  names of the slow nodes)
  

Problem summary

• ****************************************************************
  * USERS AFFECTED:                                              *
  * All Tivoli Storage Manager server users.                     *
  ****************************************************************
  * PROBLEM DESCRIPTION:                                         *
  * See error description.                                       *
  ****************************************************************
  * RECOMMENDATION:                                              *
  * Apply fixing level when available. This problem is currently *
  * projected to be fixed in levels 6.3.6.100, 7.1.7.100, 7.1.8  *
  * and 8.1.1. Note that this is subject to change at the        *
  * discretion of IBM.                                           *
  ****************************************************************
  

Problem conclusion

• This problem was fixed.
  Affected platforms: AIX, HP-UX, Solaris, Linux, and Windows.
  
  An associated flash has been published:
  http://www.ibm.com/support/docview.wss?uid=swg21994473
  

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