IT22928: COLLECTING CONTAINER STORAGE POOL STATISTICS CAN RESULT IN INCREASED TEMP SPACE USAGE AND PERFORMANCE DROP

7.1.9.000-TIV-TSMCMS-Windows64
7.1.9.000-TIV-TSMCMS-Windows32
7.1.9.000-TIV-TSMCMS-Linuxx64
7.1.9.000-TIV-TSMOC-LinuxS390
7.1.9.000-TIV-TSMOC-LinuxPPC64
7.1.9.000-TIV-TSMOC-Windows
7.1.9.000-TIV-TSMOC-Linuxx64
7.1.9.000-TIV-TSMOC-AIX
7.1.9.000-TIV-TSMSRV-WIN
7.1.9.000-TIV-TSMSRV-SolarisSPARC
7.1.9.000-TIV-TSMSRV-Linuxx86_64
7.1.9.000-TIV-TSMSRV-Linuxs390x
7.1.9.000-TIV-TSMSRV-Linuxppc64
7.1.9.000-TIV-TSMSRV-HP-UX
7.1.9.000-TIV-TSMSRV-AIX
8.1.5.000-IBM-SPCMS-WindowsX64
8.1.5.000-IBM-SPCMS-WindowsI32
8.1.5.000-IBM-SPCMS-Linuxx86_64
8.1.5.000-IBM-SPOC-WindowsX64
8.1.5.000-IBM-SPOC-Linuxx86_64
8.1.5.000-IBM-SPOC-Linuxs390x
8.1.5.000-IBM-SPOC-LinuxPPC64le
8.1.5.000-IBM-SPOC-AIX
8.1.5.000-IBM-SPSRV-WindowsX64
8.1.5.000-IBM-SPSRV-Linuxx86_64
8.1.5.000-IBM-SPSRV-Linuxs390x
8.1.5.000-IBM-SPSRV-Linuxppc64le
8.1.5.000-IBM-SPSRV-AIX
IBM Spectrum Protect Server V8.1 Fix Pack 5 (V8.1.5) Downloads
IBM Spectrum Protect Server V7.1 Fix Pack 9 (7.1.9.000) Downloads

APAR status

• Closed as program error.

Error description

