Results 1 to 7 of 7
  1. #1
    Join Date
    Sep 2003
    Posts
    85

    Unanswered: System Temporary Tablespaces

    We're running UDB 8.2 on AIX 5.2 .

    Until recently we only had tablespaces of 4k pagesize and 1 system temporary tablespace of 4k pagesize.

    We've just implemented some tablespaces with 32k pagesize.

    The question is whether UDB truly requires/expects a system temporary tablespace of 32k pagesize should there be sort overflow to disk for the 32k pagesize tables/tablespaces or will UDB see if the sorted rows can fit in the 4k pagesize system temporary tablespace, then UDB will be a happy camper.

    TIA,
    Ruby

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    If you have a 32K page user tablespace, you need a 32K system temporary tablespace.
    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
    Sep 2003
    Posts
    85
    That's what I thought and implemented (and is such documented in Admin: Implementation). However, someone suggested that if a 32k pagesize system temporary tablespace didn't exist, UDB might, if possible, use a smaller system temporary tablespace ???

  4. #4
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    If you have a 32K page user tablespace, you need a 32K system temporary tablespace.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  5. #5
    Join Date
    Sep 2003
    Posts
    85
    Stay tuned ...
    I just spoke with IBM and I was told that if a system temporary tablespace of 32k pagesize doesn't exist, UDB will try to use, let's say, a 4k pagesize system temporary tablespace.
    I then asked when/how will an SQL1584N message get issued. I await feedback from IBM.
    Ruby

  6. #6
    Join Date
    Sep 2003
    Posts
    85
    IBM feedback:
    If you search on our V8.1 Information Center.

    http://publib.boulder.ibm.com/infoce...help/index.jsp

    --Search on: tempspace row size

    --Choose: Table space design





    If a database uses more than one temporary table space and a new temporary
    object is needed, the optimizer will choose an appropriate page size for
    this object. That object will then be allocated to the temporary table
    space with the corresponding page size. If there is more than one
    temporary table space with that page size, then the table space will be
    chosen in a round-robin fashion. In most circumstances, it is not
    recommended to have more than one temporary table space of any one page
    size.
    If queries are running against tables in table spaces that are defined
    with a page size larger than the 4 KB default (for example, an ORDER BY on
    1012 columns), some of them may fail. This will occur if there are no
    temporary table spaces defined with a larger page size. You may need to
    create a temporary table space with a larger page size (8 KB, 16 KB, or 32
    KB). Any DML (Data Manipulation Language) statement could fail unless
    there exists a temporary table space with the same page size as the
    largest page size in the user table space.
    You should define a single SMS temporary table space with a page size
    equal to the page size used in the majority of your user table spaces.
    This should be adequate for typical environments and workloads.


    You will not find any documentation on a 'scenario' of how or why you
    would encounter SQL1584N.
    The only docuumentation you'll find on this is "Message Reference" or
    "Information Center".

  7. #7
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    If you have a 32K page size because your row size requires it, then you will need a 32K system temporary tablespace.

    If your row size is less than 4K, but you defined a 32K page size for the tablespace, the you might be able to get away without a 32K system temporary tablespace.

    I don't understand why you even care about this. Just define the 32K system temporary tablespace as SMS and DB2 will use it if necessary, but will not use any disk space when not being used.

    I always create one very small 32K bufferpool and one 32K system temproary tablespace (SMS) for every database. You never know when someone will want to join a lot of tables together.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

Posting Permissions

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