question on how to list tables in a DBSPACE?
Lee Duong asked for a script listing all dbspaces and the tables in each with sizes. B. Dana pointed to Lester Knutsen's sysmaster SQL on the IIUG site. Mark Oberfield gave a sysmaster query using dbinfo(DBSPACE, pe_partnum) with sysptnext and systabnames, summing pe_size. Arni Thoroddsen suggested parsing CREATE TABLE scripts and using onstat -d.
Auto-generated by Claude from the posts below — may be imperfect; read the full thread.
Hi, Does anyone have some type of scripts that list All DBSPACE for an Informixserver, and all tables in each DBSPACE + how much bytes they take? Thanks!
Have a look at Lester Knutsen's stuff on the IIUG site: http://iiug.org/members/memb_software/archive/sysmast_sql And the old WAIUG newsletter: http://www.advancedatatools.com/waiug/iugnew64.htm Lee Duong wrote: > Hi, > Does anyone have some type of scripts that list All DBSPACE for an > Informixserver, and all tables in each DBSPACE + how much bytes they take? > > Thanks!
>>>>> " " == Lee Duong <lehocd@sprint.ca> writes: > Hi, Does anyone have some type of scripts that list All DBSPACE > for an Informixserver, and all tables in each DBSPACE + how > much bytes they take? Oh, oh, I know this one! Try this: In the sysmaster database (you _are_ running IDS > 5 aren't you?): select dbinfo("DBSPACE",pe_partnum) dbspace,dbsname,tabname, sum(pe_size)*2 KB from sysptnext, systabnames where partnum=pe_partnum group by 1,2,3 order by 1,4 desc; Mark -- "A conservative is one who admires radicals centuries after they're dead." -- Leo C. Rosten
Lee Duong wrote:
> Hi,
> Does anyone have some type of scripts that list All DBSPACE for an
> Informixserver, and all tables in each DBSPACE + how much bytes they take?
>
> Thanks!
Assuming that you have all of the text of the code that built the structure of
tables in the dataspaces( CREATE TABLE etc.) , all you would have to do is
lump all of the text of this code together in one MS Word document and then
built a VB macro that finds all cases of sentences that contain:
<CREATE tablename sentences> " IN <DBSPACE name>".
You only need to find " IN <DBSPACE name>" of course.
You could then have the macro output a line of information concerning the
particular table in the particular DBSPACE.
Or you could just do a find in the MS Word document and copy and paste
manually to for example an Excel document to build up a list of this
information.
But I perhaps Informix has a way of doing this directly? You should be able to
get the information using select statements into the systables to get this
kind of information.
If you dont place the tables in a particular DBSPACE with <CREATE tablename
sentences> " IN <DBSPACE name>"they will be placed in the default DBSPACE (you
are of course better of placing them in particular DBSPACES).
Personally I prefer to document in EXCEL the creation of the DBSPACES and the
tables when I make them originally. I dont rely on my memory with anything of
this sort. I want this information available immediately.
Of course onstat -d should give you the names of the spaces and some
additional info that you can read about in the Administrators guide, Volume 2.
--
Arni Thoroddsen
arnith@loki.bok.hi.is
Related threads
- IDS 10 table-level restore
- Informix Development Webinar December 11, 2007
- ontape -p/r with changed ROOTPATH
- Migrate from HP PA-RISC to HP ITANIUM by ontape