Two tempspaces on database

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

    Two tempspaces on database

    I have the default TEMPSPACE1 (4K) in my database and another 4K
    tempspace (T_0001) of bigger size. A particular query failed with "File
    System full error" db2diag.log shows the following error message

    2006-04-16-20.34.55.088285-240 E1002439A714 LEVEL: Error
    PID : 643166 TID : 1 PROC : db2pclnr 3
    INSTANCE: cardm1in NODE : 003
    FUNCTION: DB2 UDB, buffer pool services, sqlbClnrAsyncWr iteCompletion,
    probe:0
    MESSAGE : ADM6017E The table space "TEMPSPACE1 " (ID "1") is full.
    Detected on
    container
    "/udb/udbdata/cardm1in/cardm1in/NODE0003/SQL00001/SQLT0001.0"
    (ID
    "0"). The underlying file system is full or the maximum
    allowed
    space usage for the file system has been reached. It is also
    possible
    that there are user limits in place with respect to maximum
    file size
    and these limits have been reached.

    My questions
    1. Does this mean that I need to drop the default TEMPSPACE1 tablespace
    so that my query would use the big T_0001 tablespace ?
    2. Will DB2 round robin between temporary table spaces of the same page
    size ?


    PS: Env : UDB 8.1 FP 10, AIX 5.3

    Thanks,
    db2udbgirl.

    Tags: None
    • Mark A
      #2
      Re: Two tempspaces on database

      "db2udbgirl " <db2udbgirl@gma il.com> wrote in message
      news:1145281737 .326757.59920@e 56g2000cwe.goog legroups.com...[color=blue]
      >I have the default TEMPSPACE1 (4K) in my database and another 4K
      > tempspace (T_0001) of bigger size. A particular query failed with "File
      > System full error" db2diag.log shows the following error message
      >
      > 2006-04-16-20.34.55.088285-240 E1002439A714 LEVEL: Error
      > PID : 643166 TID : 1 PROC : db2pclnr 3
      > INSTANCE: cardm1in NODE : 003
      > FUNCTION: DB2 UDB, buffer pool services, sqlbClnrAsyncWr iteCompletion,
      > probe:0
      > MESSAGE : ADM6017E The table space "TEMPSPACE1 " (ID "1") is full.
      > Detected on
      > container
      > "/udb/udbdata/cardm1in/cardm1in/NODE0003/SQL00001/SQLT0001.0"
      > (ID
      > "0"). The underlying file system is full or the maximum
      > allowed
      > space usage for the file system has been reached. It is also
      > possible
      > that there are user limits in place with respect to maximum
      > file size
      > and these limits have been reached.
      >
      > My questions
      > 1. Does this mean that I need to drop the default TEMPSPACE1 tablespace
      > so that my query would use the big T_0001 tablespace ?
      > 2. Will DB2 round robin between temporary table spaces of the same page
      > size ?
      >
      >
      > PS: Env : UDB 8.1 FP 10, AIX 5.3
      >
      > Thanks,
      > db2udbgirl.
      >[/color]

      1. Are you sure that T_0001 is a system temporary tablespace, or could be it
      be a user temporary tablespace?

      2. Yes, if they are both of the same kind (i.e., system temporary
      tablespaces).


        Comment

        • db2udbgirl
          #3
          Re: Two tempspaces on database

          1. Yes it is a system temporary tablespace

          Name = T_0001
          Type = Database managed space
          Contents = System Temporary data
          State = 0x0000
          Detailed explanation:
          Normal

          So to resolve the problem I think that I need to drop the default
          TEMPSPACE1 tablespace show that DB2 would always use the big TEMPSPACE.
          Is this assumption correct ?

          Thanks,
          db2udbgirl.

            Comment

            • Mark A
              #4
              Re: Two tempspaces on database


              "db2udbgirl " <db2udbgirl@gma il.com> wrote in message
              news:1145283886 .019707.232150@ e56g2000cwe.goo glegroups.com.. .[color=blue]
              > 1. Yes it is a system temporary tablespace
              >
              > Name = T_0001
              > Type = Database managed space
              > Contents = System Temporary data
              > State = 0x0000
              > Detailed explanation:
              > Normal
              >
              > So to resolve the problem I think that I need to drop the default
              > TEMPSPACE1 tablespace show that DB2 would always use the big TEMPSPACE.
              > Is this assumption correct ?
              >
              > Thanks,
              > db2udbgirl.
              >[/color]

              Yes, that will work fine if the T_0001 is large enough.

              BTW, SMS is recommended for system temporary tablespaces because it takes
              longer to create a table in DMS (as it optimizes space), and system
              temporary tables need to be created and dropped quickly during the execution
              of an SQL statement.


                Comment

                • db2udbgirl
                  #5
                  Re: Two tempspaces on database

                  oh ok. Thanks for the info. I will look into it.

                  Thanks,
                  db2udbgirl.

                    Comment

                    • mike_li
                      #6
                      Re: Two tempspaces on database

                      Please correct me if I am wrong.

                      can you revoke USE priviledge of TEMPSPACE1 from this user, so the user
                      will have to user the larger tmpspace you created?

                        Comment

                        • Bob [IBM]
                          #7
                          Re: Two tempspaces on database

                          mike_li wrote:[color=blue]
                          > Please correct me if I am wrong.
                          >
                          > can you revoke USE priviledge of TEMPSPACE1 from this user, so the user
                          > will have to user the larger tmpspace you created?
                          >
                          >[/color]
                          The USE privilege cannot be used with SYSTEM temporary table spaces.

                            Comment

                            Previous template Next
                            Working...