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

maricel <maricel@xtra.co.nz> wrote:
[color=blue]
> Could someone confirm which tablespace is being used when running ALTER &
> CREATE INDEX.
> Is it the tempspace or the tablespace where the table resides?[/color]

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

....and if creation of the index forces a sort that spills to disk, a
SYSTEM temp tablespace will be used.

Knut Stolze wrote:
[color=blue]
> maricel <maricel@xtra.co.nz> wrote:
>
>[color=green]
>>Could someone confirm which tablespace is being used when running ALTER &
>>CREATE INDEX.
>>Is it the tempspace or the tablespace where the table resides?[/color]
>
>
> 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.
>[/color]

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" <stolze@de.ibm.com> wrote in message
news:bvntvj$3ek$1@fsuj29.rz.uni-jena.de...[color=blue]
> maricel <maricel@xtra.co.nz> wrote:
>[color=green]
> > Could someone confirm which tablespace is being used when running ALTER[/color][/color]
&[color=blue][color=green]
> > CREATE INDEX.
> > Is it the tempspace or the tablespace where the table resides?[/color]
>
> What do you mean with "which tablespace is being used"? An ALTER TABLE[/color]
does[color=blue]
> 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[/color]
you[color=blue]
> the tablespace that will be used for the index.
>
> --
> Knut Stolze
> Information Integration
> IBM Germany / University of Jena[/color]

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:[color=blue]
> 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" <stolze@de.ibm.com> wrote in message
> news:bvntvj$3ek$1@fsuj29.rz.uni-jena.de...
>[color=green]
>>maricel <maricel@xtra.co.nz> wrote:
>>
>>[color=darkred]
>>>Could someone confirm which tablespace is being used when running ALTER[/color][/color]
>
> &
>[color=green][color=darkred]
>>>CREATE INDEX.
>>>Is it the tempspace or the tablespace where the table resides?[/color]
>>
>>What do you mean with "which tablespace is being used"? An ALTER TABLE[/color]
>
> does
>[color=green]
>>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[/color]
>
> you
>[color=green]
>>the tablespace that will be used for the index.
>>
>>--
>>Knut Stolze
>>Information Integration
>>IBM Germany / University of Jena[/color]
>
>
>[/color]