Because of the large size of the table we recently chose to fragment
by round robin into 8 dbspaces. After inserting lot of rows we ran our
delete process so that we free up the space. But it appears just
delete alone is not freeing up the space/pages. I looked at oncheck -
pT and pages are not being freed by delete alone. I read in the manual
that the best way is to run ALTER FRAGMENT ON TABLE T INIT FRAGMENT BY
ROUND ROBIN clause. So my question is:


1. If we don't run alter fragment would Informix know that these pages
are available and will use them anyway ?
2. In real time scenario we could insert 40+ million rows which are of
huge size. And as part of our process we delete them from the table
after certain timeframe. Now if we have to run alter fragment after
every delete then in production we are going to face a) downtime
because I think we need to disconnect any connections to database
before running alter fragment b) For 40+ M rows it's going to be
awfully long to do alter fragment.
3. How long does it take to execute this command.

Is there any better strategy ? How can we make it better.

Snippet from oncheck -pT
---------
Pagesize (k) 8
First extent size 1249999
Next extent size 249999
Number of pages allocated 10407781
Number of pages used 10407781
Number of data pages 55450
Number of rows 3380349
---------

You forgot to tell us your version.

1. Yes, the database has free pages which it will use for later
inserts.
2. If you needed this space for the table once you will probably
need it again so do not do anything. If you are adding rows I do not
understand why you would want to reclaim the space which the table is
just going to use again.
3. It depends upon how fast your server and disk are and how busy
they are at the time.

Once correctly sized we never touch tables again. It is the number of
extents that you have not shown that is more important.

MW


-----Original Message-----
From: informix-list-bounces@iiug.org
[mailto:informix-list-bounces@iiug.org] On Behalf Of
mohitanchlia@gmail.com
Sent: Thursday, 19 July 2007 1:24 p.m.
To: informix-list@iiug.org
Subject: Fragmented table, best way to free pages in DBspace

Because of the large size of the table we recently chose to fragment
by round robin into 8 dbspaces. After inserting lot of rows we ran our
delete process so that we free up the space. But it appears just
delete alone is not freeing up the space/pages. I looked at oncheck -
pT and pages are not being freed by delete alone. I read in the manual
that the best way is to run ALTER FRAGMENT ON TABLE T INIT FRAGMENT BY
ROUND ROBIN clause. So my question is:


1. If we don't run alter fragment would Informix know that these pages
are available and will use them anyway ?
2. In real time scenario we could insert 40+ million rows which are of
huge size. And as part of our process we delete them from the table
after certain timeframe. Now if we have to run alter fragment after
every delete then in production we are going to face a) downtime
because I think we need to disconnect any connections to database
before running alter fragment b) For 40+ M rows it's going to be
awfully long to do alter fragment.
3. How long does it take to execute this command.

Is there any better strategy ? How can we make it better.

Snippet from oncheck -pT
---------
Pagesize (k) 8
First extent size 1249999
Next extent size 249999
Number of pages allocated 10407781
Number of pages used 10407781
Number of data pages 55450
Number of rows 3380349
---------

_______________________________________________
Informix-list mailing list
Informix-list@iiug.org
http://www.iiug.org/mailman/listinfo/informix-list

On Jul 18, 9:24 pm, mohitanch...@gmail.com wrote:
> Because of the large size of the table we recently chose to fragment
> by round robin into 8 dbspaces. After inserting lot of rows we ran our
> delete process so that we free up the space. But it appears just
> delete alone is not freeing up the space/pages. I looked at oncheck -
> pT and pages are not being freed by delete alone. I read in the manual
> that the best way is to run ALTER FRAGMENT ON TABLE T INIT FRAGMENT BY
> ROUND ROBIN clause. So my question is:
>
> 1. If we don't run alter fragment would Informix know that these pages
> are available and will use them anyway ?
> 2. In real time scenario we could insert 40+ million rows which are of
> huge size. And as part of our process we delete them from the table
> after certain timeframe. Now if we have to run alter fragment after
> every delete then in production we are going to face a) downtime
> because I think we need to disconnect any connections to database
> before running alter fragment b) For 40+ M rows it's going to be
> awfully long to do alter fragment.
> 3. How long does it take to execute this command.
>
> Is there any better strategy ? How can we make it better.
>
> Snippet from oncheck -pT
> ---------
> Pagesize (k) 8
> First extent size 1249999
> Next extent size 249999
> Number of pages allocated 10407781
> Number of pages used 10407781
> Number of data pages 55450
> Number of rows 3380349
> ---------

