Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    Join Date
    Oct 2009
    Posts
    3

    Unanswered: Temporary tables

    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?

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    What query are you trying to run?

  3. #3
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    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...
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  4. #4
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963
    Maybe it will work faster with temporary tables which are created in DB, not declared in a run time ?

    Lenny

  5. #5
    Join Date
    Oct 2009
    Posts
    3
    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.

  6. #6
    Join Date
    Jul 2009
    Posts
    150

    Thumbs down change comma operation by Inner Join

    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.

  7. #7
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    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.

    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).

  8. #8
    Join Date
    Jul 2009
    Posts
    150
    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.

  9. #9
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    ??? 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

  10. #10
    Join Date
    Jul 2009
    Posts
    150
    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.

  11. #11
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    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

  12. #12
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    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

  13. #13
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963

    Exclamation Size of Sort is good idea

    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

  14. #14
    Join Date
    Oct 2009
    Location
    Calgary, AB Canada
    Posts
    38
    Quote Originally Posted by Lenny77
    Dear Dave !

    You can't use numbers as qlfrs for table.

    Lenny
    I believe Dave was merely trying proving a point.


    Uhh Dave.... to take it one step further, trying to join tables on '....' will not work either....

  15. #15
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963
    It's better if LEFT JOIN following the INNER JOINs.

    Lenny

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •