Page 1 of 3 123 LastLast
Results 1 to 15 of 42
  1. #1
    Join Date
    Dec 2008
    Location
    Chennai
    Posts
    111

    Red face Unanswered: db2 tablespace issue - help me

    Hello,

    DB2 Version 8 FP12 , Windows 2003.
    HADR Enviornment.


    One of my tablespaces contain only indexes for one big table (contain over 85 million rows) which is in another table space.
    This table space is DMS and 4K pages and contain 3 containers (each over 20GB)

    The TS which contain indexes has only 14% free. The HWM is very high. I tried to reduce with IBM help and it was not success. Now I am planning to create a two new TS for the table and indexes with bigger page size.

    Question 1. How can I choose the correct page size (8,16 or 32K)?
    Question 2. Do I need to create a bufferpool with the same page size?
    Question 3. As HADR is in place, i dont want to use the LOAD for the data migration. So Import/Export or CURSOR is suitable for my enviornment?
    Question 4 Before I start this above options, i am thinking to reorg the table. due to the HWM and the less space, will reorg cause any issues? what are the reorg parameters can be used to get maximum results?

    Please advise me. Thanks, Gunas.

  2. #2
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,369
    If this index tablespace is only used to store indexes (no tables) and all indexes are for one table only (one "index object" in the tablespace), then I believe that the HWM should be the same or very close to the number of used pages. If the index tablespace contains indexes for more than one table, then the only thing db2dart /lhwm may suggest is to drop and recreate the "index object" for some tables. If the HWM is the same or close to the number of used pages and the tablespace size is close to the limit, then you need to go to 8K in v8.

    What about data tablespace? Is it close to the limit? Do you really need all indexes defined on the table? Can you get rid of some of them (may be some are not used or redundant)? If you can, then you may drop all indexes defined on the table and then recreate the ones you really need. This way, you don't have to drop and recreate the table. This will buy you some time until you can migrate to v9 and use large tablespaces. Think about this option...

  3. #3
    Join Date
    Dec 2008
    Location
    Chennai
    Posts
    111

    Table space issue

    Thanks for the advise.
    The TS id 4 contain only 1 table and the TS id 10 contain 7 indexes for that table. Please see the detail below. I have already droped and recreated all indexes and i could not recover more space as expected.


    Tablespace ID = 4
    Type = Database managed space
    Name = DATASPACE1
    Total pages = 16777216
    Useable pages = 16777152
    Used pages = 13000544
    Free pages = 3776608
    High water mark (pages) = 15209792
    Page size (bytes) = 4096
    Extent size (pages) = 32
    Prefetch size (pages) = 32
    Number of containers = 2
    ---------------------------------------------------

    Tablespace ID = 10
    Name = INDEXSPACE03
    Type = Database managed space
    Contents = Any data
    State = 0x0000
    Detailed explanation:
    Normal
    Total pages = 16112640
    Useable pages = 16112592
    Used pages = 13629376
    Free pages = 2483216
    High water mark (pages) = 13629376
    Page size (bytes) = 4096
    Extent size (pages) = 16
    Prefetch size (pages) = 32
    Number of containers = 3

  4. #4
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,369
    Your main concern should be the tablespace getting close to the maximum limit of 64GB. The total pages of tablespace ID 4 is at the max and tablespace ID 10 is very close to the max (you could still add a bit more space to it). You don't need to worry about the HWM in your case. You may be able to reduce it for ID 4, but this should not be your main concern.

    Is this table changing a lot, do you add a lot of data? Is there some old data you can purge? If it doesn't change a lot, then you should be ok for some time and wait until you migrate to v9. If you add new data and there is no way to purge old, then you need to go to the next page size by creating the bufferpools, tablespaces, unloading and reloading the data (load with copy yes should be ok in HADR env).

    But if you don't add lots of data, then I'd suggest to wait until you migrate to v9 and convert to large tablespaces.
    Last edited by db2girl; 08-09-09 at 11:27.

  5. #5
    Join Date
    Dec 2008
    Location
    Chennai
    Posts
    111

    me and my tablespace

    Thanks.
    Yes, this is the main table. we add average 1GB worth of data every week. we dont delete any rows from this table. so i cant purge any.

    So only way to resolve this, i think that i have to create a new tablespace with new 8K page. I have never done this before. i will try this first on our test server.

    One more question.
    I am tring to REORG all indexes (INPLACE) and after 35min the REORG was failed with transaction LOG full message. Our primary log =60 Secondary is =40. Logsize=10MB.
    Do i need to increase the number of logs ? any idea that upto how much i can go?

    Thanks,

  6. #6
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,369
    You mentioned inplace, are you doing table reorg or index reorg. Inplace table reorg is recoverable and so it needs to be logged and can cause a significant amount of logging to be performed. Hard to tell how many logs it will need... (take a look at infinite logging - logsecond = -1 in the manual). You don't have enough free space in the tablespace so I think you need to use a temporary tablespace during reorg (can't use for inplace).

    Also, why are you doing reorg if you plan to move them to a new set of tablespaces? You can use reorgchk to see if you really need to do a reorg.

  7. #7
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    1. You need to factor in the maximum size of the tablespace for each page size, as you are aware.

    2. Yes, you will need a bufferpool with the appropriate page size.

    3. Make sure you use COMMITCOUNT parm on import to do periodic commits (1000 is fine) so that your logs do not fill up.

    4. If you are exporting and importing, I don't know why you need to do a reorg. You can specify an "order by" on the export if you want to.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  8. #8
    Join Date
    Dec 2008
    Location
    Chennai
    Posts
    111

    Thanks Marcus & db2girl

    I have dropped one index from INDEXTABLEspace (after talking to application providers). That perticular index size is around 12GB.

    But the LIST TS SHOW DETAIL commannd still shows same as before. No change of free pages. Thats why I am trying to REORG all the INDEXES for the table.

    Any idea, how can i recoverd the pages?

    Thanks,

  9. #9
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,369
    This is expected. Because pages from any index on the table can be interspersed throughout the index object, when an index is dropped, its pages remain allocated (but empty) to the index object (to be reused by other indexes on the table). It's only when all indexes on the table have been dropped that the pages are released and list tablespaces will show more free pages. I don't think you need to take any action since other indexes defined on this table can reuse the space from the dropped index.

    But still you need to get ready to go to 8K (data and index tablespace are close to the limit) or migrate to v9 and convert to large tablespaces

  10. #10
    Join Date
    Dec 2008
    Location
    Chennai
    Posts
    111

    Thanks

    Thank you so much for all the valuable advises. I will start planning to move the table to bigger page size. I am sure that i may need more advise during the migration. Until then THANKS...gunas.

  11. #11
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by db2girl
    You mentioned inplace, are you doing table reorg or index reorg. Inplace table reorg is recoverable and so it needs to be logged and can cause a significant amount of logging to be performed. Hard to tell how many logs it will need... (take a look at infinite logging - logsecond = -1 in the manual). You don't have enough free space in the tablespace so I think you need to use a temporary tablespace during reorg (can't use for inplace).

    Also, why are you doing reorg if you plan to move them to a new set of tablespaces? You can use reorgchk to see if you really need to do a reorg.
    I may be wrong, but not sure you can use infinite logging with HADR and logarchmeth1.
    Last edited by Marcus_A; 08-09-09 at 20:43.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  12. #12
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by db2girl
    ... If you add new data and there is no way to purge old, then you need to go to the next page size by creating the bufferpools, tablespaces, unloading and reloading the data (load with copy yes should be ok in HADR env).
    If you use this method with HADR, I believe that a copy of the input to the load command must exist on both primary and standby servers or a shared mount point. I would practice this on a test table before it is tried on table that is needed.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  13. #13
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,369
    Please check the following link if you decide to use the load command:
    DB2 Universal Database

    Look at the "Load operations and HADR" section

  14. #14
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,369
    Also, "index object" = all indexes defined on the table

  15. #15
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,369
    Quote Originally Posted by Marcus_A
    I may be wrong, but not sure you can use infinite logging with HADR and logarchmeth1.

    Marcus was right. Infinite logging is not supported with HADR - all DB2 versions.

Posting Permissions

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