We have a job that joins seven tables with GROUP BY and ORDER BY clauses. Database is not big ( < 100 GB) . After running for about 15 mins, the job will fail due to disk full error reported in the temp tablespace which is system-managed. We are using DB2 v9.5 on SUSE Linux.
I did a snapshot on the tables and noticed one of the static tables which contains < 20 records has the rows_read returns 1321419183. Overflow_accesses returns 0 across all tables. Why such a small table will have so high rows_read count? Tried RUNSTATS, REORG and BIND but did not help. Also, added more indexes on the joined columns. The Temp space did not get filled up as quickly but eventually still hit 100% full.
For SELECT queries, is setting optimization level higher, such as 7 would be beneficial as opposed to using the default setting which is 5?
In DB2, do the positions of the small table and large table make any difference?
E.G. SELECT *
FROM tableA, tableB
tableA.col1 = tableB.col1
FROM tableB, tableA
tableB.col1 = tableA.col1
Any recommendations or suggestions would be greatly appreciated.
Thanks in advance for your assistance.
clara use the optimitization level of query with in 5 like use 1 to 4 and chk.
chk for any sorting is taking place or is the query is using the proper indexes mentioned
did u reset ur monitor switches before taking table snapshot tht could contain history of rows read till ur db has recycled.
GROUP BY and ORDER BY cause sorts, which, if the sorted sets are large enough, can spill to temporary tables. You will need to look at the query explain plan, identify these large sorts, and try to avoid them or move them to a later stage in the plan.
There are other things that can cause sort spills as well, which will be evident from the explain plan. You should always start query tuning with looking at its plan.
we can do all the guessing and suggestions you want, but I am betting your SQL is the real culprit. change the names of the columns if you want and post. Have never understood why the SQL itself is secret with some folks out there, when its the data you need to protect.
You need to limit in someway the amount of data in at least one of those sorts or give the system more disk for the temp space.