Finding what’s using TEMP storage using db2pd

Sometimes a new application gets into production with, should I say, not fully tested with undesirable access plan and the first the DBA is aware of this is when you see sudden growth in the temporary storage tablespace filesystem. This can also fill the filesystem and cause application failures across the database. There is a very easy way to find the culprit. Launch db2pd for the database in question as shown in the following example:

pgunning@[lhp200hs]:
(/home/hsprd/sqllib/db2dump)> db2pd -d <dbname> -tcbstats > tcb1101.txt
pgunning@[lhp200hs]:
(/home/hsprd/sqllib/db2dump)> cat tcb1101.txt | grep -i TEMP | more

The schema column, in this case shows the application handle <41555> is using two temp storage tables along with how much space is being used. You can use this iteratively to see if space usage is growing an determine if action is needed to preclude the tempspace1 filesystem from filling up. You can identify the application by using dmctop, db2pd or mon_get table function.