IT43352: "PROTECT STGPOOL" TO LOCAL, DELETION PHASE PERFORMANCE IS SLOW

8.1.19.000-IBM-SPSRV-WindowsX64
8.1.19.000-IBM-SPSRV-Linuxx86_64
8.1.19.000-IBM-SPSRV-Linuxs390x
8.1.19.000-IBM-SPSRV-Linuxppc64le
8.1.19.000-IBM-SPSRV-AIX

APAR status

• Closed as program error.

Error description

• The "Protect Stgpool" to a local copy container pool deletion
  phase performance is slow due to a long running db2 select.
  
  As a side effect it might be seen that the admin session which
  started the "Protect Stgpool" does not return to a command
  prompt while the deletion phase is running.
  
  
  This can be seen within the built-in servermon 10min-show.xml:
   Thread 1446817, Parent 1446814: SmAdminCommandThread, Storage
  202784, AllocCnt 251 HighWaterAmt 203024
      tid=5c21, ptid=511e, det=0, zomb=0, join=0, result=0,
  sess=0, procToken=700, sessToken=881795
       Stack trace:
         0x090000000021c580 semop
  
         0x09000000061717e8 sqloSSemP
  
         0x0900000006d120bc
  sqlccipcrecv__FP17SQLCC_COMHANDLE_TP12SQLCC_COND_T
  
         0x090000000616b218 sqlccrecv
                   <==== waiting for the sql
         0x090000000641d6d8 sqljcReceive__FP10sqljCmnMgr
  
         0x090000000663be78
  sqljrDrdaArOpen__FP14db2UCinterfaceP15db2UCCursorInfo
  
         0x090000000657d424
  csmOpen__FP14db2UCinterfaceP15db2UCCursorInfo
  
         0x09000000066c0418
  CLI_sqlOpen__FP17CLI_STATEMENTINFOP19CLI_ERRORHEADERINFO
  
         0x090000000674859c
  SQLExecute2__FP17CLI_STATEMENTINFOP19CLI_ERRORHEADERINFO
  
         0x0900000006742f58 SQLExecute
  
         0x00000001000d8ee0 tbRegExecEx2
  
         0x0000000100bd0e54 IPRA.$LocalProtectOpenQryDeletedChunks
  
  
         0x0000000100ba2598
  IPRA.$LocalProtectDispatchAndWaitDeletes
  
         0x0000000100b9ef34 sdProtectStgpool
  
         0x0000000100441ba0 AdmProtectStg
  
         0x000000010113630c AdmUseExtCmdTab
  
         0x00000001009f5218 AdmCommandLocal
  
         0x00000001009f1d60 admCommand
  
         0x0000000100fd48f4 SmAdminCommandThread
  
         0x0000000100011470 StartThread
  
      Thread context:
  
        COMMAND: PROTECT STGPOOL
        COMMMETHOD: SSL
        PORT: xxxxxx
        SESSION: 881795
        PROCESS_NUMBER: 700
        PROCESS_DESC: PROTECT STGPOOL (SUMMARY)
        THREAD_TYPE: PROCESS
        SESSION_TYPE: ADMIN
        ADMIN_NAME: xxxxx
  
  Tsn=0:1633353768, Resurrected=False, InFlight=True,
  Distributed=False, Persistent=True, Addr 15e3e59c8
   Start ThreadId=1446817, Timestamp=03/28/22 15:57:02,
  Creator=sdprotect.c(17029)
   Last known in use by ThreadId=1446817
   Participants=1, summaryVote=ReadOnly
   EndInFlight False, endThreadId 0, tmidx 0, processBatchCount 0,
  mustAbort False.
     Participant DB: voteReceived=False, ackReceived=False
       DB: Txn 118531288, ReadOnly(YES), connP=12b34d528,
  applHandle=51659, openTbls=4:
       DB: --> OpenP=123db86a8 for table=GUIL2.MaintHist.
       DB: --> OpenP=19725ba48 for table=Activity.Summary.
       DB: --> OpenP=12b6a21c8 for table=MMS.Lib.Inventory.
       DB: --> OpenP=12fbbf568 for table=AS.Volume.Status.
       DB: --> RegSql=SDSTMT_GET_DELETED_CHUNKS_TAPE SELECT for
  table=SD.Chunk.Copies, executed(No).
  
  
  From the db2.xml output for long running SQLs, it can be seen
  that the following select runs for 2963 seconds for example.
  2963 EXEC  SELECT chunkid,copy_cntrid,length FROM
  TSMDB1.sd_chunk_copies WHERE poolid=? AND BITAND(flags,1)=1
  GROUP BY copy_cntrid,length,chunkid ORDER BY
  copy_cntrid,length,chunkid FOR READ ONLY WITH UR --1446817
  51659
  
  The affected db2 table is the TSMDB1. SD_CHUNK_COPIES.
  
  A db2 explain of this table shows that there are no indexes
  used by the query and a full table scan is being performed.
       Rows
      RETURN
      (   1)
       Cost
        I/O
        |
    2.21899e+08
      GRPBY
      (   2)
    2.26677e+08
    4.7784e+07
        |
    2.21899e+08
      TBSCAN
      (   3)
    2.26651e+08
    4.7784e+07
        |
    2.21899e+08
      SORT
      (   4)
    2.26109e+08
    4.74615e+07
        |
    2.21899e+08
      TBSCAN     <==== Full table scan (no index usage)
      (   5)
    2.23118e+08
    4.7139e+07
        |
    5.54748e+09
  TABLE: TSMDB1
  SD_CHUNK_COPIES
        Q1
  ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
  
  
  IBM Spectrum Protect versions Affected:
  
  IBM Spectrum Protect Version 8.1 and above on all supported
  platforms
  
  
  
  Additional Keywords TS008451460 performance deletion protect
  stgpool decrease admin session
  

Local fix

Problem summary

• ****************************************************************
  * USERS AFFECTED:                                              *
  * All IBM Spectrum Protect server users of local storage pool  *
  * protection to tape.                                          *
  ****************************************************************
  * PROBLEM DESCRIPTION:                                         *
  * See error description.                                       *
  ****************************************************************
  * RECOMMENDATION:                                              *
  * Apply fixing level when available. This problem is currently *
  * projected to be fixed in level 8.1.19. Note that this is     *
  * subject to change at the discretion of IBM.                  *
  ****************************************************************
  

Problem conclusion

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