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

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.

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.

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.

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