Change constraint implicit indexes to explicit

Informix 12.10.FC14 Solaris 10 1/13 I have a legacy database with several thousand constraints with implicit indexes. I want to change those constraints to use explicit indexes. I'm not seeing any easy way to do that with any of the Informix administrative utilities, but, then again, this isn't something I do very often, and maybe I am missing something. Would a good way to achieve this be to use Art's 'myexport' to generate the CREATE INDEX and CONSTRAINT scripts on (a copy of) the target database (on another instance), and then DROP all the existing constraints in the target database, followed by using dbaccess to run the index and constraint scripts generated by 'myexport'? I'm thinking that would accomplish my objective, with minimum offline time for the target database. Is there another way I should consider? Thank you. DG ------------------------------ David Grove ------------------------------

David: Yes, in v12.10 dropping the constraints and recreating the indexes followed by the constraints would be the only way and myschema can make that relatively painless: myschema -d mydbs -t mytbl --index-file=mytrbl.idx.sql --constraint-file=mytbl.cnstr.sql /dev/null Optionally you can use the -K option to generate longer index names or --keep-generated-names to generate short names. Examples: Default output: CREATE UNIQUE INDEX "art".P2539_1078 ON "art".constr_test ( one ASC ) USING btree IN datadbs_1; ALTER TABLE "art".constr_test ADD CONSTRAINT PRIMARY KEY ( one ) ; Note unnamed primary key constraint. With --keep-generated-names: CREATE TABLE "art".constr_test ( one SERIAL(1) NOT NULL ) IN datadbs_1 EXTENT SIZE 16 NEXT SIZE 16 LOCK MODE ROW; -- Index < 2539_1078> is a constraint index. Creating normal index: P2539_1078. CREATE UNIQUE INDEX "art".P2539_1078 ON "art".constr_test ( one ASC ) USING btree IN datadbs_1; ALTER TABLE "art".constr_test ADD CONSTRAINT PRIMARY KEY ( one ) CONSTRAINT "art".u2539_1163; Note the short generated constraint name. With -K: CREATE UNIQUE INDEX "art".constr_test_pk ON "art".constr_test ( one ASC ) USING btree IN datadbs_1; ALTER TABLE "art".constr_test ADD CONSTRAINT PRIMARY KEY ( one ) CONSTRAINT "art".constr_test_pk; Art ------------------------------ Art S. Kagel, President and Principal Consultant ASK Database Management Corp. www.askdbmgt.com ------------------------------

Oops, forgot to mention in v14.10 you can rename such indexes and constraints: $ dbaccess art -Database selected. > rename index 2539_1078 to new_contr_index; Index renamed. CREATE UNIQUE INDEX "art". new_contr_index ON "art".constr_test ( one ASC ) USING btree IN datadbs_1; ------------------------------ Art S. Kagel, President and Principal Consultant ASK Database Management Corp. www.askdbmgt.com ------------------------------

Thank you, Art. We would have upgraded to 14. years ago, but Oracle (for the first time ever [as far as I can remember] in their Solaris product line, has declined to provide an upgrade path from Solaris 10 to Solaris 11-- a bare bones install of 11 is required, rather than an upgrade from 10-- and we preferred to avoid that). Informix 14. requires Solaris 11. DG ------------------------------ David Grove ------------------------------

Hi Art, David, I think it was 12.10.xC8 which introduced this? And by renaming such implicit, internally named index to a real name (without leading space), you're making the index explicit (and standalone) and protecting it against being dropped with dropping the constraint it was tied to. HTH, Andreas ------------------------------ Andreas Legner ------------------------------

Thank you, Andreas. Good to know. But, I'm thinking it still will likely be better to rebuild the indexes because then I can (re-)create them in a 16K dbspace. DG ------------------------------ David Grove ------------------------------