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

On May 14, 2:22*am, situ <SRIDHAR... (AT) REDIFFMAIL (DOT) COM> wrote:
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;

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.

"situ" <SRIDHARA.S (AT) REDIFFMAIL (DOT) COM> wrote

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.

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

On May 17, 1:10*pm, Serge Rielau <srie... (AT) ca (DOT) ibm.com> wrote:

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

On May 17, 7:25*am, situ <SRIDHAR... (AT) REDIFFMAIL (DOT) COM> wrote:
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.

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;-)

Cheers
Serge

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