Can not delete index

IBM Informix Dynamic Server Version 11.70.FC7 -- On-Line -- Up 11 days 01:23:42 -- 24478656 Kbytes DROP INDEX 773_1045;returns: 201: A syntax error has occurred. We are moving from 11.7 to 12.1; the new servers are up and functioning, replication is online and working; all that's left is to make certain all servers are in sync. But I ran into a problem with cdr check; the command: cdr check repl -a -v -L -m g_psprod5sl -r psshare_agency returns: Nov 10 2014 14:07:33 ------ Table scan for psshare_agency start -------- Segmentation fault (core dumped) I have already tried deleting and rebuilding the replicate. No change. We reported this to IBM in the past and found out we have ownerless indexes and that is what causes cdr to blowup. Normally when this happens we drop and recreate the index and all is well. So I ran this sql: SELECT c.*,a.tabname, b.colname, c.idxname FROM systables a, syscolumns b, sysindexes c WHERE a.tabid = b.tabid AND a.tabid = c.tabid AND b.colno = c.part1 and (c.owner is null or length(c.owner) <= 0); and sure enough idxname=773_1045 has a blank (empty string) for an owner. drop index 773_10456;201: A syntax error has occurred. I've tried quoting and renaming the index to no avail. My question: Can I (is it safe to) change the owner name in sysindexes and/or is there another way to delete the index?

Sounds like an index created to enforce a constraint. Look for the = constraint and drop it - the index will go away. j. On Nov 10, 2014, at 4:28 PM, BEVIS KENNEDY <bkennedy@utah.gov> wrote: > IBM Informix Dynamic Server Version 11.70.FC7 -- On-Line -- Up 11 days=20= > 01:23:42 -- 24478656 Kbytes=20 >=20> DROP INDEX 773_1045;=20> returns: 201: A syntax error has occurred.=20 >=20 > We are moving from 11.7 to 12.1; the new servers are up and = functioning,=20 > replication is online and working; all that's left is to make certain = all=20 > servers are in sync. But I ran into a problem with cdr check; the = command:=20 > cdr check repl -a -v -L -m g_psprod5sl -r psshare_agency=20 >=20 > returns:=20 > Nov 10 2014 14:07:33 ------ Table scan for psshare_agency start = --------=20 > Segmentation fault (core dumped)=20 >=20 > I have already tried deleting and rebuilding the replicate. No change. = We=20 > reported this to IBM in the past and found out we have ownerless = indexes and=20 > that is what causes cdr to blowup. Normally when this happens we drop = and=20 > recreate the index and all is well.=20 >=20 > So I ran this sql:=20 > SELECT c.*,a.tabname, b.colname, c.idxname=20 > FROM systables a, syscolumns b, sysindexes c=20 > WHERE a.tabid =3D b.tabid=20 >=20 > AND a.tabid =3D c.tabid=20 >=20 > AND b.colno =3D c.part1=20 > and (c.owner is null=20 > or length(c.owner) <=3D 0);=20 >=20 > and sure enough idxname=3D773_1045 has a blank (empty string) for an = owner.=20 >=20 > drop index 773_10456;=20> 201: A syntax error has occurred.=20 >=20> I've tried quoting and renaming the index to no avail.=20 > My question: Can I (is it safe to) change the owner name in sysindexes = and/or=20 > is there another way to delete the index?=20 >=20 >=20 > = **************************************************************************= *****=20 > Forum Note: Use "Reply" to post a response in the discussion forum.=20= >=20

Thank you Jack, I've already tried that. I should have mentioned that in my original post. alter table tblmnt_column drop constraint fk_tblmntcol_tbl;drops the constraint but the index hangs on.

Original post: IBM Informix Dynamic Server Version 11.70.FC7 -- On-Line -- Up 11 days 01:23:42 -- 24478656 Kbytes DROP INDEX 773_1045;returns: 201: A syntax error has occurred. We are moving from 11.7 to 12.1; the new servers are up and functioning, replication is online and working; all that's left is to make certain all servers are in sync. But I ran into a problem with cdr check; the command: cdr check repl -a -v -L -m g_psprod5sl -r psshare_agency returns: Nov 10 2014 14:07:33 ------ Table scan for psshare_agency start -------- Segmentation fault (core dumped) I have already tried deleting and rebuilding the replicate. No change. We reported this to IBM in the past and found out we have ownerless indexes and that is what causes cdr to blowup. Normally when this happens we drop and recreate the index and all is well. So I ran this sql: SELECT c.*,a.tabname, b.colname, c.idxname FROM systables a, syscolumns b, sysindexes c WHERE a.tabid = b.tabid AND a.tabid = c.tabid AND b.colno = c.part1 and (c.owner is null or length(c.owner) <= 0); and sure enough idxname=773_1045 has a blank (empty string) for an owner. drop index 773_10456;201: A syntax error has occurred. I've tried quoting and renaming the index to no avail. My question: Can I (is it safe to) change the owner name in sysindexes and/or is there another way to delete the index? Response: If you have an index name that's ' 773_1045', that's a system generated index used to satisfy a constraint. If I remember how the names are generated, I think 773 would be the tabid of the table that constraint is on and then 1045 would be the constraintid. So I think you should be able to get the constraint name and type with either dbschema of that table and queries involving systables and sysconstraints. But to drop the index, you would have to disable/drop the constraint 1st (but doing that I believe would drop the underlying index). Jacques Renaut IBM Informix Advanced Support APD Team

Bevis: There is a bug in certain versions of the engine that I have run into recently (never been able to nail down which because I'm seeing the problem in later versions that were victims of the problem) that can leave a constraint index around even after the constraint it suports has been dropped. The only work-around I can think of is to: 1. Rename the table, 2. Create a new table in TYPE(RAW) mode, 3. Copy the data from the renamed table to the new one, 4. ALTER the new table to TYPE(STANDARD), 5. Recreate all of the indexes and constraints that you do need, 6. Drop the renamed and damaged table, 7. Take a level 0 archive (because the table was loaded in RAW mode). The way to copy the data quickly would be to use one of the following which are probably in speed order from fastest: 1. Use EXTERNAL tables to <-> from a named pipe. 2. Use my dbcopy utility (unless that table has CLOB or BLOB columns, more than one BYTE or TEXT column, or LVARCHAR columns). 3. Use my dbmove utility (not quite as fast as dbcopy but it has no datatype restrictions). 4. INSERT INTO ... SELECT ... FROM. 5. Unload.load. Unfortunately, although dbcopy is easier to use than the external table option and nearly as fast, I've never coded dbcopy to handle smart blobs, and it gets confused if there are more than a single BYTE or TEXT dumb blob column. The LVARCHAR problem is caused by a bug in the CSDK that I can't work around well that hasn't been fixed in the two+ years since I reported it <hint hint IBM>. If this table does not have these type columns, use it. Art Art S. Kagel, President and Principal Consultant ASK Database Management www.askdbmgt.com Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Mon, Nov 10, 2014 at 4:42 PM, BEVIS KENNEDY <bkennedy@utah.gov> wrote: > Thank you Jack, I've already tried that. I should have mentioned that in my > original post. > > alter table tblmnt_column drop constraint fk_tblmntcol_tbl;> drops the constraint but the index hangs on. > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --001a11c240662f30d805078851d2