Error 136
A user on IDS 9.40 hit ISAM error -136 (no more extents) while loading a 200-million-row table, despite specifying a huge initial extent (~50GB) and 5GB next extent. Responders explained that -136 can also mean out of space, that an extent can never exceed the size of a single chunk, that fragmented free space and the ~200-extent/16-million-page-per-partition limits apply, and suggested checking oncheck -pe, coalescing free space by reorganising other tables, or fragmenting the table across dbspaces so each fragment has its own limits. The poster replied that space wasn't the issue and that the same table loads fine on another 9.40 server while needing 19 chunks on the problem server. No resolution is recorded in the thread.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Hi to All on IDS 9.40 FC9 , i cannot load a more than 200 000 000 rows table, because of extents number. i defined a primary extent with 65 gigs and the next 500 mo , but i still cannot load the entire table, i have a 136 ISAM Error any help ? thank's in advance
136 can also be out of disk space. Can you post the DDL for the table in question. and the size of the raw = data you are trying to load? j.=20 On Apr 6, 2011, at 12:32 PM, SMITH JOHN wrote: > Hi to All=20 >=20 > on IDS 9.40 FC9 , i cannot load a more than 200 000 000 rows table, = because of=20 > extents number.=20 >=20 > i defined a primary extent with 65 gigs and the next 500 mo , but i = still=20 > cannot load the entire table, i have a 136 ISAM Error=20 >=20 > any help ?=20 >=20 > thank's in advance=20 >=20 >=20 > = **************************************************************************= *****=20 > Forum Note: Use "Reply" to post a response in the discussion forum.=20= >=20
On Wed, Apr 6, 2011 at 09:32, SMITH JOHN <daylight@webmails.com> wrote:
> on IDS 9.40 FC9
Time to upgrade.
> I cannot load a more than 200 000 000 rows table, because of
> extents number.
>
How long are your bits of string - I mean, how big are the rows?
> I defined a primary extent with 65 gigs and the next 500 mo , but I still
> cannot load the entire table; I have a 136 ISAM Error.
>
136: ISAM error: No more extents...
You'll probably need to run 'oncheck -ce' (or 'oncheck -pe') to analyze the
extents, but it is likely that your table has too many small extents. And
further, until you drop the table, it is unlikely that you will be able to
recover the space sanely - and even then it isn't guaranteed. If you were
on a modern (11.50, 11.70) server, there are ways of dealing with the
problem. On 9.40, it is much harder; much, much harder.
You may need to consolidate several other tables to clean up the space
enough. You may need to review the extent sizing. How big are the chunks
in the dbspace? If none of them is bigger than 65 GiB, you're likely to
start off with problems because your initial extent is smaller than you
intended.
Since you don't mention long transaction problems, I assume the load
operation is broken down into multiple sub-transactions. I also assume that
you aren't having logical logs dynamically allocated and the dynamic log
allocation is not interfering with the contiguity of your data extents? And
that you have no indexes on the loaded table so indexes aren't interfering
with the contiguity of your data extents?
--
Jonathan Leffler <jonathan.leffler@gmail.com> #include <disclaimer.h>
Guardian of DBD::Informix - v2008.0513 - http://dbi.perl.org
"Blessed are we who can laugh at ourselves, for we shall never cease to be
amused."
--000e0cd14f8858292304a042ae3b
First, the largest extent that the server can create is the size of a
chunk. So, unless you have a single 65GB chunk in the dbspace containing
this table, the initial extent is far smaller. Second, the largest extent
may be significantly smaller than a chunk if there are other tables and
indexes in the dbspace and/or the free space is fragmented into many small
pieces (onspaces -pe will show you this). Third, we may need a bit more
information to give you the best help:
- What is the width of a row?
- Does this table contain any variable length columns?
- What is the average actual row width?
- What are the chunk sizes?
- Can you consolidate all of the free space in the dbspace into as few
contiguous blocks as possible? If you can then Informix will coalesce
contiguous extents into a single larger extent reducing the number of
extents needed. This can be done by dropping this table, reorging the
remaining tables and indexes in the dbspace using "ALTER FRAGMENT ON
TABLE/INDEX <tabname|idxname> INIT IN <dbspacename>;" until oncheck -pe
shows that the majority of free space is contiguous.
If all else fails, you can fragment the table across multiple dbspaces since
the ~200 extent limit applies to each fragment or partition separately.
Art
Art S. Kagel
Advanced DataTools (www.advancedatatools.com)
Blog: http://informix-myview.blogspot.com/
Disclaimer: Please keep in mind that my own opinions are my own opinions and
do not reflect on my employer, Advanced DataTools, the IIUG, nor any other
organization with which I am associated either explicitly, implicitly, or by
inference. Neither do those opinions reflect those of other individuals
affiliated with any entity with which I am affiliated nor those of the
entities themselves.
On Wed, Apr 6, 2011 at 12:32 PM, SMITH JOHN <daylight@webmails.com> wrote:
> Hi to All
>
> on IDS 9.40 FC9 , i cannot load a more than 200 000 000 rows table, because
> of
> extents number.
>
> i defined a primary extent with 65 gigs and the next 500 mo , but i still
> cannot load the entire table, i have a 136 ISAM Error
>
> any help ?
>
> thank's in advance
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--bcaec53f91ddc6b14604a042dc76
Could also be hitting the 16million page limit for a partition. That's why I asked for more information. Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Wed, Apr 6, 2011 at 12:38 PM, Jack Parker <jack.parker4@verizon.net>wrote: > 136 can also be out of disk space. > > Can you post the DDL for the table in question. and the size of the raw = > data you are trying to load? > > j.=20 > On Apr 6, 2011, at 12:32 PM, SMITH JOHN wrote: > > > Hi to All=20 > >=20 > > on IDS 9.40 FC9 , i cannot load a more than 200 000 000 rows table, = > because of=20 > > extents number.=20 > >=20 > > i defined a primary extent with 65 gigs and the next 500 mo , but i = > still=20 > > cannot load the entire table, i have a 136 ISAM Error=20 > >=20 > > any help ?=20 > >=20 > > thank's in advance=20 > >=20 > >=20 > > = > **************************************************************************= > *****=20 > > Forum Note: Use "Reply" to post a response in the discussion forum.=20= > > >=20 > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --20cf3071ced8e0e6f404a042f618
In case you are hitting the 16million page limit for a partition, you can use partition in "round robin" or "by expression" creating how many 16million page partition you need or you want. Celso Cabral Coimbra Administrador de Banco de Dados ClearTech Ltda "Trust at the heart of Communications" Tel. (11) 3576-4509 -----Mensagem original----- De: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] Em nome de Art Kagel Enviada em: quarta-feira, 6 de abril de 2011 14:03 Para: ids@iiug.org Assunto: Re: Error 136 [23355] Could also be hitting the 16million page limit for a partition. That's why I asked for more information. Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Wed, Apr 6, 2011 at 12:38 PM, Jack Parker <jack.parker4@verizon.net>wrote: > 136 can also be out of disk space. > > Can you post the DDL for the table in question. and the size of the raw = > data you are trying to load? > > j.=20 > On Apr 6, 2011, at 12:32 PM, SMITH JOHN wrote: > > > Hi to All=20 > >=20 > > on IDS 9.40 FC9 , i cannot load a more than 200 000 000 rows table, = > because of=20 > > extents number.=20 > >=20 > > i defined a primary extent with 65 gigs and the next 500 mo , but i = > still=20 > > cannot load the entire table, i have a 136 ISAM Error=20 > >=20 > > any help ?=20 > >=20 > > thank's in advance=20 > >=20 > >=20 > > = > **************************************************************************= > *****=20 > > Forum Note: Use "Reply" to post a response in the discussion forum.=20= > > >=20 > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --20cf3071ced8e0e6f404a042f618 ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.
no it's not a matter of space, i verified
i have a 240 Go chunk with 70 Go free
the wierd thing is that the same table on another IDS 9.40 FC0 server doesn't
make problem, 2 chunks of 50 Go are enough.
but on the serveur where i have the problem, this table takes 19 chunks with
the same definition below
create table mytable (.
.
colmuns
.
.
)
extent size 53090724 next size 5309088
lock mode row;