 |
IDS Forum
Re: No more pages in a tablespace
Posted By: Art Kagel
Date: Tuesday, 24 April 2012, at 9:57 a.m.
In Response To: RE: No more pages in a tablespace (LARRY SORENSEN)
Yes.
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 Tue, Apr 24, 2012 at 6:33 AM, LARRY SORENSEN <lsorensen25@msn.com> wrote:
> Art, thank you for this explanation. It makes sense to me.
>
> As far as the unique index, I should just need to drop the existing index,
> and
> then create a unique index in a specific dbspace?
>
> Thank you.
>
> Larry
>
> > To: ids@iiug.org
> > From: art.kagel@gmail.com
> > Subject: Re: No more pages in a tablespace [26821]
> > Date: Tue, 24 Apr 2012 05:27:40 -0400
> >
> > OK, when you create an index on a fragmented table and do not specify an
> IN
> > clause or fragmentation scheme then the index is fragmented the same way
> as
> > the table it describes. However, you have fragmented the table using
> round
> > robin fragmentation and you cannot fragment an index round robin because
> > there would be no way to find a given index key except by scanning all
> > fragments of the index and that's just too inefficient, so Informix said
> > "No." when you asked it to do that. If you fragment the index by the key
> > column, or part of it, or use an IN clause to place the index
> unfragmented
> > into a single dbspace, that will be OK.
> >
> > On your second question, you altered the table's storage from
> > non-fragmented to round-robin fragmentation. Round robin means that new
> > rows added to the table will be assigned to each of the fragments in a
> > round robin fashion. This does not require that existing rows be
> > redistributed so the existing rows were just dumped into one fragment and
> > so you still had more than 16million pages in that one fragment. If you
> > had fragmented by some expression on sets of values in a column (say all
> > rows with the serial column's value between 1 and 2000000 in the first
> > fragment, between 2000001 and 4000000 in the second fragment, etc, that
> > would have forced the rows to be redistributed across the <N> fragments
> and
> > all would have been well. Your solution, to unload the table, create the
> > table empty but fragmented round robin, then reload the rows accomplished
> > the same thing since the inserts were distributed round robin across the
> > fragments.
> >
> > 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 Mon, Apr 23, 2012 at 9:04 AM, LARRY SORENSEN <lsorensen25@msn.com>
> wrote:
> >
> > > Thank you all for your help with this issue. I have one more question,
> and
> > > I
> > > will also post my experience.
> > >
> > > 1) I fragmented the table as well as moved it into a new dbspace with a
> > > larger
> > > page size. When I tried to create the unique index on a column, as it
> was
> > > prior, I received an error message to the affect that it was not
> possible
> > > on a
> > > fragmented table. I ended up creating a normal index; however, I would
> > > prefer
> > > to have the unique constraint. Any ideas or comments?
> > >
> > > Experience
> > >
> > > First, I created a separate dbspace where I added 30+ GB of logical
> logs.
> > > I then performed an "ALTER FRAGMENT ON TABLE ..... INIT PARTITION BY
> ROUND
> > > ROBIN
> > >
> > > PARTITION part1 in dbspace1,
> > >
> > > PARTITION part2 in dbspace1....etc
> > >
> > > That ran for quite a while to completion. I never saw any rollback of
> the
> > > transaction; however, when it was done, I noticed the same error
> message
> in
> > > the online.log file stating that there were no more pages at about the
> time
> > > the ALTER statement finished. I performed an oncheck -pt on the table
> and
> > > it
> > > was the same.
> > >
> > > I then resorted to unloading the entire table, which took a long time,
> > > creating a new dbspace with a larger page size, creating an new table,
> > > ALTER
> > > FRAGMENT on the new table; and then I loaded the new table. That worked
> > > fine,
> > > but I wonder why the first try, without the unload, didn't work.
> > >
> > > Larry
> > >
> > > > To: ids@iiug.org
> > > > From: art.kagel@gmail.com
> > > > Subject: Re: No more pages in a tablespace [26792]
> > > > Date: Fri, 20 Apr 2012 18:18:46 -0400
> > > >
> > > > 1. Add lots more logical logs, sufficient to hold the whole
> transaction
> > > >
> > > > plus all of the other activity on the server during the reorg run -
> or -
> > > >
> > > > 2. ALTER TABLE <tablename> TYPE (raw); ALTER FRAGMENT...INIT...;
> ALTER
> > > >
> > > > TABLE <tablename> TYPE (standard);
> > > >
> > > > 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 Fri, Apr 20, 2012 at 4:20 PM, Larry Sorensen <lsorensen25@msn.com
> >
> > > wrote:
> > > >
> > > > > OK. So I just ran into a long transaction. What are my options now
> to
> > > get
> > > > > this done?
> > > > >
> > > > > -----Original Message-----
> > > > > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On
> Behalf Of
> > > Art
> > > > > Kagel
> > > > > Sent: Friday, April 20, 2012 1:15 PM
> > > > > To: ids@iiug.org
> > > > > Subject: Re: No more pages in a tablespace [26781]
> > > > >
> > > > > Either fragment the table (ALTER FRAGMENT FOR <tablename> INIT
> FRAGMENT
> > > > > ......) or move the table to a dbspace with wider pages so that
> more
> > > rows
> > > > > fit
> > > > > on a page - so fewer pages. Choose a page size to minimize wasted
> > > space on
> > > > > the pages. That would just be ALTER FRAGMENT FOR <tablename> INIT
> IN
> > > <new
> > > > > dbspace>;
> > > > >
> > > > > 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 Fri, Apr 20, 2012 at 10:12 AM, LARRY SORENSEN <
> lsorensen25@msn.com
> > > > > >wrote:
> > > > >
> > > > > > OK. So, now that I have reached that limit, what are the steps
> that
> I
> > > > > > need to go through to fragment that production table?
> > > > > > > To: ids@iiug.org
> > > > > > > From: paul@oninit.com
> > > > > > > Subject: RE: No more pages in a tablespace [26767]
> > > > > > > Date: Fri, 20 Apr 2012 10:11:18 -0400
> > > > > > >
> > > > > > > Still a hard limit AFAIK
> > > > > > >
> > > > > > > Cheers
> > > > > > > Paul
> > > > > > >
> > > > > > > -----Original Message-----
> > > > > > > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On
> Behalf
> > > > > > > Of
> > > > > > Larry
> > > > > > > Sorensen
> > > > > > > Sent: Friday, April 20, 2012 9:09 AM
> > > > > > > To: ids@iiug.org
> > > > > > > Subject: RE: No more pages in a tablespace [26766]
> > > > > > >
> > > > > > > I am running IDS 11.50.FC5 on Solaris 10
> > > > > > >
> > > > > > > I am receiving the error
> > > > > > >
> > > > > > > 08:05:20 Process exited with return code 1: /bin/sh /bin/sh -c
> > > > > > > /opt/informix/production/etc/log_full.sh 3 46 "part ition
> > > > > > > 'dle_gen:informix.eclaim_h_hist': no more pages" "" ""
> > > > > > >
> > > > > > > oncheck -pt shows the following:
> > > > > > >
> > > > > > > > oncheck -pt dle_gen:informix.eclaim_h_history
> > > > > > >
> > > > > > > TBLspace Report for dle_gen:informix.eclaim_h_hist
> > > > > > >
> > > > > > > Physical Address 13:2368379
> > > > > > >
> > > > > > > Creation date 10/18/2009 03:16:04
> > > > > > >
> > > > > > > TBLspace Flags 800902 Row Locking
> > > > > > >
> > > > > > > TBLspace contains VARCHARS
> > > > > > >
> > > > > > > TBLspace use 4 bit bit-maps
> > > > > > >
> > > > > > > Maximum row size 1491
> > > > > > >
> > > > > > > Number of special columns 4
> > > > > > >
> > > > > > > Number of keys 7
> > > > > > >
> > > > > > > Number of extents 85
> > > > > > >
> > > > > > > Current serial value 16342518
> > > > > > >
> > > > > > > Current SERIAL8 value 1
> > > > > > >
> > > > > > > Current BIGSERIAL value 1
> > > > > > >
> > > > > > > Current REFID value 1
> > > > > > >
> > > > > > > Pagesize (k) 2
> > > > > > >
> > > > > > > First extent size 8
> > > > > > >
> > > > > > > Next extent size 524288
> > > > > > >
> > > > > > > Number of pages allocated 16777215
> > > > > > >
> > > > > > > Number of pages used 16777215
> > > > > > >
> > > > > > > Number of data pages 16281308
> > > > > > >
> > > > > > > Number of rows 16281308
> > > > > > >
> > > > > > > I saw a previous post by Art stating that a single fragment of
> a
> > > > > > > table cannot have more than 16 million pages. It appears that
> we
> > > may
> > > > > > > have
> > > > > > reached
> > > > > > > that limit. Is that still the case for IDS 11.50? I couldn't
> find
> a
> > > > > > version
> > > > > > > in the previous post.
> > > > > > >
> > > > > > > Also, if that is the case, what is the detailed process for
> > > > > > > fragmenting
> > > > > > this
> > > > > > >
> > > > > > > table?
> > > > > > >
> > > > > > > Larry
> > > > > > >
> > > > > > >
> > > > > >
> > > **********************************************************************
> > > > > > ******
> > > > > > > ***
> > > > > > > Forum Note: Use "Reply" to post a response in the discussion
> forum.
> > > > > > >
> > > > > > >
> > > > > > >
> > > > > >
> > > > > >
> > > > >
> > > > >
> > >
> > >
>
> ****************************************************************************
> > > > > ***
> > > > > > > Forum Note: Use "Reply" to post a response in the discussion
> forum.
> > > > > > >
> > > > > >
> > > > > >
> > > > > >
> > > > > >
> > > > >
> > > > >
> > >
> > >
>
> ****************************************************************************
> > > > > ***
> > > > > > Forum Note: Use "Reply" to post a response in the discussion
> forum.
> > > > > >
> > > > > >
> > > > >
> > > > > --e89a8f3ba75f2a4c5a04be211cd6
> > > > >
> > > > >
> > > > >
> > >
> > >
>
> ****************************************************************************
> > > > > ***
> > > > > Forum Note: Use "Reply" to post a response in the discussion forum.
> > > > >
> > > > >
> > > > >
> > > > >
> > > >
> > >
> > >
> >
>
> *******************************************************************************
> > > > > Forum Note: Use "Reply" to post a response in the discussion forum.
> > > > >
> > > > >
> > > >
> > > > --14dae9340c01a4e45304be23ab0b
> > > >
> > > >
> > > >
> > >
> > >
> >
>
> *******************************************************************************
> > > > Forum Note: Use "Reply" to post a response in the discussion forum.
> > > >
> > >
> > >
> > >
> > >
> >
>
> *******************************************************************************
> > > Forum Note: Use "Reply" to post a response in the discussion forum.
> > >
> > >
> >
> > --e89a8f3ba959184dd204be695d88
> >
> >
> >
>
> *******************************************************************************
> > Forum Note: Use "Reply" to post a response in the discussion forum.
> >
>
>
>
> *******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--bcaec5299a3119b1a104be6d22d5
Messages In This Thread
IDS Forum is maintained by Administrator with WebBBS 5.12.
|
|
