SAP Knowledge Base Article - Preview

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

Symptom

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.


Read more...

Environment

SAP on IBM Db2 for Linux, UNIX, and Windows

Keywords

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

About this page

This is a preview of a SAP Knowledge Base Article. Click more to access the full version on SAP ONE Support launchpad (Login required).

Search for additional results

Visit SAP Support Portal's SAP Notes and KBA Search.