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 ------------------------------

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 ------------------------------

Gábor: You will find the partition/fragment name in sysfragments.partition. Witness: > select fragtype, partition, evalpos from sysfragments where tabid = 344;fragtype T partition p1 evalpos 0 fragtype T partition p2 evalpos 1 fragtype T partition p3 evalpos 2 So, this table has three partitions named 'p1', 'p2', & 'p3'. Mike: The systabnames rows for a partitioned table will all have the same tabname, that of the table as a whole. The partition names are not stored in sysmaster at all, you could link back to sysfragments in the database joining on partnum like this though: > select partition from art:sysfragments sf, systabnames st > where st.partnum = sf.partn > and st.tabname = 'extents_2';partition p1 partition p2 partition p3 3 row(s) retrieved. Art ------------------------------ Art S. Kagel, President and Principal Consultant ASK Database Management Corp. www.askdbmgt.com ------------------------------

AFAIR the partnum on systables is zero for fragmented tables ... On 4/20/2026 11:10 AM, Art Kagel via IBM Community wrote: 0100019daba8b394-c68eb08b-d602-4461-a867-e6d89eeb6292-000000@email.amazonses.com"> Gábor: You will find the partition/fragment name in sysfragments.partition. Witness: > select fragtype, partition, evalpos from sysfragments...

Agreed. I was showing if the table was fragmented or not. sysfragments has the pertinent info, including the partition name. The tricky thing is to have one query against sysmaster that joins to sysfragments in the individual databases without specifically naming the database. If you know the name of the database, then a join to that database specifically and the sysfragments table is the best approach, and the way to get that partition name. ------------------------------ Mike Walker xDB Systems, Inc www.xdbsystems.com ------------------------------

Paul: The partnum stored in systables is indeed zero for partitioned tables, however, each fragment's partnum is stored in the partitions sysfragments record in the partn column and each of the table's sysmaster:systabnames records will contain the partnum of that associated partition. Art ------------------------------ Art S. Kagel, President and Principal Consultant ASK Database Management Corp. www.askdbmgt.com ------------------------------

Hi Gabor & Family. Many years ago I posted a few items on the IIUG software repository, including a script names fragments.sh. I think I enhanced it without posting the enhancements and I looked at it again now. I realized it has a few dependencies, including two Perl scripts and a Perl package, chained with Jonathan Leffler's DBD::Informix Perl package. An older, less versatile version, named partitions.sh, may still be in the GitHub site that replaced the repository. Note to Art: I still miss that repository! I never got used to the GitHub version and I suspect I'm not alone, and that's why it has fallen to disuse. I'm still writing good (IMHO) stuff and would like to share it. ------------------------------ +-----------------------------------------------------------+ | I am pleased to report that I had no problems today. | | I had only issues, opportunities, challenges and valuable | | learning experiences. | +------------------------------------------ Jacob S --------+ ------------------------------

Jacob, et al: The IIUG Board is working on an upgrade to the Software Repository. Stay tuned. Art ------------------------------ Art S. Kagel, President and Principal Consultant ASK Database Management Corp. www.askdbmgt.com ------------------------------

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 ------------------------------

If you insist on that additional identifier being a partition name as it appears in the table schema's fragmentation clause, there's not way around sysfragments and running the query per database, I guess. But even this had to account for non-fragmented tables as well as for fragmented ones not using the partition syntax. But why not simply introducing an additional 'partition_' || systabnames.partnum field next to tabname? ------------------------------ Andreas Legner Informix Dev HCL Software ------------------------------

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. ------------------------------ Mike Walker xDB Systems, Inc www.xdbsystems.com ------------------------------