You're working at this too hard! IDS will reuse slots and pages from
deleted rows immediately for new data within the same table/fragment.
You would only have to release the enwly unused pages to the free pool
if you need to use that space for a DIFFERENT table or fragment.
Otherwise, just: insert, delete, insert, delete......

Art S. Kagel

On Jul 19, 10:05 pm, "Jack Parker" <jack.park...@verizon.net> wrote:
> I cannot agree with you on this one Art.
>
> A 40 million row delete is a multi-hour process.
>
> Granted, the space will be available for reuse quickly if the insert/delete
> scheme is date based. If there is some difference in the two schemes, then
> deleted row space may not necessarily free up, the page in question would
> have to drop below half-deleted, then go through a compression, then the
> space would be available again. But I digress.
>
> Certainly an alter fragment detach is not going to be happy with others
> holding even a shared lock on the table, we tried it with a lock mode wait,
> until we discovered that there was a process that started, opened a cursor
> to the table and then stayed there forever. We had to stop that process, do
> the purge and then start it back up again.
>
> Other things we ran into:
> - prepared statements have to be re-prepared - the table layout has
> changed.
> - detach/drop table/attach will not be happy with FK/PK constraints.
> - indexes (to detach cleanly) must have the same frqagmentation
> strategy - i.e. date.
> - ROWIDs cause an underlying index which does not have the date in it and
> will take a long time to rebuild.
>
> All of those are time sinks to work around or rebuild. Without those, a
> detach/drop table/attach takes about 1 second on a rinky-dink machine. The
> number of rows is immaterial to the length of time necessary to perform the
> operation.
>
> If our friend is a 24x7 shop and cannot go down momentarily occasionally,
> then yes he is stuck with a delete scenario. He could speed that up with a
> nibble strategy:
>
> build a temp table of identifying keys (empty)
> Stuff 10K keys to be deleted from the target table into the temp table
> delete from target table where key in (select key from temp_table)
> Rinse and repeat until there are no more.
>
> My .02
>
> j.
>
> -----Original Message-----
> From: informix-list-boun...@iiug.org
>
> [mailto:informix-list-boun...@iiug.org]On Behalf Of Art S. Kagel
> Sent: Thursday, July 19, 2007 7:50 PM
> To: informix-l...@iiug.org
> Subject: Re: Fragmented table, best way to free pages in DBspace
>
> On Jul 18, 9:24 pm, mohitanch...@gmail.com wrote:
> > Because of the large size of the table we recently chose to fragment
> > by round robin into 8 dbspaces. After inserting lot of rows we ran our
> > delete process so that we free up the space. But it appears just
> > delete alone is not freeing up the space/pages. I looked at oncheck -
> > pT and pages are not being freed by delete alone. I read in the manual
> > that the best way is to run ALTER FRAGMENT ON TABLE T INIT FRAGMENT BY
> > ROUND ROBIN clause. So my question is:
>
> > 1. If we don't run alter fragment would Informix know that these pages
> > are available and will use them anyway ?
> > 2. In real time scenario we could insert 40+ million rows which are of
> > huge size. And as part of our process we delete them from the table
> > after certain timeframe. Now if we have to run alter fragment after
> > every delete then in production we are going to face a) downtime
> > because I think we need to disconnect any connections to database
> > before running alter fragment b) For 40+ M rows it's going to be
> > awfully long to do alter fragment.
> > 3. How long does it take to execute this command.
>
> > Is there any better strategy ? How can we make it better.
>
> > Snippet from oncheck -pT
> > ---------
> > Pagesize (k) 8
> > First extent size 1249999
> > Next extent size 249999
> > Number of pages allocated 10407781
> > Number of pages used 10407781
> > Number of data pages 55450
> > Number of rows 3380349
> > ---------
>
> You're working at this too hard! IDS will reuse slots and pages from
> deleted rows immediately for new data within the same table/fragment.
> You would only have to release the enwly unused pages to the free pool
> if you need to use that space for a DIFFERENT table or fragment.
> Otherwise, just: insert, delete, insert, delete......


