IDS Forum

Re: Can not delete index

Posted By: Art Kagel
Date: Monday, 10 November 2014, at 5:02 p.m.

In Response To: Re: Can not delete index (BEVIS KENNEDY)

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

Messages In This Thread

• Can not delete index
  BEVIS KENNEDY -- Monday, 10 November 2014, at 4:28 p.m.
  • Re: Can not delete index
    Jack Parker -- Monday, 10 November 2014, at 4:38 p.m.
    • Re: Can not delete index
      BEVIS KENNEDY -- Monday, 10 November 2014, at 4:42 p.m.
      • Re: Can not delete index
        Art Kagel -- Monday, 10 November 2014, at 5:02 p.m.
  • Re: Can not delete index
    JACQUES RENAUT -- Monday, 10 November 2014, at 4:43 p.m.

IDS Forum is maintained by Administrator with WebBBS 5.12.