sysmaster:systabpagtypes

All, I am in the process of writing a script to determine the actual number of pages in use by a table. All of the tables that the script will run against have old school indexes, there is no fragmentation. I was initially going to use oncheck -pT output but found that the oncheck runs too slow for large tables. Instead I am using the following sysmaster query: select count(*) from systabpagtypes a, systabnames b where a.tp_type != 0 and a.tp_partnum = b.partnum and b.tabname = "${TABLE}" and b.dbsname = "${DBNAME}"; The query runs faster than oncheck for big tables and returns what I believe to be an accurate count of pages used in the tablespace. My dilema is this: The shop I work in are sticklers for using only documented sysmaster tables. From what I've read systabpagtypes is an undocumented sysmaster table (or view to sysptnbit) with that in mind can I reliably use it in my script? Thanks - Mike

I typically use sysptnhdr for this sort of thing. It has all of the information you want in a minimalistic format. j. >From: MIKE MAGIE <jmmagie@yahoo.com> >Date: 2008/03/03 Mon AM 10:20:20 CST >To: ids@iiug.org >Subject: sysmaster:systabpagtypes [11472] >All, > >I am in the process of writing a script to determine the actual number of >pages in use by a table. All of the tables that the script will run against >have old school indexes, there is no fragmentation. I was initially going to >use oncheck -pT output but found that the oncheck runs too slow for large >tables. Instead I am using the following sysmaster query: > >select count(*) from systabpagtypes a, systabnames b> >where a.tp_type != 0 and a.tp_partnum = b.partnum > >and b.tabname = "${TABLE}" and b.dbsname = "${DBNAME}"; > >The query runs faster than oncheck for big tables and returns what I believe >to be an accurate count of pages used in the tablespace. > >My dilema is this: The shop I work in are sticklers for using only documented >sysmaster tables. From what I've read systabpagtypes is an undocumented >sysmaster table (or view to sysptnbit) with that in mind can I reliably use it >in my script? > >Thanks - > >Mike > > >******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > >See you at the IIUG Informix 2008 Conference >The Power Conference for Informix Professionals >April 27 - 30, 2008 Marriott Overland Park (Kansas City), Kansas >http://www.iiug.org/conf >Registration Now Open!!

That would be an option - however that table is also not listed among the supported sysmaster tables: (sorry for the format - this is from http://publib.boulder.ibm.com/infocenter/idshelp/v10/index.jsp) The database server supports the following SMI tables. Table Description Reference sysadtinfo Auditing configuration information page sysadtinfo sysaudit Auditing event masks page *** syschkio Chunk I/O statistics page syschkio syschunks Chunk information page syschunks sysconfig Configuration information page sysconfig sysdatabases Database information page sysdatabases sysdbslocale Locale information page sysdbslocale sysdbspaces Dbspace information page sysextents sysdri Data-replication information page sysdri sysextents Extent-allocation information page sysextents sysextspaces External spaces information page sysextspaces syslocks Active locks information page syslocks syslogs Logical-log file information page syslogs sysprofile System-profile information page sysprofile sysptprof Table information page sysptprof syssesprof Counts of various user actions page syssesprof syssessions Description of each user connected page syssessions sysseswts User's wait time on each of several objects page sysseswts systabnames Database, owner, and table name for the tblspace tblspace page systabnames sysvpprof User and system CPU used by each virtual processor page sysvpprof

I don't have the buildsmi script in front of me at the moment, but if you crack that open, it should be fairly apparent what is built on sysptnhdr - if anything. I would think sysptprof, but would suspect that that may be profile info only. j. >From: MIKE MAGIE <jmmagie@yahoo.com> >Date: 2008/03/03 Mon PM 02:14:35 CST >To: ids@iiug.org >Subject: Re: sysmaster:systabpagtypes [11474] >That would be an option - however that table is also not listed among the >supported sysmaster tables: (sorry for the format - this is from >http://publib.boulder.ibm.com/infocenter/idshelp/v10/index.jsp) > >The database server supports the following SMI tables. > >Table Description Reference >sysadtinfo Auditing configuration information page sysadtinfo >sysaudit Auditing event masks page *** >syschkio Chunk I/O statistics page syschkio >syschunks Chunk information page syschunks >sysconfig Configuration information page sysconfig >sysdatabases Database information page sysdatabases >sysdbslocale Locale information page sysdbslocale >sysdbspaces Dbspace information page sysextents >sysdri Data-replication information page sysdri >sysextents Extent-allocation information page sysextents >sysextspaces External spaces information page sysextspaces >syslocks Active locks information page syslocks >syslogs Logical-log file information page syslogs >sysprofile System-profile information page sysprofile >sysptprof Table information page sysptprof >syssesprof Counts of various user actions page syssesprof >syssessions Description of each user connected page syssessions >sysseswts User's wait time on each of several objects page sysseswts >systabnames Database, owner, and table name for the tblspace tblspace page >systabnames >sysvpprof User and system CPU used by each virtual processor page sysvpprof > > >******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > >See you at the IIUG Informix 2008 Conference >The Power Conference for Informix Professionals >April 27 - 30, 2008 Marriott Overland Park (Kansas City), Kansas >http://www.iiug.org/conf >Registration Now Open!!

I looked at sysptnhdr - it will not return what I want. It is basically the table used by oncheck -pt, which can return an inaccurate value for npused. The table used by oncheck -pT (where page types and counts are displayed) is what I need, and I believe I am probably okay with using systabpagtypes, if I can convince my folks here that it is safe - or I can get a warm and fuzzy from someone at Informix... Mike