Jack, he can't use fragment DETACH, he's using ROUND ROBIN
fragmentation, not fragment by expression to isolate his older data.
If her were, I'd agree. Meanwhile, there's no argument here. He is
already deleting the rows he needs to delete (yes different
fragmentation would make that easier) he just doesn't understand how
IDS works and how it deals with deleted row space and whether or not
it's reused. I'm confident that I answered the question. Aside, your
implied suggestion to refragment by expression is a VERY good one, and
I didn't look beyond the question at hand to see that it should be
made.

Art S. Kagel

On Jul 20, 1:10 pm, "Jack Parker" <jack.park...@verizon.net> wrote:
> True, I neglected to do more than imply that Round Robin would not work with
> this.
>
> j.
>
>
>
> -----Original Message-----
> From: informix-list-boun...@iiug.org
>
> [mailto:informix-list-boun...@iiug.org]On Behalf Of Art S. Kagel
> Sent: Friday, July 20, 2007 2:40 PM
> To: informix-l...@iiug.org
> Subject: Re: Fragmented table, best way to free pages in DBspace
>
> On Jul 19, 10:05 pm, "Jack Parker" <jack.park...@verizon.net> wrote:
> > I cannot agree with you on this one Art.
>
> > A 40 million row delete is a multi-hour process.
>
> > Granted, the space will be available for reuse quickly if the
> insert/delete
> > scheme is date based. If there is some difference in the two schemes,
> then
> > deleted row space may not necessarily free up, the page in question would
> > have to drop below half-deleted, then go through a compression, then the
> > space would be available again. But I digress.
>
> > Certainly an alterfragmentdetach is not going to be happy with others
> > holding even a shared lock on the table, we tried it with a lock mode
> wait,
> > until we discovered that there was a process that started, opened a cursor
> > to the table and then stayed there forever. We had to stop that process,
> do
> > the purge and then start it back up again.
>
> > Other things we ran into:
> > - prepared statements have to be re-prepared - the table layout has
> > changed.
> > - detach/drop table/attach will not be happy with FK/PK constraints.
> > - indexes (to detach cleanly) must have the same frqagmentation
> > strategy - i.e. date.
> > - ROWIDs cause an underlying index which does not have the date in it
> and
> > will take a long time to rebuild.
>
> > All of those are time sinks to work around or rebuild. Without those, a
> > detach/drop table/attach takes about 1 second on a rinky-dink machine.
> The
> > number of rows is immaterial to the length of time necessary to perform
> the
> > operation.
>
> > If our friend is a 24x7 shop and cannot go down momentarily occasionally,
> > then yes he is stuck with a delete scenario. He could speed that up with
> a
> > nibble strategy:
>
> > build a temp table of identifying keys (empty)
> > Stuff 10K keys to be deleted from the target table into the temp table
> > delete from target table where key in (select key from temp_table)
> > Rinse and repeat until there are no more.
>
> > My .02
>
> > j.
>
> > -----Original Message-----
> > From: informix-list-boun...@iiug.org
>
> > [mailto:informix-list-boun...@iiug.org]On Behalf Of Art S. Kagel
> > Sent: Thursday, July 19, 2007 7:50 PM
> > To: informix-l...@iiug.org
> > Subject: Re: Fragmented table, best way to free pages in DBspace
>
> > On Jul 18, 9:24 pm, mohitanch...@gmail.com wrote:
> > > Because of the large size of the table we recently chose tofragment
> > > by round robin into 8 dbspaces. After inserting lot of rows we ran our
> > > delete process so that we free up the space. But it appears just
> > > delete alone is not freeing up the space/pages. I looked at oncheck -
> > > pT and pages are not being freed by delete alone. I read in the manual
> > > that the best way is to run ALTERFRAGMENTON TABLE T INITFRAGMENTBY
> > > ROUND ROBIN clause. So my question is:
>
> > > 1. If we don't run alterfragmentwould Informix know that these pages
> > > are available and will use them anyway ?
> > > 2. In real time scenario we could insert 40+ million rows which are of
> > > huge size. And as part of our process we delete them from the table
> > > after certain timeframe. Now if we have to run alterfragmentafter
> > > every delete then in production we are going to face a) downtime
> > > because I think we need to disconnect any connections to database
> > > before running alterfragmentb) For 40+ M rows it's going to be
> > > awfully long to do alterfragment.
> > > 3. How long does it take to execute this command.
>
> > > Is there any better strategy ? How can we make it better.
>
> > > Snippet from oncheck -pT
> > > ---------
> > > Pagesize (k) 8
> > > First extent size 1249999
> > > Next extent size 249999
> > > Number of pages allocated 10407781
> > > Number of pages used 10407781
> > > Number of data pages 55450
> > > Number of rows 3380349
> > > ---------
>
> > You're working at this too hard! IDS will reuse slots and pages from
> > deleted rows immediately for new data within the same table/fragment.
> > You would only have to release the enwly unused pages to the free pool
> > if you need to use that space for a DIFFERENT table orfragment.
> > Otherwise, just: insert, delete, insert, delete......
>
> Jack, he can't usefragmentDETACH, he's using ROUND ROBIN
> fragmentation, notfragmentby expression to isolate his older data.
> If her were, I'd agree. Meanwhile, there's no argument here. He is
> already deleting the rows he needs to delete (yes different
> fragmentation would make that easier) he just doesn't understand how
> IDS works and how it deals with deleted row space and whether or not
> it's reused. I'm confident that I answered the question. Aside, your
> implied suggestion to refragment by expression is a VERY good one, and
> I didn't look beyond the question at hand to see that it should be
> made.
>
> Art S. Kagel
>
> _______________________________________________
> Informix-list mailing list
> Informix-l...@iiug.orghttp://www.iiug.org/mailman/listinfo/informix-list- Hide quoted text -
>
> - Show quoted text -

