system temporary space not releasing space

nivasreddy3287 · #1 (**permalink**) 03-05-09, 11:30

I have a database which is not releasing the space on system temporary space.I dont know the reason why it is holding space??I think any sorts,joins would take some space in tempspace but I did not understand the reason as to why it is not releasing after the completion of query execution

n_i · #2 (**permalink**) 03-05-09, 12:20

Quote:

Originally Posted by nivasreddy3287

I have a database which is not releasing the space on system temporary space.

Do you mind telling us how you came to that conclusion?

nivasreddy3287 · #3 (**permalink**) 03-05-09, 21:35

When I see the snapshot for tablespaces, I see that used pages are around 110GB which is odd.I thought tempspace would release the space after the sort/join.

db2girl · #4 (**permalink**) 03-06-09, 20:00

tempspace should be released once the application that is using it commits/rollbacks. You can use db2pd -tcbstats option to identify the application that is using (look for the appl handle under the SchemaNn column).

Marcus_A · #5 (**permalink**) 03-06-09, 21:09

Quote:

Originally Posted by nivasreddy3287

When I see the snapshot for tablespaces, I see that used pages are around 110GB which is odd.I thought tempspace would release the space after the sort/join.

Is this a SMS or DMS tablespace? If it is DMS, drop it and recreate as SMS. You are hurting performance by having system temp tablespaces as DMS.

Internet Services

Web Development

Digital Marketing

Consumer Tech