IBM Support

How to find application that uses up temporary tablespace

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

[{"Product":{"code":"SSEPGG","label":"Db2 for Linux, UNIX and Windows"},"Business Unit":{"code":"BU058","label":"IBM Infrastructure w\/TPS"},"Component":"Database Objects\/Config - Tablespaces","Platform":[{"code":"PF002","label":"AIX"},{"code":"PF010","label":"HP-UX"},{"code":"PF016","label":"Linux"},{"code":"PF027","label":"Solaris"},{"code":"PF033","label":"Windows"}],"Version":"9.8;9.7;9.5;10.1;10.5;11.1","Edition":"Advanced Enterprise Server;Advanced Workgroup Server;Enterprise Server;Express;Express-C;Personal;Workgroup Server","Line of Business":{"code":"LOB10","label":"Data and AI"}}]

Document Information

Modified date:
16 June 2018

UID

swg21992464