IDS Forum

RE: Please help: problem with index

Posted By:
Date:

Some observations.

I have used a similar plan to alter tables in OLTP systems ( before in place
alter)
On occasion I also corrected index issue with same method.

- The new table required the indexes built before the copy started.
the triggers I used, used primary key values for updates, and deletes.
as the table gets loaded, the trigger events for update and delete
will slow down without indexes to find the rows. A delete or update
is consider successful if no rows meet the query criteria.

- Take care when building the triggers on the existing table. A create
trigger is considered a table alter and can have consequences on
the applications accessing the table ( depending on how the application
was created/coded). The application I used this method against
needed to be shut down during the trigger criterion.

- The speed of the copy is not very important. With the triggers keeping
the tables in "sync" the copy just needs to complete ( ignoring rows
that have already "arrived" due to the insert trigger. The copy
will need to avoid using indexes to find its data ( as the indexes
are not in good shape in the existing table.)

- After the copy is complete the outage for the renames remains the same.

George

-----Original Message-----
From: ART KAGEL, .... [mailto:KAGEL@bloomberg.net]
Sent: Friday, May 09, 2003 6:37 AM
To: ids@iiug.org
Subject: Re: Please help: problem with index [1103]


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.