It's all about the answers!

Ask a question

Query consumes 100G of temporary tablespace

Kevin Ramer (4.5k9185200) | asked Oct 23 '12, 9:19 a.m.

Twice this week our AIX DB2 server has hit transient full file system which I've traced most recently to a query that starts this way (or at least what's reported).

 with ctx(ctx_uuid) as (select CONTEXT_UUID from REPOSITORY.CONTEXT_MEMBERS where MEMBER_UUID in('_8lNyYNwSEd2pIJ5QVwgQGg', '_E-k30VfXEd6KGeozX9EKiQ')  union  values ('_E-k30VfXEd6KGeozX9EKiQ')) select t1.ITEM_ID, t1.CONTEXT_ID, t1.MODIFIED, t1.STATE_ID, SUM(t1.ID), t1.INTERNAL_STATE, t1.INTERNAL_RESOLUTION, t1.RESOLUTION_DATE,

The temporary tablespace is mentioned in the DB2 diagnostic log.  This condition is a rare occurrence most often triggered by a poor query (e.g. a join of tables w poor or no join criteria along with order by).  The total sql is several hundred characters long, Time of occurrence 17:20 pm so it's not likely to have been the dw stuff. 

Anyone out there recognize this SQL ?

One answer

permanent link
Kevin Ramer (4.5k9185200) | answered Oct 26 '12, 10:22 a.m.
Ok,  I tracked the contributor via those UUID and turns out he was trying to create BIRT report that should have returned 11 items but obviously the query was a bit loose ! =:-o

Your answer

Register or to post your answer.

Dashboards and work items are no longer publicly available, so some links may be invalid. We now provide similar information through other means. Learn more here.