tablespace attribute not being used for DB2 with index and PK creations

[Christian Maslen]

Hi,

I'm using the tablespace attribute for index, PK and unique constraint creations, but the resulting DDL is not creating the objects in the requested table space.

Eg:

    <createIndex
      tableName="tbl" indexName="IX_tbl_001"
      tablespace="IDX_TBSP">
      <column name="tbl_Id" />
    </createIndex>

The expected generated DDL...
CREATE INDEX IX_tbl_001 ON tbl( tbl_id ) IN IDX_TBSP;

The actual generated DDL...
CREATE INDEX IX_tbl_001 ON tbl( tbl_id );


Unfortunately with primary keys and unique constraints things get more complicated...
    <addPrimaryKey
      constraintName="PK_Tbl" tableName="Tbl" columnNames="Tbl_Id"
      tablespace="IX_TBL_SPC"/>

The alter table add constraint syntax doesn't offer a tablespace option, so there are 2 choices here:
1. You create a unique index in the correct table space. Then add the constraint as usual. DB2 will re-use the existing index instead of creating a new one.

2. You offer extra table space options in the create table tag (My preference). I'll illustrate this with the DDL:
CREATE TABLE tbl
(
        tbl_Id  INTEGER  NOT NULL,
...
)
    IN TB_TBL_SPC
    INDEX IN IX_TBL_SPC
    LONG IN LG_TBL_SPC
;

So what we want here is the option of specifying all the tablespace options in the create statement. Note DB2 doesn't offer this to be done in an alter statement so I can't use a subsequent custom SQL.

Thanks in advance,

Christian Maslen

[Voxland, Nathan]

I'll add this to the issue tracker, but it won't get fixed until the
next release.  For now, you can work around the problem by converting
your createIndex and addPrimaryKey tags to the sql tag and putting the
desired sql in there.

Thanks for the bug report.

Nathan

-----Original Message-----
From: liquibase-user-bounces@...
[mailto:liquibase-user-bounces@...] On Behalf Of
Christian Maslen
Sent: Wednesday, July 02, 2008 8:03 PM
To: liquibase-user@...
Subject: [Liquibase-user] tablespace attribute not being used for DB2
with index and PK creations


Hi,

I'm using the tablespace attribute for index, PK and unique constraint
creations, but the resulting DDL is not creating the objects in the
requested table space.

Eg:

    <createIndex
      tableName="tbl" indexName="IX_tbl_001"
      tablespace="IDX_TBSP">
      <column name="tbl_Id" />
    </createIndex>

The expected generated DDL...
CREATE INDEX IX_tbl_001 ON tbl( tbl_id ) IN IDX_TBSP;

The actual generated DDL...
CREATE INDEX IX_tbl_001 ON tbl( tbl_id );


Unfortunately with primary keys and unique constraints things get more
complicated...
    <addPrimaryKey
      constraintName="PK_Tbl" tableName="Tbl" columnNames="Tbl_Id"
      tablespace="IX_TBL_SPC"/>

The alter table add constraint syntax doesn't offer a tablespace option,
so
there are 2 choices here:
1. You create a unique index in the correct table space. Then add the
constraint as usual. DB2 will re-use the existing index instead of
creating
a new one.

2. You offer extra table space options in the create table tag (My
preference). I'll illustrate this with the DDL:
CREATE TABLE tbl
(
        tbl_Id  INTEGER  NOT NULL,
...
)
    IN TB_TBL_SPC
    INDEX IN IX_TBL_SPC
    LONG IN LG_TBL_SPC
;

So what we want here is the option of specifying all the tablespace
options
in the create statement. Note DB2 doesn't offer this to be done in an
alter
statement so I can't use a subsequent custom SQL.

Thanks in advance,

Christian Maslen

--
View this message in context:
http://www.nabble.com/tablespace-attribute-not-being-used-for-DB2-with-i
ndex-and-PK-creations-tp18249908p18249908.html
Sent from the LiquiBase - User mailing list archive at Nabble.com.


------------------------------------------------------------------------
-
Sponsored by: SourceForge.net Community Choice Awards: VOTE NOW!
Studies have shown that voting for your favorite open source project,
along with a healthy diet, reduces your potential for chronic lameness
and boredom. Vote Now at http://www.sourceforge.net/community/cca08
_______________________________________________
Liquibase-user mailing list
Liquibase-user@...
https://lists.sourceforge.net/lists/listinfo/liquibase-user

-------------------------------------------------------------------------
Sponsored by: SourceForge.net Community Choice Awards: VOTE NOW!
Studies have shown that voting for your favorite open source project,
along with a healthy diet, reduces your potential for chronic lameness
and boredom. Vote Now at http://www.sourceforge.net/community/cca08
_______________________________________________
Liquibase-user mailing list
Liquibase-user@...
https://lists.sourceforge.net/lists/listinfo/liquibase-user