Create Index in Separate table space

situ

unread,

May 14, 2010, 5:22:32 AM5/14/10

to

I'm Running on DB2 9.5/AIX, currently involved in Migration from Ora
to DB2.
In oracle i've following statement.

CREATE INDEX ITEM_IDX ON ITEM
(LOWER("ITEM_NAME"), ITEM_ID)
TABLESPACE IDX_TBS;

I'm not able to find the corresponding syntax in DB2, goggled it but
still not able to find the solution.

I assume we can only point Index to be created into a separate
tablespace only during Table Creation Time ; Also we cannot have
InBuild Function with Index Key, like LOWER("ITEM_NAME").

Thanks.
Sridhar

Ian

unread,

May 14, 2010, 1:14:00 PM5/14/10

to

On May 14, 2:22 am, situ <SRIDHAR...@REDIFFMAIL.COM> wrote:
> I'm Running on DB2 9.5/AIX, currently involved in Migration from Ora
> to DB2.
> In oracle i've following statement.
>
> CREATE INDEX ITEM_IDX ON ITEM
> (LOWER("ITEM_NAME"), ITEM_ID)
> TABLESPACE IDX_TBS;

With DB2 you specify the tablespaces at table creation.

create table t1 (c1 int) in data_tbs index in index_tbs;

If you are using range partitioned tables, you DO have the
option to specify different tablespaces for each index.
In that case (after you've created your table) you can
specify:

create index i1 on t1 (c1) in index_tbs;

> Also we cannot have
> InBuild Function with Index Key, like LOWER("ITEM_NAME").

You have to define a new column that generates this expression
in your table and then index that new column. Note, the DB2
optimizer
is smart enough to automatically use the generated column (and index)
when it's appropriate.

Mark A

unread,

May 17, 2010, 12:26:41 AM5/17/10

to

"situ" <SRIDH...@REDIFFMAIL.COM> wrote in message
news:685ff6f7-03c7-424a...@n37g2000prc.googlegroups.com...

In DB2 9.7, you can specify a separate tablespace in the create index
statement after the table has been created:

CREATE UNIQUE INDEX A_IDX ON MYNUMBERDATA (A) IN IDX_TBSP

Using DB2 9.7 Fixpack 1 (pr Fixpack 2 which will be out very soon) would
make your Oracle conversion a lot easier.

Serge Rielau

unread,

May 17, 2010, 4:10:44 AM5/17/10

to

I agree an ORA to DB2 migration should be done on DB2 9.7.
It's 10x easier compared to Db2 9.1 and some of the 9.5 Ora
compatibility features were only in beta (such as VARCHAR2).

Cheers
Serge

--
Serge Rielau
SQL Architect DB2 for LUW
IBM Toronto Lab

situ

unread,

May 17, 2010, 8:25:31 AM5/17/10

to

Thanks all for the Suggestion, though the current running code is 9.5,
soon we are moving into 9.7 and as you said i can utilize the DB2 to
the maximum and make my job easy.
But i worry about backward compatibility , suppose i setup a db using
compatibility vector of 9.7 and convert all oracle packages as it is
into db2.
if client wants to apply the same code on application running on 9.5 ,
then I've nowhere to go but to prepare the DB2 relevant code and
maintainability of the code become difficult.

so right now i prefer to use DB2 specific code for some time.

Any thoughts or suggestion on this

Regards,
Sridhar

Hardy

unread,

May 18, 2010, 11:06:31 AM5/18/10

to

I'm afraid you have to do so. From the view of migration, 9.5 and 9.7
are very very different unless you don't utilize 9.7's potential.

Serge Rielau

unread,

May 20, 2010, 5:26:57 AM5/20/10

to

Sridhar,

Is this need to support DB2 9.5 real or just a monster under the bed?
We have hundreds of customers and ISVs exploiting DB2 9.7 to enable
Oracle applications.
But it's your resources.. just don't complain it's hard then given that
IBM already has shipped the solution to the problem;-)