IBM Support

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

Subscribe

You can track all active APARs for this component.

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 number

    IT22928

  • Reported component name

    TSM SERVER

  • Reported component ID

    5698ISMSV

  • Reported release

    81W

  • Status

    CLOSED PER

  • PE

    NoPE

  • HIPER

    NoHIPER

  • Special Attention

    NoSpecatt / Xsystem

  • Submitted date

    2017-10-25

  • Closed date

    2018-01-15

  • Last modified date

    2018-01-15

  • 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

  • R71A PSY

       UP

  • R71S PSY

       UP

  • R71L PSY

       UP

  • R71W PSY

       UP

  • R81A PSY

       UP

  • R81L PSY

       UP

  • R81W PSY

       UP



Document information

More support for: Tivoli Storage Manager

Software version: 81W

Reference #: IT22928

Modified date: 15 January 2018