Query consumes 100G of temporary tablespace
Hi,
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
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
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.