473,320 Members | 1,900 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

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 7683
"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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
by: Andy | last post by:
a new user of oracle 10g. tried the following statements on scott/tiger with tablespace x create table mytest ( a varchar2(30) ); alter table mytest add constraint pk_a primary key(a);
8
by: Andr? Queiroz | last post by:
Hi, I have a table with 10M records and col A has a index created on it. The data on that table has the same value for col A on all 10M records. After that I insert diferent values for that column...
0
by: xixi | last post by:
we are using db2 udb v8.1 on win 64 bit HP titanium machine with fp3 with type 4 db2jcc.jar driver. i have two database created under different patition on same drive. when i do db2dart on the...
4
by: maricel | last post by:
Could someone confirm which tablespace is being used when running ALTER & CREATE INDEX. Is it the tempspace or the tablespace where the table resides? Many thanks, maricel
14
by: Sean C. | last post by:
Helpful folks, Most of my previous experience with DB2 was on s390 mainframe systems and the optimizer on this platform always seemed very predictable and consistent. Since moving to a WinNT/UDB...
8
by: dbamota | last post by:
What is the syntax in UDB db2, to create an index IX in table TBL "in tablespace TBSP" which is different from the tablespace of TBL ? TIA
1
by: chettiar | last post by:
Hi, I am dropping an index and recreating it to lower the high water mark. The index creation is taking a lot of time. I am stuck as to why it does so. Is there any way that I can find out why...
4
by: shsandeep | last post by:
I have added primary key to my table using the 'ALTER TABLE' statement. Now, is it mandatory for me to use the 'CREATE UNIQUE INDEX' on primary key columns in order to enforce the primary key...
1
by: Lennart | last post by:
I'm trying to create a testdatabase from a productiondatabase. The steps I've gone through so far are: export data with certain criteria from roughly 150 tables drop f.k pointing to these tables...
0
by: pjhitchman | last post by:
Hi, Using DB2 version 9, fixpak 2 on AIX 5L. I created my DDL for defining a Primary Key to be done by a ALTER TABLE command, but I could not find a way of naming the tablespace to control were...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.