Results 1 to 3 of 3
  1. #1
    Join Date
    Jul 2011
    Posts
    54

    Unanswered: Table Space for Indexes

    Hi,
    What is the best approach for storing Indexes:-
    (1) Index should be store same database data table space?

    (2) We should create separate table space for indexes, and it should be separate for every database?

    (3) One separate table space for all database single table space for storing the indexes.

    (4) Is any schema\user security associate with indexes while storing in table space.

    Thanks

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Generally, indexes should be stored in separate tablespaces so that they can be placed in a separate bufferpool from the data. This is based on the assumption that you may want to have a higher bufferpool hit ratio for indexes than for the data, and that the entire database (tables and indexes) is larger than the amount of bufferpool memory that can be allocated on that server.

    The number of tablespaces for data and indexes depends on the size of the database and each table (and whether you will need to do tablespace level backup/restores). Also, having more than one tablespace can improve backup and restore performance in most cases, if the database is large enough.

    The above information is for DB2 LUW. DB2 for z/OS has different considerations.
    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
    Jul 2011
    Posts
    54
    Quote Originally Posted by Marcus_A View Post
    Generally, indexes should be stored in separate tablespaces so that they can be placed in a separate bufferpool from the data. This is based on the assumption that you may want to have a higher bufferpool hit ratio for indexes than for the data, and that the entire database (tables and indexes) is larger than the amount of bufferpool memory that can be allocated on that server.

    The number of tablespaces for data and indexes depends on the size of the database and each table (and whether you will need to do tablespace level backup/restores). Also, having more than one tablespace can improve backup and restore performance in most cases, if the database is large enough.

    The above information is for DB2 LUW. DB2 for z/OS has different considerations.
    Is any schema\user security associate with indexes while storing in table space.

Posting Permissions

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