IDS Forum

Re: Please help: problem with index

Posted By:
Date:

Here are the steps:

- create a new table with the same structure but no indexes or constraints
- add triggers to the original table to maintain the copy up-to-date if needed
ie INSERT, DELETE, and UPDATE triggers which should use stored procedures so
they can ignore rows that have not yet been copied
- copy the data from the original table (HP loader or my dbcopy utility are
good tools for copying large quantities of data quickly with minimal impact)
- index the table (if there are unique indexes or unique and/or primary key
constraints start violations on it first and set constraints to filtering to
remove offending rows that would prevent the indexes and constraints from
being created)
- add back the constraints
- stop processing for a bit
- rename the original table to a temp name
- rename the new table to the original name
- drop the original table.

Dbcopy is included in the package utils2_ak available for download from the IIUG
Software Repository.

Art S. Kagel

----- Original Message -----
From: Nadezda Bol.... <Nadezda.Boltaca@parex.lv>
At: 5/ 9 5:15

> Dear Informix Gurus,
>
> I have a problem with deleting old data (last 1 day's data from 300
> stored days)
> from the most important table of our system.
> In online.log there are the following messages:
>
> 04:00:47 Assert Failed: Fragid 0x500002, Rowid 0x2ba4b002 not found for
>
> delete in partnum 600009
> 04:00:47 Informix Dynamic Server 2000 Version 9.21.HC4
> 04:00:47 Who: Session(1042796, cortex@strong, 14976, -1215118544)
> Thread(1048556, sqlexec, b79140ec, 1)
> File: exfmsupp.c Line: 853
> 04:00:47 Results: Delete failed
> 04:00:47 Action: Run 'oncheck -cI cortex:"cortex".tlog#tlog_acbalref'
> 04:00:47 stack trace for pid 17705 written to
> /ctxtools/informix/af.3d4da3f
> 04:00:47 See Also: /ctxtools/informix/af.3d4da3f, shmem.3d4da3f.0
> 04:01:02 Error writing '/ctxtools/informix/shmem.3d4da3f.0' errno = 28
> 04:01:02 Process exited with return code 127: /bin/sh /bin/sh -c
> /ctxtools/informix/etc/alarm_prog.sh 4 2 "Index failure:
> 'cortex:"cortex".tlog#tlog_acbalref'." "Fragi
>
> Oncheck did not help:
>
> oncheck -cI cortex:tlog#tlog_acbalref
>
> Validating index tlog_acbalref for cortex:cortex.tlog...
> Index tlog_acbalref
> Index fragment in DBspace tlogidx
> ERROR:No btree item exists for data row.
> Fragid 0x500002 Rowid 0x2ba4b001 contains key value:
> Key: "":" ":37435:
> Please Drop and ReCreate Index tlog_acbalref for cortex:cortex.tlog.
>
> The problem is that this table is very large (about 10 milions records)
> -
> recreating index will take long time and we cannot stop our system
> for so long time.
>
> Could you please advise me what else can I do except drop/create index?
>
> Thank you in advance.
>
> Best regards.
> --
> Nadezda Boltach

Messages In This Thread

• Re: Please help: problem with index
  ART KAGEL, BLOOMBERG/ 65E 55TH -- Friday, 9 May 2003, at 9:37 a.m.
  • RE: Please help: problem with index
    Palmer, George -- Friday, 9 May 2003, at 11:25 a.m.
    • RE: Please help: problem with index
      Alexey Sonkin -- Friday, 9 May 2003, at 3:38 p.m.
  • RE: Please help: problem with index
    Colin Bull -- Friday, 9 May 2003, at 11:51 a.m.

IDS Forum is maintained by Administrator with WebBBS 5.12.