Re: an SQL question

Troy Burns - Trinity (troyb@genesis.admin.bethel.edu) wrote: : Informix netters: : I have a question: Is there a way from within SQL to find: : 1. the number of pages allocated to a particular table? The npused column of the systables catalog has this information. Look it up using the name of the table, as in: SELECT npused FROM systables WHERE tabname = "mytable"; : 2. the number of extents that table uses? You can back into the number of extents used from the pages allocated (as above) and the columns fextsize (first extent size) and nextsize (all subsequent extent size), also in the systables catalog. This is a pain in the neck because the extent sizes are given in Kbytes and npused is in pages. Page size in Kbytes is an immutable machine-dependent value you can find in your tbconfig file. The calculation is left as an exercise to the reader, since I can't event remember enough about ISQL to do it. It would be a one line 4GL or ESQL/C calculation. : 3. the length of all the indexes for that table? If you thought getting the number of extents was hard, you ain't seen nothin' yet! To get the length of all indexes for a table requires information from the systables, sysindexes and syscolumns catalogs. One can join systables to sysindexes on the tabid column. Sysindexes has in it 16 columns, called part[1-16], that contain a column ID for that part of the index or 0 if there are no additional parts. For instance, in a table with an index on two columns, part1 would contain the column ID of the first part of the index and part2 would contain the second column ID. From there, one may join into the syscolumns catalog where sysindexes.tabid = syscolumns.tabid AND sysindexes.part[1-16] = syscolumns.colno I don't believe it's even possible to code such a join in ISQL. You'll need a higher level language like 4GL or ESQL/C to set up a cursor on sysindexes and, one by one, join the part[1-16] columns into syscolumns to get the collength value for each column. Add up all the collength values and you've got your number. : Relevant info: Online 5.00.UC2 running on a Sequent Symmetry 2000/250 : running Dynix 1.3 : Thanks in advance for the info. : Troy Burns : troyb@genesis.admin.bethel.edu I hope this helps (and hope you're not trapped into using ISQL to do this!). You could also always do what I do to get the number of pages and extents. Run tbcheck -pT and run an awk script against it to get your numbers! I can send you or anyone else such a script through private e-mail if there is an interest. ============================================================================= | John D. Allen | "I want to die peacefully in my sleep like | | Evolving Systems, Inc. | my grandfather, not terrified and | | Englewood, CO, USA 80111 | screaming like his passengers." | | E-mail: jallen@evolving.com | | | Phone: (303) 689-1324 | | =============================================================================