Unanswered: Issue with query used in view, which is over running the space
We have a view, created using an select query. When I run (select count(*) the view) or just run the (select query used inthe view seperately) it is running for a while untill the space is filled up and throwing an error below:
db2 "select count(*) from adsiw.v_adsiw_summ_data with ur"
SQL0968C The file system is full. SQLSTATE=57011
maybe the view is based on joins or other and tempspace is filling up filesystem
describe the view - db2level - platform
Best Regards, Guy Przytula
Database Software Consultant
Good DBAs are not formed in a week or a month. They are created little by little, day by day. Protracted and patient effort is needed to develop good DBAs.
Spoon feeding : To treat (another) in a way that discourages independent thought or action, as by overindulgence.
DB2 UDB LUW Certified V7-V8-V9-V9.7-V10.1-V10.5 DB Admin - Advanced DBA -Dprop..
Information Server Datastage Certified http://www.infocura.be
Seems that the optimizer underestimates the temp space needed for an access plan chosen.
It's better to understand why, but you can try to influence the optimizer by setting the following registry variable as well: DB2_OPT_MAX_TEMP_SIZE
There still isn't much we can tell you with the info you have provided. My guess would be you are missing an index on one of these tables you are left outer joining to, Your main table is very large and from what you have shown us thus far you are not supplying any limiting criteria. Have you run an explain to see what type of access path you are getting to each of the tables? I would think you have one or more tablespace scans and probably some materialized result sets being generated.
As I said previously, we would need to know table structures, including what indexes exist. The visual explain info would help tremendously.
If removed whole GROUP BY clause(consequently remove MAX functions from MAX(BMS.OPPTY_NAME) AS PROJECT_NAME, MAX(CUST.CUSTOMER_NAME), MAX(CUST.UPPER_CUSTOMER_NAME) )
and DISTINCT keyword,
what duplicated rows did you got?
If grouping was really neccesary,
it might imply that there might be multiple OPPTY_NAME for a OPPORTUNITY_ID or multiple CUST.CUSTOMER_NAME(or CUST.UPPER_CUSTOMER_NAME) for a CUST.CUSTOMER_NUMBER.
(1) BMS.OPPORTUNITY_ID was a grouping column.
And, "MAX(BMS.OPPTY_NAME) AS PROJECT_NAME" was in select list.
I guessed OPPTY_NAME correspond to OPPORTUNITY NAME.
If my guess was right,
it might be unusual there are multiple OPPTY_NAME(i.e. OPPORTUNITY NAME) for a OPPORTUNITY_ID.
(2) Same guess might be possible for BMS.CUSTOMER_NUMBER and MAX(CUST.CUSTOMER_NAME) or MAX(CUST.UPPER_CUSTOMER_NAME).
There was a condition "ON BMS.CUSTOMER_NUMBER = CUST.CUSTOMER_NUMBER",
then there might be multiple CUST.CUSTOMER_NAME or CUST.UPPER_CUSTOMER_NAME for a BMS.CUSTOMER_NUMBER(same as CUST.CUSTOMER_NUMBER).
The explain info is just as I had guessed above. Please take note from your explain the table scans you are getting. Can you create indexes for those tables on the columns that you are using in your joins? In the View SQL you have SELECT DISTINCT, is that really needed, considering that you are performing a group by? When using the view, you should be providing indexable input for the main table(BMSIW.BMSIW_NON_FINANCIAL BMS) and no input for the other tables. Reason for the last part of that statement is that any criteria to filter rows from the LEFT OUTER JOINED tables on the view will then cause an inner join to the resultset of the left outer join. This may/may not cause a difference in the data returned and will kill your performance. Robert Catterall wrote about LEFT OUTER JOINs several years ago and he included links to white papers that Terry Purcell had written a couple years before, which would give you some great info to ensure you are using them properly.
I think taking care of the lack of indexes and providing valid inputs you will be able to run this query with no problems.