Fragmentation Question

Hi All, I am relatively new to this, so please bear with me. I am seeking clarification on the handling of fragments created with the alter fragment... We are running INFORMIX-OnLine Version 7.23.UC11 on RS/6000 AIX 4.2.3 My understanding from the manuals is that when a table has been fragmented the partnum in systables is 0 and the partnum used is taken from sysfragments. Hence if I do: select partnum, tabid, tabname, tabtype from systables where partnum = 0 and tabid > 99 and tabtype = 'T'; That will list the tables which have been fragmented. If this returns nothing, then there are no fragmented tables in the current database (ignoring indexes for the moment). Is this the case, and if so is it ALWAYS true, or are there conditions when it is not true? On a related issue, I have an inherited shell script which I do not understand. It is designed to work out appropriate next extent sizes, taking fragmentation into account. The section (as far as I can tell) which determines the tables which have been fragmented is: ... select prof2.dbsname, prof2.tabname tabname, hdr.npused npused , hdr.nextns , "F" where_flag from sysmaster:sysptprof prof , sysmaster:sysptnhdr hdr, sysmaster:sysptprof prof2 where hdr.partnum = prof.partnum and hdr.partnum != hdr.lockid and hdr.lockid = prof2.partnum ... 1) I can see sysptprof described in the SMI section of the DS Admin Guide but no mention of sysptnhdr. There is a section that says "Many other tables in the sysmaster are part of SMI but they are not documented" Is sysptnhdr a non documented, or have I just not looked in the right place yet? 2) With regards to sysptprof the same manual says "Profile information for a table is available only when the table is open. When the last user ... closes it ... any profile statistics are lost" Yet the data in sysptprof seems to remain intact?? 3) I do not understand the reasoning behind how the above 'where' clause arrives at details of fragmented tables, nor why the output from the above select does not seem to tally with the details in sysfragment in any/all the databases at all. Any clarification, or pointers as to where to look much appreciated. Thanks in advance, Regards, Dale. __________________________________________________________ Dale Spence ADP Clearing (UK), Camberley, UK ______________________________________________________________________________ This message has been checked for all known viruses by Star Internet delivered through the MessageLabs Virus Control Centre. For further information visit- http://www.star.net.uk/stats.asp

SPENCE Dale wrote: > > Hi All, > > I am relatively new to this, so please bear with me. > > I am seeking clarification on the handling of fragments created with > the alter fragment... We are running INFORMIX-OnLine Version > 7.23.UC11 on RS/6000 AIX 4.2.3 Consider upgrading to 7.31 or 9.20. > My understanding from the manuals is that when a table has been > fragmented the partnum in systables is 0 and the partnum used is > taken from sysfragments. > > Hence if I do: > > select partnum, tabid, tabname, tabtype from systables > where partnum = 0 > and tabid > 99 > and tabtype = 'T';> > That will list the tables which have been fragmented. If this returns > nothing, then there are no fragmented tables in the current database > (ignoring indexes for the moment). Is this the case, and if so is it > ALWAYS true, or are there conditions when it is not true? No but it does not take fragmented or detached indexes into consideration. > On a related issue, I have an inherited shell script which I do not > understand. It is designed to work out appropriate next extent > sizes, taking fragmentation into account. The section (as far as I > can tell) which determines the tables which have been fragmented > is: > ... > select > prof2.dbsname, > prof2.tabname tabname, > hdr.npused npused , > hdr.nextns , > "F" where_flag > > from sysmaster:sysptprof prof , Better to use systabnames instead, sysptprof is a VIEW into a join of that table with sysptntab which reports access stats for open tables. Systabnames has all the info you are selecting from this VIEW. > sysmaster:sysptnhdr hdr, > sysmaster:sysptprof prof2 > > where hdr.partnum = prof.partnum Join > and hdr.partnum != hdr.lockid Look only at FRAGMENTS (where partnum != lockid) > and hdr.lockid = prof2.partnum Join > ... > > 1) I can see sysptprof described in the SMI section of the DS > Admin Guide but no mention of sysptnhdr. There is a section that > says "Many other tables in the sysmaster are part of SMI but they > are not documented" Is sysptnhdr a non documented, or have I just > not looked in the right place yet? Not formally documented but the entire SMI database is documented to some extent in the SQL file that creates it ($INFORMIXDIR/etc/sysmaster.sql) in the comments there. > 2) With regards to sysptprof the same manual says "Profile > information for a table is available only when the table is open. > When the last user ... closes it ... any profile statistics are lost" > Yet the data in sysptprof seems to remain intact?? Only because the columns you are selecting are from systabnames whose rows are static. > 3) I do not understand the reasoning behind how the above 'where' > clause arrives at details of fragmented tables, nor why the output > from the above select does not seem to tally with the details in > sysfragment in any/all the databases at all. The lockid of a table is the partnum of the base table. For a fragmented table it is the same for all fragments and so is different from each fragment's partnum. For non-fragmented tables partnum == lockid. > Any clarification, or pointers as to where to look much appreciated. See above. Art S. Kagel