Troubleshooting
Problem
The user wants to drop some columns from an empty table, as well as alter a column data type of it, however, the "alter table alter column" statement takes 30 seconds to 2 minutes to complete. Why does the alter table alter column statement run so slowly?
Symptom
Checking db2trc collected with "-t" option:
#1. You can see many calls to the following functions including disk read(i.e. sqloReadBlocks):
$ grep -c "sqloReadBlocks entry" db2trc.flw
34472$ grep -c "sqliMoveToNextLeaf entry" db2trc.flw
34354$ grep -c "sqliLockDeletedRR entry" db2trc.flw
1504170
Note: sqliLockDeletedRR indicates Db2 is on going cleanup the pseudo deleted key.
#2. You can see the disk reads are on table SYSIBM.SYSCOLDIST:
$ grep -A 18 "entry DB2 UDB Common Trace API sqlbfix fnc" db2trc.fmt | grep "Pagekey" | awk '{print $1 " " $2 }' | sort -nr |uniq -c | sort -nr
60632 Pagekey: {pool:0;obj:264;type:65}
34378 Pagekey: {pool:0;obj:264;type:1}
20 Pagekey: {pool:0;obj:6;type:0}
18 Pagekey: {pool:0;obj:6;type:65}
18 Pagekey: {pool:0;obj:6;type:1}
1 Pagekey: {pool:0;obj:6;type:64}>>> db2pd -d <db name> -tcb <<<
TCB Table Information:
Address TbspaceID TableID PartID MasterTbs MasterTab TableName SchemaNm ObjClass DataSize LfSize LobSize XMLSize IxReqRebld
...
0x0A0003119A33D930 0 264 n/a 0 264 SYSCOLDIST SYSIBM Perm 29766 0 0 0 No
Checking the stack:
#3. You can see the agent is busy on cleaning the table statistics(i.e. sqlrlalt -> sqlrlCleanTableStatInfo -> deleteRows), plus finding #2, you know Db2 is busy on cleaning the pseudo deleted keys:
0x09000000000414E4 pread64 + 0x44
0x0900000019A819A8 sqlbReadPageInternal + 0x1308
0x0900000019A81690 sqlbReadPageInternal + 0xFF0
0x0900000019A81690 sqlbReadPageInternal + 0xFF0
0x0900000019A783EC sqlbGetPageFromDisk__FP11SQLB_FIX_CBi + 0xA88
0x0900000019A77C14 sqlbGetPageFromDisk__FP11SQLB_FIX_CBi + 0x2B0
0x090000001968F308 sqlbfix__FP11SQLB_FIX_CB + 0x2020
0x0900000019685B24 sqlifix__FP7SQLI_CBP14SQLI_PAGE_DESCUiiPUl + 0x1CC
0x090000001A138424 @161@sqliMoveToNextLeaf__FP7SQLI_CBP12SQLI_KEYDATAi + 0x20B8
0x09000000194B6ED8 @161@sqliLockUntilStable__FP7SQLI_CBiP11SQLI_SFGLOB + 0x9E4
0x09000000194BF948 @161@sqliLockEndOfRange__FP7SQLI_CBP12SQLI_KEYDATAP11SQLI_SFGLOB + 0x99C
0x09000000191A12A8 @161@sqliProcLeafNotNorm__FP7SQLI_CBi + 0x6C
0x0900000019671778 sqldRowFetch__FP8sqeAgentP8SQLD_CCBUlT3PP10SQLD_VALUEP8SQLZ_RIDT3P12SQLD_ID_LISTP9SQLP_LSN8 + 0xBC
0x090000001968BD60 sqldRowFetch__FP8sqeAgentP8SQLD_CCBUlT3PP10SQLD_VALUEP8SQLZ_RIDT3P12SQLD_ID_LISTP9SQLP_LSN8 + 0x25E4
0x0900000018DAC7D4 fetch__16sqlrlCatalogScanFv + 0x590
0x0900000018DAC7D4 fetch__16sqlrlCatalogScanFv + 0x590
0x0900000018DAC7D4 fetch__16sqlrlCatalogScanFv + 0x590
0x09000000191928DC deleteRows__16sqlrlCatalogScanFv + 0x460
0x090000001D0CBA58 sqlrlalt__FP8sqlrr_cbPUcsT2T3T2T3T2T3P8sqlrg_pdP17sqlrl_table_parmsilP18sqlrg_datapartinfoPbbP9sqlnq_ftbT16_ + 0x17338 --> sqlrlCleanTableStatInfo
0x090000001D0BB148 sqlrlalt__FP8sqlrr_cbPUcsT2T3T2T3T2T3P8sqlrg_pdP17sqlrl_table_parmsilP18sqlrg_datapartinfoPbbP9sqlnq_ftbT16_ + 0x6A28
0x090000001D104F18 sqlnq_alter_table_end__FP9sqlnq_qtb + 0x3FD8
0x090000001D11987C sqlnq_alter_table_stmt__FPP8stknode_i10actiontypePUcP3loc + 0x1DC
0x0900000019A819A8 sqlbReadPageInternal + 0x1308
0x0900000019A81690 sqlbReadPageInternal + 0xFF0
0x0900000019A81690 sqlbReadPageInternal + 0xFF0
0x0900000019A783EC sqlbGetPageFromDisk__FP11SQLB_FIX_CBi + 0xA88
0x0900000019A77C14 sqlbGetPageFromDisk__FP11SQLB_FIX_CBi + 0x2B0
0x090000001968F308 sqlbfix__FP11SQLB_FIX_CB + 0x2020
0x0900000019685B24 sqlifix__FP7SQLI_CBP14SQLI_PAGE_DESCUiiPUl + 0x1CC
0x090000001A138424 @161@sqliMoveToNextLeaf__FP7SQLI_CBP12SQLI_KEYDATAi + 0x20B8
0x09000000194B6ED8 @161@sqliLockUntilStable__FP7SQLI_CBiP11SQLI_SFGLOB + 0x9E4
0x09000000194BF948 @161@sqliLockEndOfRange__FP7SQLI_CBP12SQLI_KEYDATAP11SQLI_SFGLOB + 0x99C
0x09000000191A12A8 @161@sqliProcLeafNotNorm__FP7SQLI_CBi + 0x6C
0x0900000019671778 sqldRowFetch__FP8sqeAgentP8SQLD_CCBUlT3PP10SQLD_VALUEP8SQLZ_RIDT3P12SQLD_ID_LISTP9SQLP_LSN8 + 0xBC
0x090000001968BD60 sqldRowFetch__FP8sqeAgentP8SQLD_CCBUlT3PP10SQLD_VALUEP8SQLZ_RIDT3P12SQLD_ID_LISTP9SQLP_LSN8 + 0x25E4
0x0900000018DAC7D4 fetch__16sqlrlCatalogScanFv + 0x590
0x0900000018DAC7D4 fetch__16sqlrlCatalogScanFv + 0x590
0x0900000018DAC7D4 fetch__16sqlrlCatalogScanFv + 0x590
0x09000000191928DC deleteRows__16sqlrlCatalogScanFv + 0x460
0x090000001D0CBA58 sqlrlalt__FP8sqlrr_cbPUcsT2T3T2T3T2T3P8sqlrg_pdP17sqlrl_table_parmsilP18sqlrg_datapartinfoPbbP9sqlnq_ftbT16_ + 0x17338 --> sqlrlCleanTableStatInfo
0x090000001D0BB148 sqlrlalt__FP8sqlrr_cbPUcsT2T3T2T3T2T3P8sqlrg_pdP17sqlrl_table_parmsilP18sqlrg_datapartinfoPbbP9sqlnq_ftbT16_ + 0x6A28
0x090000001D104F18 sqlnq_alter_table_end__FP9sqlnq_qtb + 0x3FD8
0x090000001D11987C sqlnq_alter_table_stmt__FPP8stknode_i10actiontypePUcP3loc + 0x1DC
Document Location
Worldwide
[{"Line of Business":{"code":"LOB10","label":"Data and AI"},"Business Unit":{"code":"BU058","label":"IBM Infrastructure w\/TPS"},"Product":{"code":"SSEPGG","label":"Db2 for Linux, UNIX and Windows"},"ARM Category":[{"code":"a8m500000008PkqAAE","label":"Performance"}],"ARM Case Number":"TS003994298","Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"All Version(s)"}]
Log InLog in to view more of this document
This document has the abstract of a technical article that is available to authorized users once you have logged on. Please use Log in button above to access the full document. After log in, if you do not have the right authorization for this document, there will be instructions on what to do next.
Was this topic helpful?
Document Information
Modified date:
01 May 2025
UID
ibm16324311