sysmaster query help

Posted in 2009

Floyd Wellershaus — Wed, 11 Nov 2009 10:34:31 -0600 (CST) — source: Usenet: comp.databases.informix

Does anyone have a query that will provide the Number of pages allocated for each partition in the database, along with the tablename/indexname that the partition belongs to, and the partiton name itself ? Thanks. Floyd

↪ replying to Floyd Wellershaus

Fernando Nunes — Wed, 11 Nov 2009 22:55:30 +0000 — source: Usenet: comp.databases.informix

Floyd Wellershaus wrote: > Does anyone have a query that will provide the Number of pages allocated > for each partition in the database, along with the tablename/indexname > that the partition belongs to, and the partiton name itself ? > > Thanks. > Floyd Check systabnames and sysptnhdr. They can be connected by partnum. And you can connect all the partitions of a table because they all have the same lockid. The partition where partnum=lockid defines the table name... I would have to start an engine, but maybe something like: SELECT ( SELECT SUM(p1.nptotal) FROM sysptnhdr p1 WHERE p1.lockid = p2.lockid ), t.tabname FROM systabnames t, sysptnhdr p2 WHERE p2.partnum = t.partnum AND p2.lockid = p2.partnum Hmmm... this would mix up index partitions and data partitions in the same SUM, for each table... If you need to split data from pages it could be harder... Of course (and it's probably better) you can start with the database catalog (systables, sysfragments, sysindices) and then join with the two tables above (or possibly only with sysptnhdr)... Regards.