• APAR IT19613 was opened when it was found that the following SQL
  caused the server to halt because we ran out of tempspace.
  SELECT SUM(CAST(sdac.length as bigint))
  FROM   TSMDB1.sd_replicated_chunks sdrc   LEFT JOIN
  TSMDB1.sd_recon_order sdro
    ON (sdrc.chunkid=sdro.chunkid)   LEFT JOIN
  TSMDB1.sd_all_chunks sdac
    ON (sdrc.chunkid=sdac.chunkid) WHERE
    sdrc.poolid=1 AND   sdro.chunkid IS NULL AND   sdac.chunkid IS
  NOT NULL AND
    (BITAND(sdrc.flags, 2) = 2) FOR READ ONLY   WITH UR
  
  The following replacement SQL (delivered via IT19613)can still
  cause issues when there are many records in the related tables.
  Executing the
  statement can result in excessive usage of temporary tablespace
  and affect overall server operation.
  SELECT  SUM(CAST(sdac.length as bigint))
  FROM  TSMDB1.sd_all_chunks sdac WHERE
    sdac.poolid = 1 AND
   NOT EXISTS  (SELECT 1 FROM tsmdb1.sd_recon_order sdro WHERE
       sdro.chunkid=sdac.chunkid  )
  AND EXISTS   (SELECT 1 FROM tsmdb1.sd_replicated_chunks sdrc
  WHERE
       sdrc.chunkid=sdac.chunkid AND sdrc.poolid=1 AND
       BITAND(sdrc.flags, 2) = 2  ) FOR READ ONLY WITH UR
  
  The SQL command is used to  calculate the total size of data in
  a pool which is replicated from remote servers and not
  referenced locally.
  
  The output from the statement is used to populate the QUERY STG
  F=D column for
  Space Used for Protected Data.
  
  When there are many records in the related tables, executing the
  statement can result in excessive usage of temporary tablespace
  and affect overall server operation.
  
  To confirm the APAR collect data using the server monitoring
  script (swg21432937), in the *-db2.txt files you can find the
  statement listed under the output collected at the end of the
  file:
  ELAPSED_TIME_SEC STATE STMT_TEXT
  ---------------- ----- ---------- ..
  ----------------------------
           777712 EXEC  SELECT SUM( .. FOR READ ONLY WITH UR --
  240
  
  Here the thread id is 240, if you look at the matching
  *-show.txt file you can find the following from SHOW TXNT:
  slot -> 142:
  Tsn=0:1422, Resurrected=False, InFlight=True, Distributed=False,
  Persistent=True, Addr 000000AEBA6539C0
    Start ThreadId=240, Timestamp=08/09/2017 16:47:20,
  Creator=sdutil.c(6398)
    Last known in use by ThreadId=240
    Participants=1, summaryVote=ReadOnly
    EndInFlight False, endThreadId 0, tmidx 0, processBatchCount
  0, mustAbort False.
      Participant DB: voteReceived=False, ackReceived=False
        DB: Txn 000000AEA9349BD0, ReadOnly(YES),
  connP=000000AE900A6960, applHandle=1135, openTbls=0:
        DB: --> RegSqlId=0x0F0000B9 SELECT for
  table=SD.Replicated.Chunks, executed(No).
  
  You are facing this APAR if the SQL statement listed is
  RegSqlId=0x0F0000B9
  
  In the same file, SHOW THREADS output will look similar to
     Thread 240, ID 4528 (0x11b0): SdUpdateProtectStatsThread,
  procToken=0, sessToken=26
     Parent=0, result=0, joining=0, detached=1, zombie=0,
  session=0
  
     Stack trace:
     6e4b06fa Unknown
     7ff86b921118 WaitForSingleObjectEx()+98
     7ff86556ed26 sqloWaitInterrupt()+2f6
     7ff865457349 sqloSSemP()+39
     7ff863d47917 sqlcmhex()+49e7
     7ff863d4ec12 sqlccrecv()+3b2
     7ff86403bc95 sqljcReadGetPtrInt()+df5
     7ff86406ba15 sqljrDrdaArOpen()+1f5
     7ff86417865e csmCursorBlaster()+1f2e
     7ff864536a9b CLI_dumpEventRecords()+cc2b
     7ff8643759fc SQLReloadConfig()+a5ec
     7ff86435211a SQLExecute()+13aa
     7ff857765524 tbRegExecEx()+464 tbrsql.c:1447
     7ff8577dba1e SdUpdateProtectStatsThread()+2be sdutil.c:6429
     7ff8570879d1 startThread()+141 pkthread.c:3685
     7ff863bb4f7f beginthreadex()+107
     7ff863bb5126 endthreadex()+192
     7ff86baa13d2 BaseThreadInitThunk()+22
     7ff86e4354e4 RtlUserThreadStart()+34
  
  | MDVPARTL 7.1.8.0-TIV_5698ISMSV | (IT19613)
  | MDVPARTL 8.1.2.0-TIV_5698ISMSV | (IT19613)
  
  IBM Spectrum Protect Versions Affected:
  7.1.8.x and higher IBM Spectrum Protect Server - All Platforms
  8.1.2.0 and higher IBM Spectrum Protect Server - All Platforms
  

Local fix

• contact IBM support for instructions to implement a local fix
  

Problem summary

• ****************************************************************
  * USERS AFFECTED:                                              *
  * All IBM 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.9 and 8.1.5. Note that   *
  * this is subject to change at the discretion of IBM.          *
  ****************************************************************
  

Problem conclusion

• This problem was fixed.
  Affected platforms for reported release:  AIX, Solaris, Linux,
  and Windows.
  Platforms fixed:  AIX, Linux, Solairs 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