Re: Please help: problem with index
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
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
ART KAGEL, .... wrote
>
> 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.
I think you should run the command -
update statistics for procedure ;
at this point as procedures seem to use a handle to the old table and are
not by table name
>
> 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?
> >
>
Colin Bull
c.bull@videonetworks.com
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
>