Create Index in Separate table space
in [DB2]
|
Prev: Disabling a MQT in db2 9.7
Next: DB2 DBA - Distributed Database Administrator required at Morgan Stanley, UK, London
From: situ on 14 May 2010 05:22 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
From: Ian on 14 May 2010 13:14 On May 14, 2:22 am, situ <SRIDHAR...(a)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.
From: Mark A on 17 May 2010 00:26 "situ" <SRIDHARA.S(a)REDIFFMAIL.COM> wrote in message news:685ff6f7-03c7-424a-9aaf-eea51659ecaa(a)n37g2000prc.googlegroups.com... > 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 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.
From: Serge Rielau on 17 May 2010 04:10 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
From: situ on 17 May 2010 08:25
On May 17, 1:10 pm, Serge Rielau <srie...(a)ca.ibm.com> wrote: > 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 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 |