 |
IDS Forum
RE: No more pages in a tablespace and monitori....
Posted By: LARRY SORENSEN
Date: Thursday, 3 May 2012, at 4:16 p.m.
In Response To: Re: No more pages in a tablespace and monitori.... (Art Kagel)
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.
>
Messages In This Thread
IDS Forum is maintained by Administrator with WebBBS 5.12.
|
|
