Board index Informix

repair an Index on a big table (IDS 7.31 on AIX 4.2.1)

repair an Index on a big table (IDS 7.31 on AIX 4.2.1)

Post by Peter Weiger » Fri, 28 Sep 2001 17:37:28



Hi,
I have a table with 215.000.000 rows with a column datetime year to second which is indexed.
This Index is corrupted (and not useable).
To recstore the Index I tried with oncheck -cI
within the Database i got an error: "out of diskspace" (of corse there is enough space in my database)

Informix Dynamic Server Version 7.31.UC2X3 -- On-Line -- Up 04:30:50 -- 93040 Kb
ytes

Dbspaces
address  number   flags    fchunk   nchunks  flags    owner    name
4004c13c 1        1        1        1        N        informix rootdbs
40075704 2        11       2        1        N B      informix dfuedbs
400757c0 3        2001     3        1        N T      informix tmpdbs
4007587c 4        1        4        1        N        informix physdbs
40075938 5        1        5        1        N        informix ardbs
400759f4 6        1        6        27       N        informix stddbs
40075ab0 7        1        21       3        N        informix logdbs
 7 active, 2047 maximum

Chunks
address  chk/dbs offset   size     free     bpages   flags pathname
4004c1f8 1   1   0        8000     6565              PO-   /charlydb/dbi/rootdbs
.inf
4004c3b4 2   2   0        187500  ~186749   187500   POB   /charlydb/dbi/dfuedbs
.inf
4004c490 3   3   0        15000    14947             PO-   /charlydb/dbi/tmp1dbs
.inf
4004c56c 4   4   0        3750     0                 PO-   /charlydb/dbi/physdbs
.inf
4004c648 5   5   0        250000   192447            PO-   /charlydb/dbi/ar01dbs
.inf
4004c724 6   6   0        500000   3                 PO-   /db1/std01dbs.inf
4004c800 7   6   0        500000   5                 PO-   /db2/std02dbs.inf
4004c8dc 8   6   0        500000   581               PO-   /db3/std03dbs.inf
4004c9b8 9   6   0        500000   589               PO-   /db4/std04dbs.inf
4004ca94 10  6   0        500000   1477              PO-   /db5/std05dbs.inf
4004cb70 11  6   0        500000   1277              PO-   /db6/std06dbs.inf
4004cc4c 12  6   0        500000   989               PO-   /db7/std07dbs.inf
4004cd28 13  6   0        500000   5325              PO-   /db8/std08dbs.inf
4004ce04 14  6   0        500000   1157              PO-   /db9/std09dbs.inf
4004cee0 15  6   0        500000   1141              PO-   /db10/std10dbs.inf
4004cfbc 16  6   0        500000   997               PO-   /db11/std11dbs.inf
4004d098 17  6   0        500000   29                PO-   /db12/std12dbs.inf
4004d174 18  6   0        500000   6989              PO-   /db13/std13dbs.inf
4004d250 19  6   0        500000   29                PO-   /db14/std14dbs.inf
4004d32c 20  6   0        500000   3029              PO-   /db15/std15dbs.inf
4004d408 21  7   0        25000    947               PO-   /charlydb/dbi/log1dbs
.inf
4004d4e4 22  7   0        25000    997               PO-   /charlydb/dbi/log2dbs
.inf
4004d5c0 23  6   0        500000   29                PO-   /db16/std16dbs.inf
4004d69c 24  6   0        500000   7053              PO-   /db17/std17dbs.inf
4004d778 25  6   0        500000   0                 PO-   /db18/std18dbs.inf
4004d854 26  6   0        500000   0                 PO-   /db19/std19dbs.inf
4004d930 27  6   0        500000   38285             PO-   /db20/std20dbs.inf
4004da0c 28  6   0        500000   0                 PO-   /db21/std21dbs.inf
4004dae8 29  6   0        500000   104605            PO-   /db22/std22dbs.inf
4004dbc4 30  6   0        500000   24861             PO-   /db23/std23dbs.inf
4004dca0 31  6   0        500000   0                 PO-   /db24/std24dbs.inf
4004dd7c 32  6   0        500000   88864             PO-   /db25/std25dbs.inf
4004de58 33  6   0        500000   0                 PO-   /db26/std26dbs.inf
4004df34 34  6   0        500000   237853            PO-   /db27/std27dbs.inf
40075628 35  7   0        150000   997               PO-   /charlydb/dbi/log3dbs
.inf
 35 active, 2047 maximum
To assign the location of the sortfile I tried different methods for
1. DBSPACETEMP=tmpdbs,stddbs
2. DBSPACETEMP=stddbs

In both cases I gut an out-of-diskspace error (within a short time).

