Re: Which tables are in which dbspace?

Thanks for all the interesting replies. We were looking for all tables in a dbspace which was full so as to move one of them to make room for more inserts into the others! The missing documentation was which table was where - the person who normally looks after that system was away hence it was missing. In the end my colleague ran dbschema into a file and checked that, but I was sure there was a more elegant way. I will be checking through the various suggestions to see the differences in what they display, and how easy the output is to use. Most of all I was fascinated to see the SQL's joining two databases (the 'current' one and sysmaster). I felt that was where the answer lay but wasn't sure how to do such a thing. Is Art's site the best place to look for such gems of wisdom? Other suggestions would be most welcome. Many thanks In article <7h77o1$568$1@news.xmission.com>, Mark Collins <mcollins@us.dhl.com> writes > >> SELECT tabname, DBINFO('dbspace', HEX(partnum)) >> FROM systables;> >I've seen this syntax posted to this newsgroup before, but I've always used the >method I posted earlier (joining systables with sysmaster:sysdbspaces, possibly >including a union with sysfragments). I guess the reason I always used that is >that the DBINFO function wasn't around, or at least not properly documented, >when I was first working with 7.10.UC2. Anyway, I decided to try the above >SELECT to see what I've been missing. I receive the message "727: Invalid or >NULL TBLspace number given to dbinfo(dbspace)." when running this within isql. >It chokes when it encounters a fragmented table, since partnum in systables is >0. So, if you have fragmented tables, you must alter the query to: > > SELECT tabname, DBINFO('dbspace', HEX(partnum)) > FROM systables > WHERE partnum > 0;> >(Actually, the 'HEX(partnum)' is not necessary, just 'partnum' will work.) >Then, to get the same information on fragmented tables, you have to have a >query joining sysfragments with systables. > >I do like this form a bit better than my old join with sysmaster:sysdbspaces, >as it avoids the hassle I documented last week about joining sysmaster with >non-logged databases. I just wonder if I'll remember to use this new way. > > > > >Mark Collins >mcollins@us.dhl.com > >Everybody at some level realizes that the calendar is a fairly >arbitrary thing, invented by humans, for reasons that have more to >do with how committees are structured than with anything that's >really happening in the heavens. And yet people look at the fact >that the calendar is about to turn 2000, and assume there's some >deity who thinks that the base 10 counting system is pretty darn >important, and make all sorts of predictions of doom and gloom as a >result. To me, that tells you everything you need to know about >human beings -- and a whole lot about the market for the NC. > Scott Adams, creator of _Dilbert_ > -- Surfer!

Surfer! wrote: > > Thanks for all the interesting replies. [SNIP] > Is Art's site the best place to look for such gems of wisdom? Other > suggestions would be most welcome. It's not my site it is the International Informix Users Group (IIUG) WEB Site and yes. Yes, the IIUG Software Repository is the first place to look for DBA utilities and scripts, 4GL and ESQL library gems, and other related stuff. [SNIP] Art S. Kagel