Question & Answer
Question
When a temporary tablespace is encountering full, how to find application that uses up temporary tablespace ?
Answer
This can be found by tracking 'db2pd -tcbstat' and DB2 application snapshot.
For example, a tablespace has full situation with the following pattern.
db2diag.log pattern :
FUNCTION: DB2 UDB, buffer pool services, sqlbClnrAsyncWriteCompletion, probe:0
MESSAGE : ADM6017E The table space TEMP32SPACE" (ID "13") is full.
Detected on container
"/db2/TEMPSPACE/temp32" (ID "0").
From 'db2pd -db <dbname> -tcbstat' output,
'StoreByte' means size and 'SchemaNm' is the application handle.
(In this example, 4115029842 bytes and application handle 33146.)
TCB Table Stats:
Address TableName SchemaNm Scans UDI RTSUDI PgReorgs NoChgUpdts Reads FscrUpdates Inserts Updates Deletes OvFlReads OvFlCrtes RowsComp RowsUncomp CCLogReads StoreBytes BytesSaved
...
0x070000017D6A0858 13 3 n/a 13 3 TEMP (00013,00003) <33146>< Temp 145065 0 0 0
0x070000017D6A0858 TEMP (00013,00003) <33146>< 0 53441946 53441946 0 0
Then check what is running under the application handle id by DB2 application snapshot.
db2 get snapshot for applications on <dbname>
As temporary tablespace size can fluctuate in short period of time, collect this multiple times in some interval accordingly
Related Information
Was this topic helpful?
Document Information
Modified date:
16 June 2018
UID
swg21992464