2841581 - DB6: Frequent compression check via SAPTOOLS.DBH_COMPRESS_COLLECT may cause high CPU usage by db2sysc

1. You notice db2sysc is consuming a significant amount of CPU from OS monitoring tool such as topas, top.

2. You can also observe from the application snapshot, application with SAPTOOLS.DBH_COMPRESS_COLLECT is running while high CPU issue is happening.

For example,

Application handle = <app handle>
Application status = UOW Executing
...
Application name = DB2ATS
...
TP Monitor client application name = DB2ATS-Task: SAPTOOLS.DBH_COMPRESS_COLLECT
...

Dynamic SQL statement text:
INSERT INTO SAPTOOLS.COMPRESSION_TAB( SNAPSHOT_TIMESTAMP, COMPRESS_ATTR, AVGROWSIZE_ADAPTIVE, AVG_COMPRESS_REC_LENGTH, DATA_PARTITION_ID, DBPARTITIONNUM, OBJECT_TYPE, PCTPAGESSAVED_ADAPTIVE, PAGES_SAVED_PERCENT, ROWCOMPMODE, TABNAME, TABSCHEMA, DICT_BUILDER, DICT_BUILD_TIMESTAMP, ROWS_SAMPLED, COMPRESS_DICT_SIZE) SELECT CAST(? AS TIMESTAMP), ( CASE SRC_ADMIN_GET_TAB_COMPRESS_INFO.ROWCOMPMODE WHEN 'A' THEN 'Y' WHEN 'S' THEN 'Y' WHEN '' THEN 'N' ELSE ' ' END ) AS COMPRESS_ATTR, (SRC_ADMIN_GET_TAB_COMPRESS_INFO.AVGROWSIZE_ADAPTIVE) AS AVGROWSIZE_ADAPTIVE, (SRC_ADMIN_GET_TAB_COMPRESS_INFO.AVGROWSIZE_STATIC) AS AVGROWSIZE_STATIC, (SRC_ADMIN_GET_TAB_COMPRESS_INFO.DATAPARTITIONID) AS DATAPARTITIONID, (SRC_ADMIN_GET_TAB_COMPRESS_INFO.DBPARTITIONNUM) AS DBPARTITIONNUM, (SRC_ADMIN_GET_TAB_COMPRESS_INFO.OBJECT_TYPE) AS OBJECT_TYPE, (SRC_ADMIN_GET_TAB_COMPRESS_INFO.PCTPAGESSAVED_ADAPTIVE) AS PCTPAGESSAVED_ADAPTIVE, (SRC_ADMIN_GET_TAB_COMPRESS_INFO.PCTPAGESSAVED_STATIC) AS PCTPAGESSAVED_STATIC, ( CASE SRC_ADMIN_GET_TAB_COMPRESS_INFO.ROWCOMPMODE WHEN 'A' THEN 'Adaptive' WHEN 'S' THEN 'Static' WHEN '' THEN 'Not Enabled' ELSE SRC_ADMIN_GET_TAB_COMPRESS_INFO.ROWCOMPMODE END ) AS ROWCOMPMODE, (SRC_ADMIN_GET_TAB_COMPRESS_INFO.TABNAME) AS TABNAME, (SRC_ADMIN_GET_TAB_COMPRESS_INFO.TABSCHEMA) AS TABSCHEMA, (SRC_ADMIN_GET_TAB_DICTIONARY_INFO.BUILDER) AS BUILDER, (SRC_ADMIN_GET_TAB_DICTIONARY_INFO.BUILD_TIMESTAMP) AS BUILD_TIMESTAMP, (SRC_ADMIN_GET_TAB_DICTIONARY_INFO.ROWS_SAMPLED) AS ROWS_SAMPLED, (SRC_ADMIN_GET_TAB_DICTIONARY_INFO.SIZE) AS SIZE FROM TABLE( SYSPROC.ADMIN_GET_TAB_COMPRESS_INFO(CAST( ? AS VARCHAR(128)),CAST( ? AS VARCHAR(128))) ) AS SRC_ADMIN_GET_TAB_COMPRESS_INFO LEFT OUTER JOIN TABLE( SYSPROC.ADMIN_GET_TAB_DICTIONARY_INFO(CAST( ? AS VARCHAR(128)),CAST( ? AS VARCHAR(128))) ) AS SRC_ADMIN_GET_TAB_DICTIONARY_INFO ON SRC_ADMIN_GET_TAB_COMPRESS_INFO.TABSCHEMA = SRC_ADMIN_GET_TAB_DICTIONARY_INFO.TABSCHEMA AND SRC_ADMIN_GET_TAB_COMPRESS_INFO.TABNAME = SRC_ADMIN_GET_TAB_DICTIONARY_INFO.TABNAME AND SRC_ADMIN_GET_TAB_COMPRESS_INFO.DBPARTITIONNUM = SRC_ADMIN_GET_TAB_DICTIONARY_INFO.DBPARTITIONNUM AND SRC_ADMIN_GET_TAB_COMPRESS_INFO.DATAPARTITIONID = SRC_ADMIN_GET_TAB_DICTIONARY_INFO.DATAPARTITIONID AND SRC_ADMIN_GET_TAB_COMPRESS_INFO.OBJECT_TYPE = SRC_ADMIN_GET_TAB_DICTIONARY_INFO.OBJECT_TYPE

3. The same SQL can be found in T-cd:DBACockpit-> Performance-> Top SQL Statement.

SAP on IBM Db2 for Linux, UNIX, and Windows

db2sysc, high CPU, SAPTOOLS.DBH_COMPRESS_COLLECT, SAPTOOLS.COMPRESSION_TAB , KBA , BC-DB-DB6 , DB2 Universal Database for Unix / NT , Problem