By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,358 Members | 2,811 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,358 IT Pros & Developers. It's quick & easy.

Which tablespce is used when running ALTER TABLE & CREATE INDEX

P: n/a
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
Share this Question
Share on Google+
4 Replies


P: n/a
maricel <ma*****@xtra.co.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

P: n/a
....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.co.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

P: n/a
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.uni-jena.de...
maricel <ma*****@xtra.co.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

P: n/a
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.uni-jena.de...
maricel <ma*****@xtra.co.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 discussion thread is closed

Replies have been disabled for this discussion.