Results 1 to 10 of 10
  1. #1
    Join Date
    Dec 2008
    Location
    Chennai
    Posts
    111

    Unanswered: Create TABLESPACE for TABLE [and|or] INDEX?

    Hello,
    Our DB2 version is 8.2 on Windows 2003. Some table contains over 70million rows.

    In our system all the TABLES and INDEXES are in two seperate TABLESPACES and all 4K pages.

    One of the IndexSpaces which contain only INDEXES is nearly full (over 92% full) and not allow to add more containers. So I have to create new 8K or 32k page tablespace for this INDEXES and recreate the indexes.

    Now the question is, If I create the bigger TABLESPACE for INDEXES and recreate the INDEXES will be enough? OR Do I must create the bigger size tablespace for both TABLES and INDEXES?

    Please advise me.


    Thanks, Gunas.

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Since you can only specify an index tablespace at the table creation time, you will have to re-create the table entirely. It might make sense then to place its data into a tablespace with a larger page size as well.
    ---
    "It does not work" is not a valid problem statement.

  3. #3
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    I am assuming he will drop the old tablespace for indexes and recreate it with a larger page size. In that case, I think he can just recreate the index tablespace without recreating the table and without changing the tablespace for the table data.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  4. #4
    Join Date
    Dec 2008
    Location
    Chennai
    Posts
    111
    Quote Originally Posted by Marcus_A
    I am assuming he will drop the old tablespace for indexes and recreate it with a larger page size. In that case, I think he can just recreate the index tablespace without recreating the table and without changing the tablespace for the table data.
    Thanks Marcus.
    The tablespace which contain only tables has more than 50 % free space. So really I like to avoid to drop and recreate this tablespace as there are more than 100 tables in that tablespace.
    So I thought to drop and recreate only the index tablespace with 16K page then recreate the indexes.
    Can someone advise me that this is correct approach? May I see any performance issues due to different page size?
    Thanks..

  5. #5
    Join Date
    Dec 2008
    Location
    Toronto, Canada
    Posts
    399
    why can't you re-size existing or add new containers by using ALTER TABLESPACE command?

    Depending on how the table and its indexes are used it might be a good idea to create separate tablespace and indexspace for this big table. When I worked for IBM we had a rule that every table had its own tablespace (it was db2 v7 on z/OS).
    DB2 9.5/9.7 on Unix/AIX 6.1/Linux

  6. #6
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by MarkhamDBA
    why can't you re-size existing or add new containers by using ALTER TABLESPACE command?

    Depending on how the table and its indexes are used it might be a good idea to create separate tablespace and indexspace for this big table. When I worked for IBM we had a rule that every table had its own tablespace (it was db2 v7 on z/OS).
    Having one tablespace for each table is only advisable for DB2 z/OS, not LUW.

    The OP already has a separate tablespace for the tables and the indexes.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  7. #7
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Actually it appears that Nick is correct and you cannot drop a tablespace that is defined as the index tablespace, even if there are no indexes in it, so long as the table data is in a different tablespace (and you don't drop the table). I just tried it and got SQL0282N.
    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
    Toronto, Canada
    Posts
    399
    Quote Originally Posted by Marcus_A
    Having one tablespace for each table is only advisable for DB2 z/OS, not LUW.

    The OP already has a separate tablespace for the tables and the indexes.
    Would not it make sense to have a separate TS and IndexSpace for a large heavily used table/index to improve perfomance in this case? I mean no other tables in the same TS and not other indexes in the same IS. Even if it's on Windows. Thanks
    Last edited by MarkhamDBA; 12-30-08 at 11:43.
    DB2 9.5/9.7 on Unix/AIX 6.1/Linux

  9. #9
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Putting a table, even if it is "heavily used", into a separate tablespace does not magically improve performance, so the general answer is "no".
    ---
    "It does not work" is not a valid problem statement.

  10. #10
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by MarkhamDBA
    Would not it make sense to have a separate TS and IndexSpace for a large heavily used table/index to improve perfomance in this case? I mean no other tables in the same TS and not other indexes in the same IS. Even if it's on Windows. Thanks
    A lot depends on whether you have an OLTP or data warehouse application. Most OLTP applications have a bufferpool hit ratio of at least 90% (many have 98%). Even with an data warehouse applicaiton, it would only help if DB2 was accessing two tables on disk (not in a bufferpool) at exactly the same time. But in terms of performance it is usually better to have a tablespace spread accross multiple devices (for example, by having multiple containers on different disks or arrays), rather than worrying about having a lot of different tablspaces.

    Certainly there are some administrative reasons for have multiple tablespaces (which are mostly different reasons than for z/OS). Chief among the reasons in LUW is that bufferpool assignments are at the tablespace level.
    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
  •