IT43832: EXPIRE INVENTORY PROCESS IS EXTREMELY SLOW

APAR status

• Closed as program error.

Error description

• Inventory expiration process takes very long time  to complete
  processing very few objects. In detail the inventory is slow
  when
  it is processing data that has been handled by retention set
  rules.
  ===============================================================
  =================================
  Servermon reports:
  
  Expiration was active for Thread 208550:
  
  Thread 208550 ExpirationProcessThread parent=208539
  2023-03-07T06:00:30.080-->2023-03-07T06:20:18.508
  NODE_NAME:,PROCESS_NUMBER:70,COMMAND:EXPIRE
  INVENTORY,PROCESS_DESC:EXPIRE INVENTORY
  Operation Count Tottime Avgtime Mintime Maxtime InstTput Total
  KB
  ---------------------------------------------------------------
  -------------
  DB2 Fetch Prep 78 0.044 0.001 0.000 0.007
  DB2 MFtch Prep 9 0.019 0.002 0.000 0.006
  DB2 Inser Prep 2 0.005 0.003 0.001 0.003
  DB2 Delet Prep 2 0.003 0.002 0.001 0.001
  DB2 Updat Prep 3 0.008 0.003 0.001 0.004
  DB2 Fetch Exec 8218 1.192 0.000 0.000 0.013
  DB2 MFtch Exec 333 58.103 0.174 0.000 3.313
  DB2 Inser Exec 939 0.149 0.000 0.000 0.009
  DB2 Delet Exec 876 3.010 0.003 0.000 0.352
  DB2 Updat Exec 13 0.023 0.002 0.000 0.007
  DB2 Fetch 8218 0.020 0.000 0.000 0.000
  DB2 MFetch 1033 0.026 0.000 0.000 0.001
  DB2 Commit 2298 0.279 0.000 0.000 0.024
  DB2 Reg Prep 647 0.604 0.001 0.000 0.310
  DB2 Reg Exec 6549 1124.341 0.172 0.000 344.853
  DB2 Reg Fetch 5280 0.018 0.000 0.000 0.000
  DB2 Connect 1 0.027 0.028 0.027 0.027
  Acquire Latch 3254 0.000 0.000 0.000 0.000
  Thread Wait 4 0.174 0.044 0.012 0.100
  Unknown 0.375
  ---------------------------------------------------------------
  -------------
  Total 1188.427
  
  Most of the time is spent in DB2 Reg Exec section.
  Searching for the thread number correlated to the expiration
  thread it is possible to identify the following Long running
  selects for table backup_objects which is the largest table on
  the server.
  For e.g. the following SQL takes more than 1000 sec.:
  
  20230307T0720-00000044-db2.xml:1124|EXEC|SELECT 1 FROM
  TSMDB1.BACKUP_OBJECTS IMBK WHERE IMBK.NODEID=? AND IMBK.FSID=?
  AND EXISTS (SELECT 1 FROM TSMDB1.BF_RETENTION_MAPPING BFRM WHERE
  IMBK.OBJID=BFRM.OBJID) FETCH FIRST 1 ROW ONLY FOR READ ONLY WITH
  UR --208550 |13333"
  NAME|ROWS_IN_TABLE|TABLE_USED_MB|TABLE_ALLOC_MB|INDEX_USED_MB|I
  NDEX_ALLOC_MB
  BACKUP_OBJECTS
  |1864580272"|692577|694133"|588733|590682"
  

Local fix

Problem summary

• ****************************************************************
  * USERS AFFECTED:                                              *
  * All IBM Spectrum Protect server users of retention sets.     *
  ****************************************************************
  * PROBLEM DESCRIPTION:                                         *
  * See error description                                        *
  ****************************************************************
  * RECOMMENDATION:                                              *
  * Apply fixing level when available. This problem is projected *
  * to be fixed in level 8.1.19.000                              *
  ****************************************************************
  

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