It's all about the answers!

Ask a question

Query consumes 100G of temporary tablespace

Kevin Ramer (4.5k6171190) | 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.5k6171190) | 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.