IT19245: UNEXPECTED DATABASE GROWTH IN TABLE BF_QUEUED_CHUNKS WITH ARCHIVELOG FILLING VERY FAST

7.1.7.200-TIV-TSMSRV-AIX
7.1.7.200-TIV-TSMSRV-HP-UX
7.1.7.200-TIV-TSMSRV-Linuxx86_64
7.1.7.200-TIV-TSMSRV-SolarisSPARC
7.1.7.200-TIV-TSMSRV-WIN
7.1.7.200-TIV-TSMSRV-Linuxppc64
7.1.7.200-TIV-TSMSRV-Linuxs390x
8.1.2.000-IBM-SPSRV-AIX
8.1.2.000-IBM-SPSRV-Linuxppc64le
8.1.2.000-IBM-SPCMS-Linuxx86_64
8.1.2.000-IBM-SPCMS-WindowsI32
8.1.2.000-IBM-SPSRV-Linuxs390x
8.1.2.000-IBM-SPCMS-WindowsX64
8.1.2.000-IBM-SPSRV-Linuxx86_64
8.1.2.000-IBM-SPSRV-WindowsX64
8.1.2.000-IBM-SPOC-AIX
8.1.2.000-IBM-SPOC-LinuxPPC64le
8.1.2.000-IBM-SPOC-Linuxs390x
8.1.2.000-IBM-SPOC-Linuxx86_64
8.1.2.000-IBM-SPOC-WindowsX64
IBM Spectrum Protect Server V7.1.7.X interim fix downloads
IBM Spectrum Protect Server V8.1 Fix Pack 2 (V8.1.2) Downloads
IBM Spectrum Protect Server V7.1 Fix Pack 8 (7.1.8.000) Downloads

APAR status

• Closed as program error.

Error description

• If legacy deduplication is enabled on the IBM Spectrum Protect
  Server, database TSMDB1 can grow very fast in table
  BF_QUEUED_CHUNKS and it can be seen that the ARCHIVELOG is
  filling rapidly.
  
  
  Customer/L2 Diagnostics:
  With db2top you can see a particular 'Application handle' keeps
  going over and over to 'Rollback to Savepoint' status.
  This indicates that the INSERT statement, which can be found in
  the instr.txt, is consistently failing and being retried, so
  many transaction logs are being used and the data is not being
  processed.
  Cause of this failing SQL, the DB2 storage is increasing in
  table BF_QUEUED_CHUNKS.
  
  "db2top" command will show:
  Application handle: 6896
  Application status: Rollback in progress(56%)
  Application name:   dsmserv
  
  'db2pd -db TSMDB1 -applications' shows:
  Application handle          = 6896
  Application status          = Rollback to Savepoint
       Start Time             = 11/21/2016 12:38:47.509055
       Completed Work         = 3102198 bytes
       Total Work             = 3495758 bytes
  Status change time          = Not Collected
  Application name            = dsmserv
  Application ID              = *LOCAL.tsminst1.161121183644
  
  
  
  From the IBM Spectrum Protect Server monitoring script data you
  will see:
  
  The application handle from db2top command can be found in a
  bfdedup.c created thread in the *-show.txt output files:
  
  slot -> 150:
  Tsn=0:1033110, Resurrected=False, InFlight=True,
  Distributed=False,
  Persistent=True, Addr 0x7fdda8014de8
    Start ThreadId=195, Timestamp=<timestamp>,
  Creator=bfdedup.c(10653)
    Last known in use by ThreadId=195
    Participants=1, summaryVote=ReadOnly
    EndInFlight False, endThreadId 0, tmidx 0, processBatchCount
  0,
  mustAbort False.
      Participant DB: voteReceived=False, ackReceived=False
        DB: Txn 0x7fde3c00b508, ReadOnly(NO),
  connP=0x7fde3c00b2d8,
  applHandle=6896 , openTbls=0:
        DB: --> RegSqlId=0x0100003D INSERT for
  table=BF.Queued.Chunks,executed(No).
  
        DB: --> RegSqlId=0x0100003E DELETE for
  table=BF.Dereferenced.Chunks, executed(No).
  
  and from there the ThreadId can be used to investigate
  *-instr.txt files
  
  instr.txt shows:
  DB2 STATEMENT STATISTICS:
  
  Execution Time: 655.001 sec, Number of Executions: 4865,
  RowsReturned: 0
  INSERT INTO TSMDB1.BF_QUEUED_CHUNKS ( workerid, bfid,
  origworkerid, nodeid, fsid, batchid ) SELECT cast( ? as integer
  ), bfid, cast( ? as integer ), nodeid, fsid, cast( ? as integer
  ) from TSMDB1.bf_dereferenced_chunks where srvid=0 and nodeid=?
  and fsid=? and flags=1 order by srvid, nodeid, fsid, flags, bfid
  fetch first 25000 rows only with CS -- 195
  
  
  
  Tivoli Storage Manager Server Version Affected:
  Tivoli Storage Manager Server and
  IBM Spectrum Protect Server versions 6.3.x, 7.1.x and 8.1.x on
  all supported platforms
  
  Initial Impact:
  High
  
  
  Additional Keywords:
  TSM server Spectrum Protect DB grow dedup
  

Local fix

• Disable deduplication worker threads by adding following into
  dsmserv.opt:
  
  DEDUPDELETIONTHREADS 0
  
  and restart the IBM Spectrum Protect Server.
  

Problem summary

• ****************************************************************
  * USERS AFFECTED:                                              *
  * All Tivoli Storage Manager and IBM Spectrum Protect Server   *
  * users.                                                       *
  ****************************************************************
  * PROBLEM DESCRIPTION:                                         *
  * See ERROR DESCRIPTION.                                       *
  ****************************************************************
  * RECOMMENDATION:                                              *
  * Apply fixing level when available. This problem is currently *
  * projected to be fixed in levels 7.1.7.200, 7.1.8 and 8.1.2.  *
  * Note that this is subject to change at the discretion of     *
  * IBM.                                                         *
  ****************************************************************
  

Problem conclusion

• This problem was fixed.
  Affected platforms:  AIX, Solaris, Linux, and Windows.
  

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