Results 1 to 4 of 4
  1. #1
    Join Date
    May 2010
    Posts
    2

    Unanswered: Opinions on expanding IBMDEFAULTBP pagesize

    Aloha,

    We just encountered a problem in using JPA to display some table information with a varchar(6000) field. Sorting causes the use of a TEMP storage table and IBMDEFAULTBP has the default 4096 pagesize. Obviously this causes a problem (SQL1585N)...

    I'm considering increasing the size to 8K but after Googling, I'm a little concerned that there may be upgrade issues with DB2 in the future.
    https://www-304.ibm.com/support/docv...id=swg1IO12416

    So I'm wondering what opinions you DBA's have on this.

    Is it possible to set up an alternate buffer pool and somehow have that used as the TEMP storage Table for sorts?
    Or should I just take the risk and increase the IBMDEFAULTBP pagesize?


    DB2 v9.7 on Linux-64

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    I always create a 32K bufferpool (of small size) and a 32K System Temporary Tablespace in every database. Shouldn't matter what default page size is if you do that. It is OK to leave the default 4K TEMPSPACE1 as it was created.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  3. #3
    Join Date
    May 2010
    Posts
    2

    Thanks!

    Aloha and thanks Marcus,

    That's was a very reasonable suggestion, so that's what I did.
    Works like a charm!


  4. #4
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by GregMalick View Post
    We just encountered a problem in using JPA to display some table information with a varchar(6000) field. Sorting causes the use of a TEMP storage table and IBMDEFAULTBP has the default 4096 pagesize. Obviously this causes a problem (SQL1585N)...

    I'm considering increasing the size to 8K
    Clearly, since you have a table with a 6K column, you must have a tablespace with the page size larger than 4K to contain it and, subsequently, a bufferpool with the same page size.

    The only thing that you are missing is a system temporary tablespace with that page size. If you follow Marcus' approach of 32K you'll need also a bufferpool with that page size.

    Also, you can't change a bufferpool page size; you can only drop and re-create the bufferpool, so "increasing the size to 8K" won't work.

Posting Permissions

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