dbspace utilisation in Informix 5x
User on OnLine 5.11 needed to know which dbspace/chunk each table lives in, since 5.x has no sysmaster by default. Answers: query each database's systables (select tabname, partnum, hex(partnum)) — the leading hex digits of partnum give the chunk/dbspace number matching tbstat -d; alternatively use tbcheck -pe (which locks the database during its run, a concern on a busy production system) or massage tbstat/tbcheck output. Jonathan Leffler noted a rudimentary sysmaster.sql in $INFORMIXDIR/etc. Art Kagel added that version 5 has no fragmentation; in later versions fragmented tables show partnum 0 in systables with rows in sysfragments, and fragmented indexes must be found via sysmaster's systabnames. The poster confirmed the systables query gave what he needed.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Hi We are using Informix Online 5.11. We need to purge data on a regular basis. How to find in which dbspace/s a table resides because here I donot find sysmaster db here and tracking dbspace utilisation by table is not possible. Can anyone help ? dp
You need to query systables for each database. I believe 'partnum' is the
field where the 1st 3 hex chars (?) give the dbspace # that matches "tbstat
-d". Haven't worked with Online is a while but it works for IDS also.
select tabname, partnum , hex(partnum)
from systables
order by partnum;
Bob
----- Original Message -----
From: "PRABAL DAS" <d.prabal@gmail.com>
To: ids@iiug.org
Sent: Friday, September 4, 2009 5:44:26 AM GMT -05:00 US/Canada Eastern
Subject: dbspace utilisation in Informix 5x [16863]
Hi
We are using Informix Online 5.11. We need to purge data on a regular basis.
How to find in which dbspace/s a table resides because here I donot find
sysmaster db here and tracking dbspace utilisation by table is not possible.
Can anyone help ?
dp
*******************************************************************************
Forum Note: Use "Reply" to post a response in the discussion forum.
On Fri, Sep 4, 2009 at 02:44, PRABAL DAS<d.prabal@gmail.com> wrote:
> We are using Informix Online 5.11. We need to purge data on a regular basis.
> How to find in which dbspace/s a table resides because here I donot find
> sysmaster db here and tracking dbspace utilisation by table is not possible.
>
> Can anyone help ?
The sysmaster database was not created as standard with OnLIne 5.x.
There was, IIRC, a rudimentary version of available if user informix
ran 'dbaccess - $INFORMIXDIR/etc/sysmaster.sql'. Don't do this on a
production system until you're satisfied it works safely enough for
you. If the tables you need are in the sysmaster database, then that
is great; if not, the sysmaster approach is not going to work.
The alternative is to work with the output from tbstat, and maybe
tbcheck too, and massage that to yield the necessary information.
--
Jonathan Leffler #include <disclaimer.h>
Email: jleffler@earthlink.net, jleffler@us.ibm.com
Guardian of DBD::Informix v2008.0513 -- http://dbi.perl.org/
"Blessed are we who can laugh at ourselves, for we shall never cease
to be amused."
NB: Please do not use this email for correspondence.
I don't necessarily read it every week, even.
Mike Ditka - "If God had wanted man to play soccer, he wouldn't have
given us arms." -
http://www.brainyquote.com/quotes/authors/m/mike_ditka.html
This might be useful - from my old version 5 manual.
SELECT tabname, partnum, HEX(partnum) hex_tblspace_name FROM systables;
Although I just ran this in version 11 it might be helpful:
tabname iosrc
partnum 4196596
hex_tblspace_name 0x004008F4
I believe the "0x004" will be the chunk number listed in "onstat -d"; "tbstat
-d" for version 5x
tbcheck -pe Art Art S. Kagel Oninit (www.oninit.com) IIUG Board of Directors (art@iiug.org) Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Oninit, the IIUG, nor any other organization with which I am associated either explicitly or implicitly. 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, Sep 4, 2009 at 5:44 AM, PRABAL DAS <d.prabal@gmail.com> wrote: > Hi > > We are using Informix Online 5.11. We need to purge data on a regular > basis. > How to find in which dbspace/s a table resides because here I donot find > sysmaster db here and tracking dbspace utilisation by table is not > possible. > > Can anyone help ? > > dp > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --001517448ad222841e0472c483bd
Thanks a lot to all who have replied.
Query from systables is giving the desired output.
But a little confused.... doesn't oncheck -pe lock database? Because I have to
run it in production in a very busy database
The manual is unclear as to how the engine goes about "checking/verifing" the chunk free list and corresponding free space and tblspace extents. If errors are encountered and the tables are updated then there will be some row locking on some of the system tables.
Yes, tbcheck will lock the database completely during its run.
Art
Art S. Kagel
Oninit (www.oninit.com)
IIUG Board of Directors (art@iiug.org)
Disclaimer: Please keep in mind that my own opinions are my own opinions and
do not reflect on my employer, Oninit, the IIUG, nor any other organization
with which I am associated either explicitly or implicitly. 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, Sep 8, 2009 at 10:00 AM, PRABAL DAS <d.prabal@gmail.com> wrote:
> Thanks a lot to all who have replied.
> Query from systables is giving the desired output.
>
> But a little confused.... doesn't oncheck -pe lock database? Because I have
> to
> run it in production in a very busy database
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--0015174757a4c80982047312e55b
Hi Bob, Thanks for your advice. But in case the table is fragmented among multiple dbspaces in v7 or v5 (dont know whether fragmentation is supported in v5), how can we identify that? Prabal
OnLine 5 does not support fragmentation. In other versions that do, a fragmented table will have a zero for the partnum in systables and will have several records in sysfragments in the database's catalog. You cannot detect fragmented indexes in the database catalog but must look in sysmaster. In sysmaster, the table or index will have multiple records in systabnames if it is fragmented with each having the same lockid (normally the partnum of the first fragment) but different partnums. Art Art S. Kagel Oninit (www.oninit.com) IIUG Board of Directors (art@iiug.org) Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Oninit, the IIUG, nor any other organization with which I am associated either explicitly or implicitly. 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, Sep 11, 2009 at 8:05 AM, PRABAL DAS <d.prabal@gmail.com> wrote: > Hi Bob, > > Thanks for your advice. > > But in case the table is fragmented among multiple dbspaces in v7 or v5 > (dont > know whether fragmentation is supported in v5), how can we identify that? > > Prabal > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --001517479188eb652f04734c47b8
Thanks for your advice Prabal
Hi Art Thanks for your advice Prabal
Related threads
- IDS 10 table-level restore
- Informix Development Webinar December 11, 2007
- ontape -p/r with changed ROOTPATH
- Migrate from HP PA-RISC to HP ITANIUM by ontape