Results 1 to 3 of 3
  1. #1
    Join Date
    Aug 2003
    Location
    India
    Posts
    262

    Unanswered: Moving Indexes from One dbspace to the other

    Hi all,

    I need to know whether i can move an index from one dbspace to other.

    create table role
    (
    role_id integer not null ,
    role_nm varchar(10) not null ,
    role_desc varchar(20) not null ,
    is_active "informix".boolean,
    primary key (role_id) constraint role_role_id_pk
    ) in devdata extent size 32 next size 32 lock mode page;
    revoke all on "ocslinkdev".role from "public";

    I have created the above table in dbspace devdata, now i need to move
    the index (i.e primary key) to another dbspace called idx_dbspace.
    Please let me know whether we can do it & what is the syntax for it.

    I can create an index with create index syntax in other dbspace, but i need to move the constraints i.e. primary & foreign key to other dbspace. When i created these constraints, informix internally generated an index name to them, is there a way i can change them or create a new one. Awaiting ur reply. Thanks in advance

    Best Regards,

    lloyd

  2. #2
    Join Date
    Aug 2002
    Location
    Belgium
    Posts
    534
    When you want to controll the constrain of a table you have to assign an index to it.
    iIe.

    1. Create the flat table
    2. Create the index
    3. Alter the table create the constraint, pointing to the index.

    Now you can simply drop the primary key constraint fro the table. Internally IDS created an index (which begins with a space - so you can't controll it)
    Create a unique index in th ecorrect dbspace and alter the table.
    For th ecorrect syntax, check the sql syntax guide of th ecorrect version you are using online at
    http://www-3.ibm.com/software/data/i.../pubs/library/
    rws

  3. #3
    Join Date
    Aug 2003
    Location
    India
    Posts
    262
    Originally posted by Roelwe
    When you want to controll the constrain of a table you have to assign an index to it.
    iIe.

    1. Create the flat table
    2. Create the index
    3. Alter the table create the constraint, pointing to the index.

    Now you can simply drop the primary key constraint fro the table. Internally IDS created an index (which begins with a space - so you can't controll it)
    Create a unique index in th ecorrect dbspace and alter the table.
    For th ecorrect syntax, check the sql syntax guide of th ecorrect version you are using online at
    http://www-3.ibm.com/software/data/i.../pubs/library/
    Hi Roelwe,

    Thanks for your prompt reply.

    Regards,

    lloyd

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •