Temporary tables

Rathish · #1 (**permalink**) 10-13-09, 12:10

Hi,

When I ran one of the query in acceptance environment it failed with the error -904 with the error code that we need to increase the temporary work file size of the database.After we increased the MAXTEMPS parameter we were able to execute the query well.

As we shouldnt be increasing the MAXTEMPS to maximum everytime we are asked to change the qury now..

Can you guys let me know how to write a qury in such a way that it wil make use of the temporary table for resultant qury data?

tonkuma · #2 (**permalink**) 10-13-09, 12:40

What query are you trying to run?

stolze · #3 (**permalink**) 10-13-09, 13:28

Change the join to a set operation, of course. :-)

Seriously, how do you expect to get an answer if you don't provide any details? From the error code I gather that you are running on DB2 z/OS. Which version? What's the query and what should it do? What's the schema (tables, indexes, ...)? What's the data volume? What's the access plan for your query...

Lenny77 · #4 (**permalink**) 10-13-09, 14:30

Maybe it will work faster with temporary tables which are created in DB, not declared in a run time ?

Lenny

Rathish · #5 (**permalink**) 10-17-09, 13:18

This is the query

SELECT
A.TOT_AMT
,B.START_DT
,C.CHAR_VAL
FROM
ADJ_TY_CHAR D
,FT A
,SA B
LEFT OUTER JOIN CI_SA_TYPE_CHAR C
ON (C.CIS_DIVISION = 'BDNL'
AND C.SA_TYPE_CD = B.SA_TYPE_CD
AND C.CHAR_TYPE_CD = 'VRPLCHTN')
WHERE
B.VERSION = 100
AND B.Cus_id = A.Cus_id
AND A.PARENT_ID = D.ADJ_TYPE_CD
AND A.FT_TYPE_FLG = 'AD'
AND D.CHAR_TYPE_CD = 'COMPSRT'
AND D.CHAR_VAL IN
('X', 'Y', 'Z')
ORDER BY
C.CHAR_VAL
END-EXEC.

In table A we have around 76 million records.The table B has around 50 million records.When I run this query we are getting -904 SQL error that temporary databse work space less.

I want to change the query that it will run in partitions to so that we wont get the temporary space error.

Can you please guide me to write this query in partitions.

DB2Plus · #6 (**permalink**) 10-17-09, 13:38

Code:

FROM
ADJ_TY_CHAR D
,FT A
,SA B
LEFT OUTER JOIN CI_SA_TYPE_CHAR C

You can't use join by comma and left join together in same query without some additional operations.

This is not correct.
You have to change comma operation by Inner Join.

Kara S.

tonkuma · #7 (**permalink**) 10-17-09, 15:32

Quote:

You can't use join by comma and left join together in same query without some additional operations.

More exactly,
you can mix join by comma and left join, but "join by comma" will be executed after "left join".

So, "B LEFT OUTER JOIN C" executed first and result will be kept in temporary tablespace.
This must be waste much of the temporary tablespace.

Quote:

You have to change comma operation by Inner Join.

Yes, you should change the sequence of joins by replacing all comma operations with [INNER] JOIN(s).

DB2Plus · #8 (**permalink**) 10-17-09, 16:00

Thank you, tonkuma !

I met some queries with comma after left join....
You are right, in this case it possible. But who need it ?

The best rule: If you use Left Join - use Inner Join instead of comma.

Kara S.

dav1mo · #9 (**permalink**) 10-17-09, 17:02

??? You mind pointing us to some documentation on that. I have never heard of anything like that in over 10 years of writing SQL. Thanks.

Rathish,
You spoke of the millions of rows in these tables, but you did not speak of how many rows this query will return. Get a count of expected numbers of rows being returned. Also, check your explain plan to see what is taking up your temp space. My guess would be that it is a sort of millions of rows.
Dave Nance

DB2Plus · #10 (**permalink**) 10-17-09, 17:24

Quote:

Originally Posted by dav1mo

??? You mind pointing us to some documentation on that. I have never heard of anything like that in over 10 years of writing SQL. Thanks.

Rathish,
You spoke of the millions of rows in these tables, but you did not speak of how many rows this query will return. Get a count of expected numbers of rows being returned. Also, check your explain plan to see what is taking up your temp space. My guess would be that it is a sort of millions of rows.
Dave Nance

Hi, Dave !
Sometimes it's happen. In our case the query is not good.
He has to change query and try again.

I beleive everything will be Ok.

Kara.

dav1mo · #11 (**permalink**) 10-19-09, 09:27

Kara,
I, believe, you are very wrong about this. The SQL looks fine. Granted, it is not of the flavor a lot of people and tools use today with

Code:

FROM TABLE 1
INNER JOIN TABLE 2
ON ....
LEFT OUTER JOIN TABLE 3
ON ....
INNER JOIN TABLE 4
ON....

The method being used for the query is syntactically correct and common for those of us that have been writing SQL for a decade or more. As far as the comments about the order in which the query is completed will be based completely on the optimizers decision of which access path to use. I have many queries of this type querying tables upwards of 200 million rows and they all perform subsecond, due to the predicates being supplied and the indexing on the table, along with the fact that we are not getting most of the table back with our query.
The problem I see is that the predicates being supplied do not look like they will filter out much data at all. Add to that the fact that the tables are upwards of 50 million rows. I think Rathish is just getting too much data back from this query to sort.
Dave Nance

dav1mo · #12 (**permalink**) 10-19-09, 09:31

Rathish,
If my prior assumptions about your data are correct, then you may have to do as you suggested and breack this into partitions. You can do this by selecting for some range of a column that will limit the amount of data being returned for the query, for instance a likely candidate might be your cus_id column.
Also, please post your final solution so we can take note of your issues/resolution. Thanks.
Dave Nance

Lenny77 · #13 (**permalink**) 10-19-09, 10:13

Quote:

Originally Posted by dav1mo

Kara,
I, believe, you are very wrong about this. The SQL looks fine. Granted, it is not of the flavor a lot of people and tools use today with

Code:

FROM TABLE 1
INNER JOIN TABLE 2
ON ....
LEFT OUTER JOIN TABLE 3
ON ....
INNER JOIN TABLE 4
ON....

The method being used for the query is syntactically correct and common for those of us that have been writing SQL for a decade or more. As far as the comments about the order in which the query is completed will be based completely on the optimizers decision of which access path to use. I have many queries of this type querying tables upwards of 200 million rows and they all perform subsecond, due to the predicates being supplied and the indexing on the table, along with the fact that we are not getting most of the table back with our query.
The problem I see is that the predicates being supplied do not look like they will filter out much data at all. Add to that the fact that the tables are upwards of 50 million rows. I think Rathish is just getting too much data back from this query to sort.
Dave Nance

Dear Dave !

You can't use numbers as qlfrs for table.
About sort is good idea. Maybe you are right about size of the sorted RS.

He can try how your idea is working by removing "order by" from the query....

Comma and Left Join doesn't look professional.

Lenny