473,587 Members | 2,489 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Which tablespce is used when running ALTER TABLE & CREATE INDEX

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
Nov 12 '05 #1
4 5867
maricel <ma*****@xtra.c o.nz> wrote:
Could someone confirm which tablespace is being used when running ALTER &
CREATE INDEX.
Is it the tempspace or the tablespace where the table resides?


What do you mean with "which tablespace is being used"? An ALTER TABLE does
not cause the table's data to be moved around. And an index is created in
the tablespace that was explicitly or implicitly specified in the CREATE
TABLE statement for the table on which the index is to be created. Have a
look at the catalog view SYSCAT.TABLES, column INDEX_TBSPACE. It tells you
the tablespace that will be used for the index.

--
Knut Stolze
Information Integration
IBM Germany / University of Jena
Nov 12 '05 #2
....and if creation of the index forces a sort that spills to disk, a
SYSTEM temp tablespace will be used.

Knut Stolze wrote:
maricel <ma*****@xtra.c o.nz> wrote:

Could someone confirm which tablespace is being used when running ALTER &
CREATE INDEX.
Is it the tempspace or the tablespace where the table resides?

What do you mean with "which tablespace is being used"? An ALTER TABLE does
not cause the table's data to be moved around. And an index is created in
the tablespace that was explicitly or implicitly specified in the CREATE
TABLE statement for the table on which the index is to be created. Have a
look at the catalog view SYSCAT.TABLES, column INDEX_TBSPACE. It tells you
the tablespace that will be used for the index.


Nov 12 '05 #3
Is this means that DB2 will not use some sort of temporary table when
processing the alter statement. In Informix, when altering the table, it
requires at least similar amount of extra space (table size) in the same
table space where the table resides - if my table size is 500MB then I
should have at least 500MB extra space to process the alter statement,
otherwise you can't alter it. In the case of CREATE index, Informix uses the
temporary tablespace to build the index before putting them into their
assigned tablespace.

In DB2, what is the requirement for the ALTER TABLE & CREATE INDEX
processing, does it need extra space & if yes, how large & where?

maricel

"Knut Stolze" <st****@de.ibm. com> wrote in message
news:bv******** **@fsuj29.rz.un i-jena.de...
maricel <ma*****@xtra.c o.nz> wrote:
Could someone confirm which tablespace is being used when running ALTER & CREATE INDEX.
Is it the tempspace or the tablespace where the table resides?
What do you mean with "which tablespace is being used"? An ALTER TABLE

does not cause the table's data to be moved around. And an index is created in
the tablespace that was explicitly or implicitly specified in the CREATE
TABLE statement for the table on which the index is to be created. Have a
look at the catalog view SYSCAT.TABLES, column INDEX_TBSPACE. It tells you the tablespace that will be used for the index.

--
Knut Stolze
Information Integration
IBM Germany / University of Jena

Nov 12 '05 #4
The alter table statments supported today are all online, and immediate
update. No change to the table's row data is required (and no temporary
space is consumed).

maricel wrote:
Is this means that DB2 will not use some sort of temporary table when
processing the alter statement. In Informix, when altering the table, it
requires at least similar amount of extra space (table size) in the same
table space where the table resides - if my table size is 500MB then I
should have at least 500MB extra space to process the alter statement,
otherwise you can't alter it. In the case of CREATE index, Informix uses the
temporary tablespace to build the index before putting them into their
assigned tablespace.

In DB2, what is the requirement for the ALTER TABLE & CREATE INDEX
processing, does it need extra space & if yes, how large & where?

maricel

"Knut Stolze" <st****@de.ibm. com> wrote in message
news:bv******** **@fsuj29.rz.un i-jena.de...
maricel <ma*****@xtra.c o.nz> wrote:

Could someone confirm which tablespace is being used when running ALTER
&
CREATE INDEX.
Is it the tempspace or the tablespace where the table resides?


What do you mean with "which tablespace is being used"? An ALTER TABLE


does
not cause the table's data to be moved around. And an index is created in
the tablespace that was explicitly or implicitly specified in the CREATE
TABLE statement for the table on which the index is to be created. Have a
look at the catalog view SYSCAT.TABLES, column INDEX_TBSPACE. It tells


you
the tablespace that will be used for the index.

--
Knut Stolze
Information Integration
IBM Germany / University of Jena


Nov 12 '05 #5

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
9301
by: Rajesh Kumar Mallah | last post by:
Hi , Looks like ADD UNIQUE( some_fuc( some_feild) ) is not supported with add constraint. the only way is to add the constriant is using UNIQUE INDEX . Is it a bug or intended behaviour? Regds Mallah.
4
2092
by: Fan Ruo Xin | last post by:
Hi, I have been watching MDC for a long time ... I have seen it from INDEX-SCAN to INDEX ONLY for the DISTINCT (block index columns) operator .... But the Elapsed time shows MDC still run slower. This did confuse me. Of course I expected it should run faster, because of the small block index tree, compare with the RID index tree. select...
10
8307
by: hilz | last post by:
Hi all. I have a table that i create in MsAccess, using ado connection as follows: create table PAYITEM ( PAYITEM_ID COUNTER PRIMARY KEY, PAYITEM_NAME CHAR(255) UNIQUE, PAYITEM_DESCRIPTION CHAR(255) );
17
3956
by: clintonG | last post by:
I'm using an .aspx tool I found at but as nice as the interface is I think I need to consider using others. Some can generate C# I understand. Your preferences please... <%= Clinton Gallagher http://forta.com/books/0672325667/
2
31469
by: RamaKrishna Narla | last post by:
In MS SQL Server, I have the following tables with some data in it. create table table1 ( column1 varchar(32), column2 int not null, column10 varchar(255), ..... primary key (column1, column2), );
10
14663
by: Laurence | last post by:
Hi there, How to differentiate between unique constraint and unique index? These are very similar but I cannot differentiate them? Could someone give me a hand? Thanks in advance
8
5200
by: rcamarda | last post by:
I thought I would delve into index fragmentation and I found some great sql from many posters (thanks Erland!). My question is how bad is bad? I know this is very subjective. Some scripts I found would reindex if the LogicalFragmenation is over 30%. I have some tables that are 98% (I'm guessing really bad). I know it all depends.. more as...
0
2427
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 the index that gets created goes. I did find that I could create the table with the clause "INDEX IN ...", name the tablespace I wanted and then...
1
612
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);
0
7923
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main...
0
7852
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language...
0
8349
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that...
1
5719
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 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 a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes...
0
5395
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert...
0
3845
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in...
0
3882
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
1455
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
0
1192
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...

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.