469,950 Members | 2,282 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,950 developers. It's quick & easy.

Index tablespace

I created tables without using the 'INDEX in tablespace' statement.
I want to add indexes on these tables.
How can I mention as to which tablespace the indexes should use?

Thanks.

Cheers,
San.

Jul 24 '06 #1
4 7514
"shsandeep" <sa**********@gmail.comwrote in message
news:19******************************@localhost.ta lkaboutdatabases.com...
>I created tables without using the 'INDEX in tablespace' statement.
I want to add indexes on these tables.
How can I mention as to which tablespace the indexes should use?

Thanks.

Cheers,
San.
Given how you have created the table, any indexes you create will reside in
the same tablespaces as the data. There is nothing wrong with this, unless
you want the indexes to reside in a different bufferpool than the table data
(because tablespaces are assigned to bufferpools).

The only way to change it (to have table and indexes in different
tablespaces) is to drop and recreate the table. Also, you must use a DMS
tablespaces to have table and indexes in separate tablespaces.
Jul 24 '06 #2
Thanks Mark. This is exactly what I was looking for.
One question out of curiosity: What is the necessity of having indexes
and tables using different bufferpools? Performance? Does it make a big
impact on a database which deals with majority of OLAP transactions?

Cheers,
San.

Jul 24 '06 #3
"shsandeep" <sa**********@gmail.comwrote in message
news:91******************************@localhost.ta lkaboutdatabases.com...
Thanks Mark. This is exactly what I was looking for.
One question out of curiosity: What is the necessity of having indexes
and tables using different bufferpools? Performance? Does it make a big
impact on a database which deals with majority of OLAP transactions?

Cheers,
San.
Because bufferpool memory should be limited to the amount of free real
system memory, and also constrained to a max of about 1.5 - 2.0 GB for
32-bit DB2 instances (if you have a 64-bit DB2 instance it is only
constrained by real memory), it has to be utilized in an efficient manner.

If you have a database which will not completely fit in bufferpool memory,
then you may want to prioritize certain objects over others and create more
than one bufferpool. For example, you may want to have the DB2 system
catalog, small lookup tables (including dimension tables), and all indexes
(for all tables) reside in memory all the time (by placing them in a single
appropriately sized bufferpool), and have the large tables (including large
fact tables) in a second bufferpool (which because of their size will never
fit completely in any bufferpool). If everything were to be placed in a
single bufferpool, then a table scan on a large table would likely flush out
all the catalog objects, indexes, and small tables from the bufferpool
memory.

Bufferpool design is a bit of an art (as well as some science) and there is
no perfect configuration, nor will 2 different people usually come up with
the exact same solution (unless there is only one bufferpool).

But generally, in a data warehouse application it is best to have 2 or 3
bufferpools in the manner I have described above.
For an OLTP application where you can fit all (or almost all) of the data,
indexes, and catalog in a single large bufferpool, then that is usually the
best solution. For a large OLTP system, then multiple bufferpools can be
used in a similar manner as a data warehouse.

But the biggest mistake most people make is creating too many bufferpools.
Jul 24 '06 #4
shsandeep wrote:
Thanks Mark. This is exactly what I was looking for.
One question out of curiosity: What is the necessity of having indexes
and tables using different bufferpools? Performance? Does it make a big
impact on a database which deals with majority of OLAP transactions?
In a first order it is not relevant. Only when you want to eek out the
last iota of speed would you want to look at this issue.
Note that prior to DB2 9 having multiple buffer pools means also that
you have to tune each of them. Too much hassle for the typical app,
especially OLTP.

Cheers
Serge

PS-FYI: A side effect of range partitioning in DB2 9 is that range
partitioned tables can place there indexes where ever they please.
PPS: Any table can be seen as a range partitioned table with one open
ended partition ;-)
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

IOD Conference
http://www.ibm.com/software/data/ond...ness/conf2006/
Jul 24 '06 #5

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

8 posts views Thread by Andr? Queiroz | last post: by
14 posts views Thread by Sean C. | last post: by
8 posts views Thread by dbamota | last post: by
1 post views Thread by chettiar | last post: by
4 posts views Thread by shsandeep | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.