Results 1 to 5 of 5
  1. #1
    Join Date
    Oct 2004
    Posts
    13

    Unanswered: tablespace used for index in DB2

    Hi,

    If we give create Index statement then which tablespace will be used for storing that index? will it be default tablespace or a separate Indexspace is used?Is there any separate system defined tablespace created for storing Indexes? If yes then through which system table we can see the number of Indexspaces in our system?

    I am working on the optimization area and looking into the area of sagregating tables and indexes .As i know in Oracle we can assign Tablespace to index by giving Index in (tablespace name) , but not sure about DB2.

    Can anyone please tell me wats the difference in Indexes in oracle and that of DB2?
    How to see the number of bufferpools used by our database? and how to see the relationship between bufferpools and Tablespaces?

    We are using DB2 Version 7 and OS/390.

    Thanks a lot in Advance.

    Vivek Srivastava

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    You can only specify the index tablespace when you create the table. All indexes that you create for that table will go in that tablespace. If the index tablespace haven't been specified at the table creation time the indexes will be created in the same tablespace as the table data.

    You can't change the index tablespace after the table has been created.

  3. #3
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    SYSIBM.SYSTABLESPACE will tell you which bufferpool the tablespace is assigned to.
    You can use the -DISPLAY BUFFERPOOL command to see the bufferpools (see the Command Reference for details).

    The regular b-tree indexes are very similar in Oracle and DB2. Oracle also has hash indexes which are different.
    Last edited by Marcus_A; 10-28-04 at 12:14.
    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
    Aug 2004
    Posts
    330
    I think you should look at the "USING" clause of the CREATE INDEX statement in OS/390 in order to designate the "TABLESPACE" that you want the index stored in.

  5. #5
    Join Date
    Oct 2004
    Location
    Norway
    Posts
    53
    Normally the indexspace is generated by the subsystem.
    But you are able to override this by using the following syntax:

    -- CREATE INDEX IDXNAME.IDXSPACE ON ...

    Where IDXSPACE is a user Defined name with a maximum length of 8 characters.

    Using the USING keyword in the DDL:

    ... USING STOGROUP SYSDEFLT ...

    Will only tell the subsystem where to store the dataset, and what the dataset will be named. For your information, the dataset name is the fully qualified name of the indexspace, ea:

    --- DBSBD.DSNDBD.SIEBP716.SRCO15ON.I0001.A001

    where SRC=15ON is the actual indexspace name.

    ---

    Regards

    /Filip

Posting Permissions

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