How can I calculate free space in tablespaces in different dbspace for
a fragmented table because once extents are allocated those are never
reported as free in syschunks table. Is following query good enough:

select
name dbspace, -- dbspace name truncated to fit
f.chknum, -- chunk number
f.extnum, -- extent number of free space
f.start, -- starting address of free space
f.leng free_pages -- length of free space
from sysdbspaces d, syschunks c, syschfree f
where d.dbsnum = c.dbsnum
and c.chknum = f.chknum
order by dbspace, chknum

On Aug 6, 3:16 pm, mohitanch...@gmail.com wrote:
> On Jul 20, 1:10 pm, "Jack Parker" <jack.park...@verizon.net> wrote:
>
>
>
> > True, I neglected to do more than imply that Round Robin would not work with
> > this.
>
> > j.
>
> > -----Original Message-----
> > From: informix-list-boun...@iiug.org
>
> > [mailto:informix-list-boun...@iiug.org]On Behalf Of Art S. Kagel
> > Sent: Friday, July 20, 2007 2:40 PM
> > To: informix-l...@iiug.org
> > Subject: Re: Fragmented table, best way to free pages in DBspace
>
> > On Jul 19, 10:05 pm, "Jack Parker" <jack.park...@verizon.net> wrote:
> > > I cannot agree with you on this one Art.
>
> > > A 40 million row delete is a multi-hour process.
>
> > > Granted, the space will be available for reuse quickly if the
> > insert/delete
> > > scheme is date based. If there is some difference in the two schemes,
> > then
> > > deleted row space may not necessarily free up, the page in question would
> > > have to drop below half-deleted, then go through a compression, then the
> > > space would be available again. But I digress.
>
> > > Certainly an alterfragmentdetach is not going to be happy with others
> > > holding even a shared lock on the table, we tried it with a lock mode
> > wait,
> > > until we discovered that there was a process that started, opened a cursor
> > > to the table and then stayed there forever. We had to stop that process,
> > do
> > > the purge and then start it back up again.
>
> > > Other things we ran into:
> > > - prepared statements have to be re-prepared - the table layout has
> > > changed.
> > > - detach/drop table/attach will not be happy with FK/PK constraints.
> > > - indexes (to detach cleanly) must have the same frqagmentation
> > > strategy - i.e. date.
> > > - ROWIDs cause an underlying index which does not have the date in it
> > and
> > > will take a long time to rebuild.
>
> > > All of those are time sinks to work around or rebuild. Without those, a
> > > detach/drop table/attach takes about 1 second on a rinky-dink machine.
> > The
> > > number of rows is immaterial to the length of time necessary to perform
> > the
> > > operation.
>
> > > If our friend is a 24x7 shop and cannot go down momentarily occasionally,
> > > then yes he is stuck with a delete scenario. He could speed that up with
> > a
> > > nibble strategy:
>
> > > build a temp table of identifying keys (empty)
> > > Stuff 10K keys to be deleted from the target table into the temp table
> > > delete from target table where key in (select key from temp_table)
> > > Rinse and repeat until there are no more.
>
> > > My .02
>
> > > j.
>
> > > -----Original Message-----
> > > From: informix-list-boun...@iiug.org
>
> > > [mailto:informix-list-boun...@iiug.org]On Behalf Of Art S. Kagel
> > > Sent: Thursday, July 19, 2007 7:50 PM
> > > To: informix-l...@iiug.org
> > > Subject: Re: Fragmented table, best way to free pages in DBspace
>
> > > On Jul 18, 9:24 pm, mohitanch...@gmail.com wrote:
> > > > Because of the large size of the table we recently chose tofragment
> > > > by round robin into 8 dbspaces. After inserting lot of rows we ran our
> > > > delete process so that we free up the space. But it appears just
> > > > delete alone is not freeing up the space/pages. I looked at oncheck -
> > > > pT and pages are not being freed by delete alone. I read in the manual
> > > > that the best way is to run ALTERFRAGMENTON TABLE T INITFRAGMENTBY
> > > > ROUND ROBIN clause. So my question is:
>
> > > > 1. If we don't run alterfragmentwould Informix know that these pages
> > > > are available and will use them anyway ?
> > > > 2. In real time scenario we could insert 40+ million rows which are of
> > > > huge size. And as part of our process we delete them from the table
> > > > after certain timeframe. Now if we have to run alterfragmentafter
> > > > every delete then in production we are going to face a) downtime
> > > > because I think we need to disconnect any connections to database
> > > > before running alterfragmentb) For 40+ M rows it's going to be
> > > > awfully long to do alterfragment.
> > > > 3. How long does it take to execute this command.
>
> > > > Is there any better strategy ? How can we make it better.
>
> > > > Snippet from oncheck -pT
> > > > ---------
> > > > Pagesize (k) 8
> > > > First extent size 1249999
> > > > Next extent size 249999
> > > > Number of pages allocated 10407781
> > > > Number of pages used 10407781
> > > > Number of data pages 55450
> > > > Number of rows 3380349
> > > > ---------
>
> > > You're working at this too hard! IDS will reuse slots and pages from
> > > deleted rows immediately for new data within the same table/fragment.
> > > You would only have to release the enwly unused pages to the free pool
> > > if you need to use that space for a DIFFERENT table orfragment.
> > > Otherwise, just: insert, delete, insert, delete......
>
> > Jack, he can't usefragmentDETACH, he's using ROUND ROBIN
> > fragmentation, notfragmentby expression to isolate his older data.
> > If her were, I'd agree. Meanwhile, there's no argument here. He is
> > already deleting the rows he needs to delete (yes different
> > fragmentation would make that easier) he just doesn't understand how
> > IDS works and how it deals with deleted row space and whether or not
> > it's reused. I'm confident that I answered the question. Aside, your
> > implied suggestion to refragment by expression is a VERY good one, and
> > I didn't look beyond the question at hand to see that it should be
> > made.
>
> > Art S. Kagel
>
> > _______________________________________________
> > Informix-list mailing list
> > Informix-l...@iiug.orghttp://www.iiug.org/mailman/listinfo/informix-list-Hide quoted text -
>
> > - Show quoted text -
>
> How can I calculate free space in tablespaces in different dbspace for
> a fragmented table because once extents are allocated those are never
> reported as free in syschunks table. Is following query good enough:
>
> select
> name dbspace, -- dbspace name truncated to fit
> f.chknum, -- chunk number
> f.extnum, -- extent number of free space
> f.start, -- starting address of free space
> f.leng free_pages -- length of free space
> from sysdbspaces d, syschunks c, syschfree f
> where d.dbsnum = c.dbsnum
> and c.chknum = f.chknum
> order by dbspace, chknum

No. That will report unused extents not assigned to any table. To
see the free space within the extents assigned to a table and its
fragments and its indexes and their fragments it's more like:

select dbsname, tabname, dbinfo( 'dbspace', sp.partnum ) as dbspace,
sum( nptotal ), sum( nptotal - npused) as npfree
from systabnames st, sysptnhdr sp
where st.partnum = sp.lockid
and dbsname = 'mydatabase' and tabname = 'mytablename'
group by 1, 2, 3
order by 1, 2, 3;

This will report free space totals within allocated extents by object
(table or index) and dbspace (ie fragment). If you want lower level
detail remove the aggregations and the group by clause.

Art S. Kagel