Results 1 to 15 of 15
  1. #1
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11

    Unanswered: temporary tablespace limit

    we are using db2 ese on luw 9.5 fp8 on plinux
    having a database with tablespaces of different page sizes and also temporary tablespaces of different page sizes.
    we have a specific large table, residing in a 32k tablespace.
    we run a query that returns a large result set. we are getting the message that the size of the temp.tablesize is hitting the physical limit (according the limits in the doc). but the query uses a 4k temp.tablespace. if it would have used a 32k, we should not have had this problem.
    the question is : can we force, by a condition or setting.. that a query uses a larger page size for the temp.tablespace.
    you can not drop the 4k tempspace, as it is the only available of this size.
    any idea ? thanks for all help/comments
    Best Regards, Guy Przytula
    Database Software Consultant
    Good DBAs are not formed in a week or a month. They are created little by little, day by day. Protracted and patient effort is needed to develop good DBAs.
    Spoon feeding : To treat (another) in a way that discourages independent thought or action, as by overindulgence.
    DB2 UDB LUW Certified V7-V8-V9-V9.7-V10.1-V10.5 DB Admin - Advanced DBA -Dprop..
    Information Server Datastage Certified
    http://www.infocura.be

  2. #2
    Join Date
    Oct 2004
    Location
    DELHI INDIA
    Posts
    338
    Please create a 32 K page Temp tablespace i.e. TEMPSYS32 and corresponding bufferpool with 32 K pagesize and see the response of the query from db2top.

    From your post, it appears in your system only 4 K pagesize Tempspace is existing.

    Jayanta Datta
    DB2 UDB DBA
    IBM India, Global Delivery
    New Delhi

  3. #3
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    no - not correct
    we have a tempspace of 4K 8k and 32k
    we would have expected it would use the last one, but it is not.
    why would we ask how to force this if none was available ? there would be no choice.
    Best Regards, Guy Przytula
    Database Software Consultant
    Good DBAs are not formed in a week or a month. They are created little by little, day by day. Protracted and patient effort is needed to develop good DBAs.
    Spoon feeding : To treat (another) in a way that discourages independent thought or action, as by overindulgence.
    DB2 UDB LUW Certified V7-V8-V9-V9.7-V10.1-V10.5 DB Admin - Advanced DBA -Dprop..
    Information Server Datastage Certified
    http://www.infocura.be

  4. #4
    Join Date
    Oct 2004
    Location
    DELHI INDIA
    Posts
    338
    great that you have all the available pagesize tempspace. I came across similar issue last year when one of the user queries were using too much of tempspace with 4K Page, and we thought of creating a 32 K pagesize tempspace as the query was fetching huge number of records towards the tempspace and for that we created a separate bufferpool too. We ensured that the BP size was large enough and the tempspace with 32 K pagesize was having sufficient number of pages.

    As much I remember, there was no way to force the optimizer to use a particular pagesize ( say 4K) tempspace. DB2 will prefer the one which can give a better cost.

    Jayanta Datta
    DB2 UDB DBA
    IBM India, Global Delivery
    New Delhi

  5. #5
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    so how do we overcome the problem - hitting the max limit size of a tablespace ?
    this query can not be run anymore.
    Best Regards, Guy Przytula
    Database Software Consultant
    Good DBAs are not formed in a week or a month. They are created little by little, day by day. Protracted and patient effort is needed to develop good DBAs.
    Spoon feeding : To treat (another) in a way that discourages independent thought or action, as by overindulgence.
    DB2 UDB LUW Certified V7-V8-V9-V9.7-V10.1-V10.5 DB Admin - Advanced DBA -Dprop..
    Information Server Datastage Certified
    http://www.infocura.be

  6. #6
    Join Date
    Feb 2009
    Location
    Germany
    Posts
    23
    you can create the new 4K tablespace that is enough large for your query and drop the old small 4k tablespace.

  7. #7
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    as you can see in the doc : there is a physical limit of size in respect to a tablespace
    the tempspace is sms on a filesystem of 1T which should be enough for this query
    see : Table space page sizes
    for 4k pages the tempspace is limited to 64G but for 32k the limit is 512G
    Best Regards, Guy Przytula
    Database Software Consultant
    Good DBAs are not formed in a week or a month. They are created little by little, day by day. Protracted and patient effort is needed to develop good DBAs.
    Spoon feeding : To treat (another) in a way that discourages independent thought or action, as by overindulgence.
    DB2 UDB LUW Certified V7-V8-V9-V9.7-V10.1-V10.5 DB Admin - Advanced DBA -Dprop..
    Information Server Datastage Certified
    http://www.infocura.be

  8. #8
    Join Date
    Oct 2004
    Location
    DELHI INDIA
    Posts
    338
    Did you create a large enough bufferpool for the 32 K pagesize Tempspace so that the query can get tempted for the 32 K one. I will update more in case come across any other helpful info on this.

    Jayanta Datta
    DB2 UDB DBA
    IBM India, Global Delivery
    New Delhi

  9. #9
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Try assigning a significantly larger bufferpool to the 32K system temporary tablespace than to the 4K tablspace - this should make optimizer prefer the former.

  10. #10
    Join Date
    Feb 2009
    Location
    Germany
    Posts
    23
    have you found a workaround?

  11. #11
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    I will try the suggestion of n_i "Try assigning a significantly larger bufferpo......"
    Best Regards, Guy Przytula
    Database Software Consultant
    Good DBAs are not formed in a week or a month. They are created little by little, day by day. Protracted and patient effort is needed to develop good DBAs.
    Spoon feeding : To treat (another) in a way that discourages independent thought or action, as by overindulgence.
    DB2 UDB LUW Certified V7-V8-V9-V9.7-V10.1-V10.5 DB Admin - Advanced DBA -Dprop..
    Information Server Datastage Certified
    http://www.infocura.be

  12. #12
    Join Date
    Jan 2009
    Location
    Zoetermeer, Holland
    Posts
    746
    What if you dropped the 4k temptablespace? DB2 would be forced to use the other ones...... Bit risky, so test 1st I'd say

  13. #13
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    this can NOT be dropped, as it is the only for that page size..
    Best Regards, Guy Przytula
    Database Software Consultant
    Good DBAs are not formed in a week or a month. They are created little by little, day by day. Protracted and patient effort is needed to develop good DBAs.
    Spoon feeding : To treat (another) in a way that discourages independent thought or action, as by overindulgence.
    DB2 UDB LUW Certified V7-V8-V9-V9.7-V10.1-V10.5 DB Admin - Advanced DBA -Dprop..
    Information Server Datastage Certified
    http://www.infocura.be

  14. #14
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,367
    Are you sure 4K temp can't be dropped if you already have 32K temp?

  15. #15
    Join Date
    Oct 2004
    Location
    DELHI INDIA
    Posts
    338
    on 9.5 the first default tempspace gets created with 4 K pagesize. So you can keep on creating other tempspace with higher pagesize say 8K / 16 K, but at any point at least one 4 K tempspace should exist.
    Last edited by JAYANTA_DATTA; 08-01-11 at 13:55. Reason: typo

    Jayanta Datta
    DB2 UDB DBA
    IBM India, Global Delivery
    New Delhi

Posting Permissions

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