Temp tablespace - system or user?

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

    Temp tablespace - system or user?

    I have a sql that goes like

    select ............... from table (select ....... from table t where
    <>) as x

    Now this temp table x is supposed to keep 10 K rows at a point of
    time. Where would they be stored? I know the answer is temp
    tablespace, but is it in system temp or user temp?
    All I am trying to do is to create a temp (sys or user) with good
    amount of pages so that these kinds of queries would speed up. I dont
    know the size of which I'd have to create (16, 32k) ? How can we
    estimate this so that I can create a system temp space wth 10 gigs.

    Please help in defining them.

    Ever questioning ,
    Arun
    Tags: None
    • Serge Rielau
      #2
      Re: Temp tablespace - system or user?

      Arun Srinivasan wrote:
      I have a sql that goes like
      >
      select ............... from table (select ....... from table t where
      <>) as x
      >
      Now this temp table x is supposed to keep 10 K rows at a point of
      time. Where would they be stored? I know the answer is temp
      tablespace, but is it in system temp or user temp?
      All I am trying to do is to create a temp (sys or user) with good
      amount of pages so that these kinds of queries would speed up. I dont
      know the size of which I'd have to create (16, 32k) ? How can we
      estimate this so that I can create a system temp space wth 10 gigs.
      In your example my hope would be that there is no TEMP involved
      whatsoever since the access can be pipe-lined.
      Anyway any temps "thrown in" bu DB2, including temps to spill sort
      memory are SYSTEM TEMPs You should define a system temp for each page
      size (4k-32K on DB2 for LUW)

      Cheers
      Serge

      --
      Serge Rielau
      DB2 Solutions Development
      IBM Toronto Lab

        Comment

        • stefan.albert
          #3
          Re: Temp tablespace - system or user?

          Use SMS for this - space in the filesystem will only be used when temp
          is used.
          So put all TEMP into the same filesystem for shared usage.
          When TEMP is not needed any more (SQL/transaction ended) the space
          will be freed.

          Anyway any temps "thrown in" bu DB2, including temps to spill sort
          memory are SYSTEM TEMPs You should define a system temp for each page
          size (4k-32K on DB2 for LUW)
          >
          Cheers
          Serge
          >
          --
          Serge Rielau
          DB2 Solutions Development
          IBM Toronto Lab

            Comment

            • Arun Srinivasan
              #4
              Re: Temp tablespace - system or user?

              On Jul 2, 8:10 am, "stefan.alb ert" <stefan.alb...@ spb.dewrote:
              Use SMS for this - space in the filesystem will only be used when temp
              is used.
              So put all TEMP into the same filesystem for shared usage.
              When TEMP is not needed any more (SQL/transaction ended) the space
              will be freed.
              >
              >
              >
              Anyway any temps "thrown in" bu DB2, including temps to spill sort
              memory are SYSTEM TEMPs You should define a system temp for each page
              size (4k-32K on DB2 for LUW)
              >
              Cheers
              Serge
              >
              --
              Serge Rielau
              DB2 Solutions Development
              IBM Toronto Lab- Hide quoted text -
              >
              - Show quoted text -
              Thanks both..

                Comment

                Previous template Next
                Working...