Results 1 to 5 of 5
  1. #1
    Join Date
    Sep 2003
    Location
    canada
    Posts
    230

    Unanswered: How can I specify the tablespace with suitable page size for my tables? please help

    I am working with db2 v8.2.2 and aix 5.2

    I have a table in production with 52 mil record and average row length is 42 byte and located in tablespace with 16k page size. I found out I am going a lot of space because:
    255(at most rows in a page)*42 = 10710 byte
    16000-10710 = ~5000

    as you see we loose 5000 byte for every 255 records and you can imagine in 52 mil records we have lost about half GIG.

    I decided put this table in 4k page size tablespace but NLEVELS for index is 4
    if I put in 4k the NLEVEL will be increased and it is not acceptable, please advise what should I do?

    table has been runstats recently.

  2. #2
    Join Date
    Sep 2003
    Posts
    237
    Why not 8k? Also why are you SO concerned about NLEVEL?
    mota

  3. #3
    Join Date
    Sep 2003
    Location
    canada
    Posts
    230
    Quote Originally Posted by dbamota
    Why not 8k? Also why are you SO concerned about NLEVEL?
    1- yes 8k is possible but I should create new temp tablespace 8k and new bufferpool 8k for new tablespace. with 4k and can use default ones.
    2- I have heard and read NLEVEL more than 3 is not good for performace .
    am I right?

  4. #4
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by M_RAS
    2- I have heard and read NLEVEL more than 3 is not good for performace .
    am I right?
    No, NLEVEL is not that important, especially on a table that size. It would help if you could put the index in a different 4K tablespace and a different 4K bufferpool than the table data, so that the index pages will not be flushed out of memory (or at least not flushed out as often as the table pages).
    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
    Location
    canada
    Posts
    230
    Quote Originally Posted by Marcus_A
    No, NLEVEL is not that important, especially on a table that size. It would help if you could put the index in a different 4K tablespace and a different 4K bufferpool than the table data, so that the index pages will not be flushed out of memory (or at least not flushed out as often as the table pages).
    Thank you Marcus_A,
    I am using SMS, so I am not able to have seperate table space for index.

    so other than saving space what is the advantage/disadvantage I repalce the table to 4k table space. we have a lot of update and insert in this table during the batch jobs.

Posting Permissions

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