DB2 Performance Issue

Collapse
This topic is closed.
X
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • T.C. Sheng
    #1

    DB2 Performance Issue

    During the execution a sql query, DB2 generates a temporary file in
    the temp table space which is over 6Gig. Does anyone know how to
    reduce the size of this temporary file? It is possible to reduce its
    size by tuning the database configurations?

    T.C. Sheng
    TCSHENG@SYSVIEW .COM.TW
    Tags: None
    • Philip Nelson
      #2
      Re: DB2 Performance Issue

      On Tue, 09 Dec 2003 03:09:17 -0800, T.C. Sheng wrote:
      [color=blue]
      > During the execution a sql query, DB2 generates a temporary file in
      > the temp table space which is over 6Gig. Does anyone know how to
      > reduce the size of this temporary file? It is possible to reduce its
      > size by tuning the database configurations?
      >
      > T.C. Sheng
      > TCSHENG@SYSVIEW .COM.TW[/color]

      More likely by tuning the database or rewriting the query.

      But we'd need more info - suggest the query SQL and table and index
      definitions from describe (the index ones with column details) and the
      number of rows in the tables.

      Phil


        Comment

        • AK
          #3
          Re: DB2 Performance Issue

          T.C. Sheng,

          if you post DDL, the query and the plan, you might get some useful advice.
          This could be a cross join, for example.

            Comment

            • T.C. Sheng
              #4
              Re: DB2 Performance Issue

              Philip Nelson <gulfvuk@yahoo. co.uk> wrote in message news:<pan.2003. 12.09.12.27.43. 600321@yahoo.co .uk>...[color=blue]
              > On Tue, 09 Dec 2003 03:09:17 -0800, T.C. Sheng wrote:
              >[color=green]
              > > During the execution a sql query, DB2 generates a temporary file in
              > > the temp table space which is over 6Gig. Does anyone know how to
              > > reduce the size of this temporary file? It is possible to reduce its
              > > size by tuning the database configurations?
              > >
              > > T.C. Sheng
              > > TCSHENG@SYSVIEW .COM.TW[/color]
              >
              > More likely by tuning the database or rewriting the query.
              >
              > But we'd need more info - suggest the query SQL and table and index
              > definitions from describe (the index ones with column details) and the
              > number of rows in the tables.
              >
              > Phil[/color]

              This query joins four tables in which three of these tables contain
              2.5 million rows and the fourth has 12 million rows. Each table has
              only one index base on a single primary key column. Actually, this
              query is not complex in any sense, just the result is very large. I
              was told that by increase the sort heap might help reduce the size of
              the temp file. But, I am not sure how to determine a the sort heap
              size "scientifically "

                Comment

                Previous template Next
                Working...