 |
IDS Forum
RE: No more pages in a tablespace and monitori....
Posted By: LARRY SORENSEN
Date: Thursday, 3 May 2012, at 4:26 p.m.
In Response To: Re: No more pages in a tablespace and monitori.... (Art Kagel)
Thank you, as always, for your help.
> To: ids@iiug.org
> From: art.kagel@gmail.com
> Subject: Re: No more pages in a tablespace and monitori.... [26960]
> Date: Thu, 3 May 2012 16:20:55 -0400
>
> The original message was saying that the engine couldn't find a function
> with that name and the types of arguments (or lack thereof) that you call
> it with. Informix supports function overloading so the arguments are part
> of the function's signature.
>
> 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 Thu, May 3, 2012 at 4:16 PM, LARRY SORENSEN <lsorensen25@msn.com> wrote:
>
> > Oops! I forgot to take the arguments out of the original function. I took
> > them
> > out and now it seems to run without an error. The original error message
> > was
> > not very descriptive. Thank you.
> > > To: ids@iiug.org
> > > From: art.kagel@gmail.com
> > > Subject: Re: No more pages in a tablespace and monitori.... [26957]
> > > Date: Thu, 3 May 2012 16:11:35 -0400
> > >
> > > OK, so, when you ran it, did you pass in two integers to satisfy the two
> > > mandatory arguments task_id and task_seq? Try:
> > >
> > > dbaccess sysadmin - <<!
> > > execute function check_table_pages( 999,999);
> > > !
> > >
> > > 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 Thu, May 3, 2012 at 4:04 PM, LARRY SORENSEN <lsorensen25@msn.com>
> > wrote:
> > >
> > > > Here is a snipet from the first part of the function, if that helps...
> > > > DATABASE sysadmin;DROP FUNCTION check_table_pages1;CREATE FUNCTION
> > > > check_table_pages1(task_id INT, task_seq INT) RETURNING INTEGER
> > > > DEFINE database_name CHAR(129);
> > > > DEFINE table_owner CHAR(33);
> > > > DEFINE table_name CHAR(129);
> > > > DEFINE pages_used INTEGER;
> > > > DEFINE pages_used_pcnt INTEGER;
> > > > DEFINE alt_type CHAR(10);
> > > > DEFINE alt_color CHAR(10);
> > > > DEFINE alt_object_type CHAR(10);
> > > > DEFINE alt_table_name CHAR(100);
> > > > DEFINE alt_message CHAR(100);CREATE TEMP TABLE page_result ( alert_type
> > > > char(10), alert_color char(10),
> > > >
> > > > alert_object_type char(10), tab_name char(100), alert_msg char(100)
> > > > );SELECT
> > > > --+ORDERED,INDEX(a,systabs_pnix),INDEX(b,sysptnhdridx)
> > > >
> > > > a.dbsname,
> > > >
> > > > a.owner,
> > > >
> > > > a.tabname,
> > > >
> > > > b.npused
> > > > FROM sysmaster:systabnames a,
> > > >
> > > > sysmaster:sysptnhdr b
> > > > WHERE a.partnum = b.partnum
> > > >
> > > > INTO TEMP __t_tab_max_pages_1;> To: ids@iiug.org
> > > > > From: lsorensen25@msn.com
> > > > > Subject: RE: No more pages in a tablespace and monitori.... [26953]
> > > > > Date: Thu, 3 May 2012 15:58:37 -0400
> > > > >
> > > > > It's installed in the sysadmin database. That was the database used
> > in
> > > > the
> > > > > sample I was given.
> > > > > > To: ids@iiug.org
> > > > > > From: art.kagel@gmail.com
> > > > > > Subject: Re: No more pages in a tablespace and monitori.... [26952]
> > > > > > Date: Thu, 3 May 2012 15:55:21 -0400
> > > > > >
> > > > > > Is the check_table_pages function installed in sysadmin or in
> > > > sysmaster?
> > > > > > If the function was installed in sysmaster then to run it from
> > sysadmin
> > > > you
> > > > > > will have to qualify the name, so:
> > > > > >
> > > > > > dbaccess sysadmin - <<!
> > > > > >
> > > > > > EXECUTE FUNCTION sysmaster:check_table_pages(..);
> > > > > > !
> > > > > >
> > > > > > 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 Thu, May 3, 2012 at 1:34 PM, Larry Sorensen <
> > lsorensen25@msn.com>
> > > > wrote:
> > > > > >
> > > > > > > OK. Previously, I asked a related question about creating a SPL
> > > > Function
> > > > > to
> > > > > > > monitor page usage, so that I would not run out of pages again
> > > > > > > unexpectedly.
> > > > > > > I create a Function that does what I want it to do in the
> > sysadmin
> > > > > > > database.
> > > > > > > I want to be able to call this from dbaccess or from the UNIX OS
> > > > using
> > > > > > > dbaccess....
> > > > > > >
> > > > > > > dbaccess <<!
> > > > > > > DATABASE sysadmin;
> > > > > > >
> > > > > > > EXECUTE FUNCTION.......
> > > > > > > !
> > > > > > >
> > > > > > > First I tried to run it directly from dbaccess and received the
> > > > following
> > > > > > > message:
> > > > > > > 674: Routine (check_table_pages) can not be resolved
> > > > > > >
> > > > > > > Is what I am trying to do possible; and if so, what am I doing
> > wrong?
> > > > > > >
> > > > > > > Thank you in advance.
> > > > > > >
> > > > > > > -----Original Message-----
> > > > > > > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On
> > Behalf
> > > > Of
> > > > > > > Marcus
> > > > > > > Haarmann
> > > > > > > Sent: Monday, April 23, 2012 1:07 PM
> > > > > > > To: ids@iiug.org
> > > > > > > Subject: Re: No more pages in a tablespace [26814]
> > > > > > >
> > > > > > > Hi Larry,
> > > > > > >
> > > > > > > you might have moved the whole table in another tablespace with
> > alter
> > > > > > > fragment ... init in new_dbs (where new_dbs has a bigger page
> > size).
> > > > > > > This would result in a non-fragmented table (while your proposal
> > > > would
> > > > > > > split
> > > > > > > the table in multiple dbspaces).
> > > > > > >
> > > > > > > The other way (with re-creation) would be a create table with a
> > > > different
> > > > > > > name but same structure in a new dbs, insert all the data (lock
> > whole
> > > > > table
> > > > > > > before inserting, take care of transaction logs, maybe copy the
> > > > table in
> > > > > > > smaller transactions), using "insert into xxxx select * from
> > yyyy",
> > > > then
> > > > > > > rename the tables (check foreign keys and rights if applicable).
> > > > > > > This can be done without unloading/reloading data (more time
> > > > consuming).
> > > > > > > But
> > > > > > > the init fragment approach should lead to almost the same result.
> > > > > > > The copying of data is faster if you can modify the tables to be
> > no
> > > > > logging
> > > > > > > while copying (alter table mode raw, copy data, alter table mode
> > > > > standard),
> > > > > > > works only if no indexes are present.
> > > > > > > Afterwards, you have to take a full backup.
> > > > > > >
> > > > > > > Marcus
> > > > > > >
> > > > > > > ----- Ursprüngliche Mail -----
> > > > > > >
> > > > > > > Von: "LARRY SORENSEN" <lsorensen25@msn.com>
> > > > > > > An: ids@iiug.org
> > > > > > > Gesendet: Montag, 23. April 2012 18:04:18
> > > > > > > Betreff: RE: No more pages in a tablespace [26813]
> > > > > > >
> > > > > > > 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.
> > > > > > >
> > > > > > >
> > > > > > >
> > > > >
> > > >
> >
> > ****************************************************************************
> > > > > > > ***
> > > > > > > Forum Note: Use "Reply" to post a response in the discussion
> > forum.
> > > > > > >
> > > > > > >
> > > > > > >
> > > > > > >
> > > > > >
> > > > >
> > > >
> > > >
> > >
> >
> >
> *******************************************************************************
> > > > > > > Forum Note: Use "Reply" to post a response in the discussion
> > forum.
> > > > > > >
> > > > > > >
> > > > > >
> > > > > > --14dae9340c019bbdf604bf272e87
> > > > > >
> > > > > >
> > > > > >
> > > > >
> > > >
> > > >
> > >
> >
> >
> *******************************************************************************
> > > > > > 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.
> > > >
> > > >
> > >
> > > --e89a8f3ba9598dbcdf04bf2767cc
> > >
> > >
> > >
> >
> >
> *******************************************************************************
> > > Forum Note: Use "Reply" to post a response in the discussion forum.
> > >
> >
> >
> >
> >
> *******************************************************************************
> > Forum Note: Use "Reply" to post a response in the discussion forum.
> >
> >
>
> --14dae9340d59dff48104bf2789aa
>
>
> *******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
Messages In This Thread
IDS Forum is maintained by Administrator with WebBBS 5.12.
|
|
