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