That is correct, if you want the partition name and/or the fragment expression. That info is not in sysmaster but only in the sysfragments table in each individual database.
Original Message:
Sent: Fri April 24, 2026 06:29 AM
From: Gábor Fekete
Subject: Table /fragment size monitoring
Hi Mike
Thanks for answer.
This mean I have to write a unique monitoring script for every customers each databases. Or I have to write a dynamic script which can dynamically querying the databases about a fragment information.
------------------------------
Gábor Fekete
Software Engineer
Capital Systems
Budapest
Original Message:
Sent: Mon April 20, 2026 09:37 AM
From: Mike Walker
Subject: Table /fragment size monitoring
If you want to a sysmaster query only, then I don't know of another table to join to, but in your query you will see the same table listed multiple times when that table is fragmented. You can do a subquery or use a derived table (or a temp table) to see how many times the table name exists in systabnames, e.g.
.
.
ti_nkeys num_keys, --0 -táblák, 1 - indexex
fragcount.frags
from systabnames, systabinfo,
((select dbsname, tabname, count(*) frags
from systabnames
group by 1,2)) as fragcount
where systabinfo.ti_partnum = systabnames.partnum and
systabnames.dbsname not in ( "sysmaster", "sysuser", "sysutils", "sysadmin", "SORTTEMP" )
and ( systabnames.dbsname = '$dbname' or '$dbname'= 'all' )
and systabnames.tabname not like ' %'
and dbinfo("DBSPACE", systabinfo.ti_partnum) not like 'temp%'
and systabnames.dbsname = fragcount.dbsname
and systabnames.tabname = fragcount.tabname
.
.
If there are indexes with the same name as tables then this will give the wrong result, so you will need to add extra criteria if that is the case.
If you are looking at a set of tables each week, then I would join to the sysfragments table in the specific database (not sysmaster). That's much cleaner, and you can see the fragment expression.
------------------------------
Mike Walker
xDB Systems, Inc
www.xdbsystems.com
Original Message:
Sent: Mon April 20, 2026 08:49 AM
From: Gábor Fekete
Subject: Table /fragment size monitoring
Hi
I have a monitoring sql for a tables in a database. I want to add idenficator or name for a table fragment if a table is fragmented. We have a fragmantation where the table using mor dbspace and we have fragmantation by years in a same dbspace.
I would like to compare the size data of the fragments on a weekly basis.
Which table should I join to extract the missing information?
select
systabnames.dbsname database,
systabnames.tabname tabname,
ti_nextns num_extents,
replace(round(ti_pagesize * ti_nptotal/1024/1024,2), '.',',') size_in_mb,
ti_pagesize page_size,
ti_nptotal pages_total,
ti_npused pages_used,
ti_npdata pages_data,
(ti_nptotal - ti_npused ) pages_unallocated,
(ti_nptotal - ti_npdata ) pages_free,
ti_nrows ::varchar(20) num_rows,
case
when ( (ti_pagesize +4) -24) < ti_rowsize then "Row larger then pagesize"
else "Row smaller the pagesize"
end rowfit,
case
when ti_rowsize > 0 then
trunc ((ti_pagesize -24) / ti_rowsize )
else 0
end rows_per_page,
case
when ti_rowsize > 0 then
( ( trunc ((ti_pagesize -24) / ti_rowsize ) ) * (ti_nptotal - ti_npused ) )
else 0
end unallocated_for_free_rows,
case
when ti_rowsize > 0 then
( ( trunc ((ti_pagesize -24) / ti_rowsize ) ) * (ti_nptotal - ti_npdata ) )
else 0
end free_rows,
DBINFO ('utc_to_datetime', ti_created ) create_date,
( dbinfo('dbspace', ti_partnum )) dbspace,
ti_rowsize row_size,
ti_ncols num_columns,
ti_nkeys num_keys --0 -táblák, 1 - indexex
from systabnames
join systabinfo on systabinfo.ti_partnum = systabnames.partnum
where systabnames.dbsname not in ( "sysmaster", "sysuser", "sysutils", "sysadmin", "SORTTEMP" )
and ( systabnames.dbsname = '$dbname' or '$dbname'= 'all' )
and systabnames.tabname not like ' %'
and dbinfo("DBSPACE", systabinfo.ti_partnum) not like 'temp%'
order by systabnames.dbsname, num_keys, pages_total desc;
------------------------------
Gábor Fekete
Software Engineer
Capital Systems
Budapest
------------------------------