IDS Forum

RE: Please help: problem with index

Posted By:
Date:

The table is called 'LOG'. It seems it is insert-only
table. For such tables, procedure can be simplified:

1. Create new_table without indexes;
2. Create SPL-procedure that copies original
table to the new one. Run it online.
It's just took me 10 hours to copy 20 million table.
3. Using PDQ, create indexes for new table.
4. Run update statistics on new table.
5. Create simple SQL to load new table itaratively:
LOCK NEW_TABLE IN EXCLUSIVE MODE;
INSERT INTO NEW_TABLE
SELECT * FROM OLD_TABLE
WHERE (ID > (select max(ID) from NEW_TABLE);
(ID on new_table should be indexed by that time)
6. Run this SQL several times iteratively to minimize unsync
7. Stop system. Run iterative sync SQL again, Swap tables. Start system
(the system is really stopped for few seconds!!!)

------------------------------------------
Alexey Sonkin
Senior Database Administrator


> -----Original Message-----
> From: Palmer, George [mailto:George.Palmer@pegs.com]
> Sent: Friday, May 09, 2003 11:26 AM
> To: ids@iiug.org
> Subject: RE: Please help: problem with index [1104]
>
> 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.