I tried to locate the sortfile in a external directory (using PSORT_DBTEMP) which failed due to the 2 GB limit (32 Bit implementation of Informix external file-operations)

Informix-Hotline assumed that some temporary data might be written into the root-tablespace when sorting in the Database - but they seemed not to be certain... but they said IDS can handle data > 2GB inside the DB (althogh I always invented the Problem that you can't use a chunk > 2GB with IDS 7.31)
(I can't put the engine in quiescent-mode for such vague test, because the machine is a central data dispatch server;
the engine is able to do it's primary tasks, but I can't extract time-framed data from this table any more)

Any hints, declarations, suggestions how to repair/recreate the index...

Thanks in advance
Peter

 
 
 
Top

repair an Index on a big table (IDS 7.31 on AIX 4.2.1)

Post by Neil Trub » Fri, 28 Sep 2001 20:20:46


If you zero out the stats and do frequent onstat -Ds you should be able to
see which dbspace is filling.  That may give you some clues.
At a brief glance, it doesn't look to me like you've got as much as 2GBytes
of free space in your non-blob dbspaces.
Why not just add some more disk to tmpdbs - even if it's cooked -  just to
get you through the problem?

--
Neil Truby                       t:01932 724027
Director                         m:07798 811708


Hi,
I have a table with 215.000.000 rows with a column datetime year to second
which is indexed.
This Index is corrupted (and not useable).
To recstore the Index I tried with oncheck -cI
within the Database i got an error: "out of diskspace" (of corse there is
enough space in my database)

Informix Dynamic Server Version 7.31.UC2X3 -- On-Line -- Up 04:30:50 --
93040 Kb
ytes

Dbspaces
address  number   flags    fchunk   nchunks  flags    owner    name
4004c13c 1        1        1        1        N        informix rootdbs
40075704 2        11       2        1        N B      informix dfuedbs
400757c0 3        2001     3        1        N T      informix tmpdbs
4007587c 4        1        4        1        N        informix physdbs
40075938 5        1        5        1        N        informix ardbs
400759f4 6        1        6        27       N        informix stddbs
40075ab0 7        1        21       3        N        informix logdbs
 7 active, 2047 maximum

Chunks
address  chk/dbs offset   size     free     bpages   flags pathname
4004c1f8 1   1   0        8000     6565              PO-
/charlydb/dbi/rootdbs
.inf
4004c3b4 2   2   0        187500  ~186749   187500   POB
/charlydb/dbi/dfuedbs
.inf
4004c490 3   3   0        15000    14947             PO-
/charlydb/dbi/tmp1dbs
.inf
4004c56c 4   4   0        3750     0                 PO-
/charlydb/dbi/physdbs
.inf
4004c648 5   5   0        250000   192447            PO-
/charlydb/dbi/ar01dbs
.inf
4004c724 6   6   0        500000   3                 PO-   /db1/std01dbs.inf
4004c800 7   6   0        500000   5                 PO-   /db2/std02dbs.inf
4004c8dc 8   6   0        500000   581               PO-   /db3/std03dbs.inf
4004c9b8 9   6   0        500000   589               PO-   /db4/std04dbs.inf
4004ca94 10  6   0        500000   1477              PO-   /db5/std05dbs.inf
4004cb70 11  6   0        500000   1277              PO-   /db6/std06dbs.inf
4004cc4c 12  6   0        500000   989               PO-   /db7/std07dbs.inf
4004cd28 13  6   0        500000   5325              PO-   /db8/std08dbs.inf
4004ce04 14  6   0        500000   1157              PO-   /db9/std09dbs.inf
4004cee0 15  6   0        500000   1141              PO-
/db10/std10dbs.inf
4004cfbc 16  6   0        500000   997               PO-
/db11/std11dbs.inf
4004d098 17  6   0        500000   29                PO-
/db12/std12dbs.inf
4004d174 18  6   0        500000   6989              PO-
/db13/std13dbs.inf
4004d250 19  6   0        500000   29                PO-
/db14/std14dbs.inf
4004d32c 20  6   0        500000   3029              PO-
/db15/std15dbs.inf
4004d408 21  7   0        25000    947               PO-
/charlydb/dbi/log1dbs
.inf
4004d4e4 22  7   0        25000    997               PO-
/charlydb/dbi/log2dbs
.inf
4004d5c0 23  6   0        500000   29                PO-
/db16/std16dbs.inf
4004d69c 24  6   0        500000   7053              PO-
/db17/std17dbs.inf
4004d778 25  6   0        500000   0                 PO-
/db18/std18dbs.inf
4004d854 26  6   0        500000   0                 PO-
/db19/std19dbs.inf
4004d930 27  6   0        500000   38285             PO-
/db20/std20dbs.inf
4004da0c 28  6   0        500000   0                 PO-
/db21/std21dbs.inf
4004dae8 29  6   0        500000   104605            PO-
/db22/std22dbs.inf
4004dbc4 30  6   0        500000   24861             PO-
/db23/std23dbs.inf
4004dca0 31  6   0        500000   0                 PO-
/db24/std24dbs.inf
4004dd7c 32  6   0        500000   88864             PO-
/db25/std25dbs.inf
4004de58 33  6   0        500000   0                 PO-
/db26/std26dbs.inf
4004df34 34  6   0        500000   237853            PO-
/db27/std27dbs.inf
40075628 35  7   0        150000   997               PO-
/charlydb/dbi/log3dbs
.inf
 35 active, 2047 maximum
To assign the location of the sortfile I tried different methods for
1. DBSPACETEMP=tmpdbs,stddbs
2. DBSPACETEMP=stddbs

In both cases I gut an out-of-diskspace error (within a short time).

I tried to locate the sortfile in a external directory (using PSORT_DBTEMP)
which failed due to the 2 GB limit (32 Bit implementation of Informix
external file-operations)

Informix-Hotline assumed that some temporary data might be written into the
root-tablespace when sorting in the Database - but they seemed not to be
certain... but they said IDS can handle data > 2GB inside the DB (althogh I
always invented the Problem that you can't use a chunk > 2GB with IDS 7.31)
(I can't put the engine in quiescent-mode for such vague test, because the
machine is a central data dispatch server;
the engine is able to do it's primary tasks, but I can't extract time-framed
data from this table any more)

Any hints, declarations, suggestions how to repair/recreate the index...

Thanks in advance
Peter

 
 
 
Top

repair an Index on a big table (IDS 7.31 on AIX 4.2.1)

Post by DJW » Sat, 29 Sep 2001 09:45:50


Drop the index and then create it rather than
letting oncheck do it.

"Neil Truby" <neil.tr...@ardenta.co.uk> wrote in message

news:q_Ds7.72272$a74.294968@NewsReader...
> If you zero out the stats and do frequent onstat -Ds you should be able to
> see which dbspace is filling.  That may give you some clues.
> At a brief glance, it doesn't look to me like you've got as much as
2GBytes
> of free space in your non-blob dbspaces.
> Why not just add some more disk to tmpdbs - even if it's cooked -  just to
> get you through the problem?

> --
> Neil Truby                       t:01932 724027
> Director                         m:07798 811708
> Ardenta Limited              e:neil.tr...@ardenta.com

> "Peter Weigert" <Peter.Weig...@ils-consult.at> wrote in message
> news:rzBs7.5$pj4.625@nreader2.kpnqwest.net...
> Hi,
> I have a table with 215.000.000 rows with a column datetime year to second
> which is indexed.
> This Index is corrupted (and not useable).
> To recstore the Index I tried with oncheck -cI
> within the Database i got an error: "out of diskspace" (of corse there is
> enough space in my database)

> Informix Dynamic Server Version 7.31.UC2X3 -- On-Line -- Up 04:30:50 --
> 93040 Kb
> ytes

> Dbspaces
> address  number   flags    fchunk   nchunks  flags    owner    name
> 4004c13c 1        1        1        1        N        informix rootdbs
> 40075704 2        11       2        1        N B      informix dfuedbs
> 400757c0 3        2001     3        1        N T      informix tmpdbs
> 4007587c 4        1        4        1        N        informix physdbs
> 40075938 5        1        5        1        N        informix ardbs
> 400759f4 6        1        6        27       N        informix stddbs
> 40075ab0 7        1        21       3        N        informix logdbs
>  7 active, 2047 maximum

> Chunks
> address  chk/dbs offset   size     free     bpages   flags pathname
> 4004c1f8 1   1   0        8000     6565              PO-
> /charlydb/dbi/rootdbs
> .inf
> 4004c3b4 2   2   0        187500  ~186749   187500   POB
> /charlydb/dbi/dfuedbs
> .inf
> 4004c490 3   3   0        15000    14947             PO-
> /charlydb/dbi/tmp1dbs
> .inf
> 4004c56c 4   4   0        3750     0                 PO-
> /charlydb/dbi/physdbs
> .inf
> 4004c648 5   5   0        250000   192447            PO-
> /charlydb/dbi/ar01dbs
> .inf
> 4004c724 6   6   0        500000   3                 PO-
/db1/std01dbs.inf
> 4004c800 7   6   0        500000   5                 PO-
/db2/std02dbs.inf
> 4004c8dc 8   6   0        500000   581               PO-
/db3/std03dbs.inf
> 4004c9b8 9   6   0        500000   589               PO-
/db4/std04dbs.inf
> 4004ca94 10  6   0        500000   1477              PO-
/db5/std05dbs.inf
> 4004cb70 11  6   0        500000   1277              PO-
/db6/std06dbs.inf
> 4004cc4c 12  6   0        500000   989               PO-
/db7/std07dbs.inf
> 4004cd28 13  6   0        500000   5325              PO-
/db8/std08dbs.inf
> 4004ce04 14  6   0        500000   1157              PO-
/db9/std09dbs.inf
> 4004cee0 15  6   0        500000   1141              PO-
> /db10/std10dbs.inf
> 4004cfbc 16  6   0        500000   997               PO-
> /db11/std11dbs.inf
> 4004d098 17  6   0        500000   29                PO-
> /db12/std12dbs.inf
> 4004d174 18  6   0        500000   6989              PO-
> /db13/std13dbs.inf
> 4004d250 19  6   0        500000   29                PO-
> /db14/std14dbs.inf
> 4004d32c 20  6   0        500000   3029              PO-
> /db15/std15dbs.inf
> 4004d408 21  7   0        25000    947               PO-
> /charlydb/dbi/log1dbs
> .inf
> 4004d4e4 22  7   0        25000    997               PO-
> /charlydb/dbi/log2dbs
> .inf
> 4004d5c0 23  6   0        500000   29                PO-
> /db16/std16dbs.inf
> 4004d69c 24  6   0        500000   7053              PO-
> /db17/std17dbs.inf
> 4004d778 25  6   0        500000   0                 PO-
> /db18/std18dbs.inf
> 4004d854 26  6   0        500000   0                 PO-
> /db19/std19dbs.inf
> 4004d930 27  6   0        500000   38285             PO-
> /db20/std20dbs.inf
> 4004da0c 28  6   0        500000   0                 PO-
> /db21/std21dbs.inf
> 4004dae8 29  6   0        500000   104605            PO-
> /db22/std22dbs.inf
> 4004dbc4 30  6   0        500000   24861             PO-
> /db23/std23dbs.inf
> 4004dca0 31  6   0        500000   0                 PO-
> /db24/std24dbs.inf
> 4004dd7c 32  6   0        500000   88864             PO-
> /db25/std25dbs.inf
> 4004de58 33  6   0        500000   0                 PO-
> /db26/std26dbs.inf
> 4004df34 34  6   0        500000   237853            PO-
> /db27/std27dbs.inf
> 40075628 35  7   0        150000   997               PO-
> /charlydb/dbi/log3dbs
> .inf
>  35 active, 2047 maximum
> To assign the location of the sortfile I tried different methods for
> 1. DBSPACETEMP=tmpdbs,stddbs
> 2. DBSPACETEMP=stddbs

> In both cases I gut an out-of-diskspace error (within a short time).

> I tried to locate the sortfile in a external directory (using
PSORT_DBTEMP)
> which failed due to the 2 GB limit (32 Bit implementation of Informix
> external file-operations)

> Informix-Hotline assumed that some temporary data might be written into
the
> root-tablespace when sorting in the Database - but they seemed not to be
> certain... but they said IDS can handle data > 2GB inside the DB (althogh
I
> always invented the Problem that you can't use a chunk > 2GB with IDS
7.31)
> (I can't put the engine in quiescent-mode for such vague test, because the
> machine is a central data dispatch server;
> the engine is able to do it's primary tasks, but I can't extract
time-framed
> data from this table any more)

> Any hints, declarations, suggestions how to repair/recreate the index...

> Thanks in advance
> Peter

 
 
 
Top

1. IDS 7.31 on AIX 5.1

Hi,
And about IDS 7.30.UC7 on AIX 5.1 (Even 5.2) on an p670 running LPAR?
Tanks for your help.

-----Message d'origine-----

Envoy : mercredi 8 janvier 2003 19:43

Objet : RE: IDS 7.31 on AIX 5.1

Lisa,

We have encountered no (new) problems when running 32-bit versions of
IDS 7.31 on AIX 5.1 vs. AIX 4.3.3.  The 64-bit versions are a
different story.  

When I looked and inquired last year, IBM had no 64-bit IDS 7.31
version available for AIX 5.1 and no plans to create one.

Rick

2. NY-NYC-Manhattan --> C++ Oracle OLTP

3. ^^

4. IDS 7.31 on AIX 4.3.3

5. xp_unc_to_drive?

6. IDS 7.31 on AIX 5.1?

7. DBCombo listing

8. ISM_Startup errors IDS 7.31 on AIX

9. ISM_Startup errors IDS 7.31 on AIX - SOLUTION

10. IDS 7.31 using e-mail on AIX

11. IDS 7.31 and OnBar and ADSM on AIX

12. Index Build using PDQ running real slow IDS 7.31 Solaris 8


All times are UTC
Board index