Re: Please Help w/ SMI Query
Mark
Dont know exactly how you would write the query for the dbcockpit
alarm, but here is how you can go about determining the number of
extents and number of chunks from SMI tables:
DATABASE yourdatabase;
SELECT a.tabname, hex(b.start)
FROM systables a, sysmaster:sysextents b
WHERE a.tabname = "yourtablename"
AND b.tabname = a.tabname
AND b.dbsname = "yourdatabase";
The output will list the table name and the starting address of each
extent. Counting the rows will give you the number of extents. The hex
of size is expressed as 0xCCCPPPPP where CCC is the chunk number. The
number of chunks is the number of unique CCC in the output.
Once you find a way to output this information from an SQL (if you do
I would appreciate knowing how you did it), it should be a relatively
simple matter to put in the WHERE clause you mentioned below to filter
out tables you really need.
HTH
Sujit
______________________________ Reply Separator _________________________________
Subject: Please Help w/ SMI Query
Author: mecory@gte.net at internet
Date: 12/22/1998 1:51 PM
On Informix 7.24.UC3 on Solaris (2.5 or 2.6), using the SMI tables, how do I
find out in how many different chunks a table resides? I've looked at the
schema for the sysmaster database, but all I can find is information about
extents, nothing to link them to the chunks in which they reside.
I have a number of tables for which I am alerted by the onprobe/dbcockpit
alarm for fragmentation, but it is because the tables are so large that they
span multiple two Gb chunks. I want to modify the alarm query so that it
not only evaluates the number of extents a table uses, but also takes into
consideration how many chunks the table spans. For example, alarm with
databasename and tablename where:
(# of fragments > (1.5 * # of chunks in which the table resides))
AND
(# of fragments > 8)
Please reply to mecory@gte.net , as I will be on holiday until 1/4/99.
Thank you VERY much for your experience, insight, and consideration! Have a
great holiday!
Mark E. Cory
Informix DBA
Portland, OR, USA