The chances are that you can find what you want by running a query against sysmaster:'informix'.systabnames – which is a table in the sysmaster database.
SELECT * FROM sysmaster:'informix'.systabnames
Sample output (rather carefully selected rows):
partnum dbsname owner tabname collate dbsnum
INTEGER CHAR(128) CHAR(32) CHAR(128) CHAR(32) INTEGER
1048586 sysmaster informix sysusers en_US.819 1
1048641 sysmaster informix sysextdfiles en_US.819 1
1048648 sysuser informix sysattrtypes en_US.819 1
1048673 sysuser informix syssecpolicycomponents en_US.819 1
1048704 sysadmin informix sysconstraints en_US.819 1
1048723 sysadmin informix sysinherits en_US.819 1
1048731 sysadmin informix syserrors en_US.819 1
1048742 sysadmin informix sysxasourcetypes en_US.819 1
1048750 sysadmin informix sysseclabelnames en_US.819 1
1048794 sysadmin informix storagepool en_US.819 1
1048825 sysadmin informix 124_51 en_US.819 1
1048843 sysadmin informix mon_users en_US.819 1
1048856 sysadmin informix mon_prof_idx2 en_US.819 1
1048861 unlogged informix syscolumns en_US.819 1
1048878 sysutils informix syssynonyms en_US.819 1
1048881 sysutils informix sysreferences en_US.819 1
1048888 sysutils informix sysprocauth en_US.819 1
1048891 sysutils informix systriggers en_US.819 1
1048912 sysutils informix sysaggregates en_US.819 1
1048942 sysutils informix 102_10 en_US.819 1
1048952 sysuser informix syscolumns en_US.819 1
1048991 logged informix systabauth en_US.819 1
1049051 unlogged informix sysprocauth en_US.819 1
1049055 mode_ansi informix sysprocauth en_US.819 1
1049110 mode_ansi informix sysinherits en_US.819 1
1049128 logged informix syslogmap en_US.819 1
1049222 utf8 informix sysobjstate en_US.57372 1
1049259 utf8 informix sysseclabelcomponentelements en_US.57372 1
1049269 utf8 informix syssecpolicycomponents en_US.57372 1
1049297 unlogged jleffler dual en_US.819 1
1049298 stores jleffler dual en_US.819 1
1049303 stores jleffler 100_1 en_US.819 1
1049309 stores jleffler elements en_US.819 1
1049313 stores jleffler 101_4 en_US.819 1
1049318 stores jleffler 101_5 en_US.819 1
1049321 stores jleffler 101_6 en_US.819 1
1049341 stores jleffler compound en_US.819 1
1049343 stores jleffler 102_18 en_US.819 1
1049346 stores jleffler 102_19 en_US.819 1
1049351 stores jleffler compound_component en_US.819 1
1049353 stores jleffler 103_22 en_US.819 1
1049356 stores jleffler 103_23 en_US.819 1
1049358 stores jleffler 103_24 en_US.819 1
1049367 stores jleffler isotopes en_US.819 1
1049368 stores jleffler 104_30 en_US.819 1
1049371 stores jleffler 104_31 en_US.819 1
1049382 stores jleffler named_isotopes en_US.819 1
1049383 stores jleffler 105_37 en_US.819 1
1049385 stores jleffler 105_38 en_US.819 1
The 'table' such as " 105_38" are not tables but indexes. There may be other tables or views in sysmaster which satisfy your question better — but it is likely that querying sysmaster is the way to get the information you're after.
I figured out the answer to my question. The below works fine
select
case when num_of_extents between 0 and 9 then a.tabname end as one,
case when num_of_extents between 10 and 19 then a.tabname end two,
case when num_of_extents between 20 and 29 then a.tabname end three,
case when num_of_extents between 30 and 39 then a.tabname end four,
case when num_of_extents between 40 and 49 then a.tabname end five,
case when num_of_extents >= 50 then a.tabname end six
from (
select
a.tabname,
count(*) num_of_extents,
sum( pe_size ) total_size_pg,
round(sum( pe_size*4),2) total_size_kb,
round(sum( pe_size*4/1024 ),2) total_size_mb,
round(sum( pe_size *4/1024/1024),2) total_size_gb
from systabnames a, sysptnext b, sysdatabases c
where a.partnum = b.pe_partnum
and a.dbsname = c.name
and a.dbsname = 'system'
-- a.tabname not like ' %'
group by 1
order by 3 desc, 4 desc
) a group by 1,2,3,4,5,6
Output shows like below within dbaccess:
one
two bc_storage_idx2
three
four
five
six
one
two
three
four tock_mvmt
five
six
one
two
three rd_stats
four
five
six
one
two tock_mvmt_idx1
three
four
five
six
...
Please note that I did not use the same table data as above. The above is just an example of how the information will be presented within dbaccess. Due to the number of columns formatting it will not be able to display as needed, but can be filtered when outputting to spreadsheet when extracting.
The below formatting would work if I mess around with my environment variables within AIX:
select
(case when num_of_extents between 0 and 9 then a.tabname end)[2,20] as one,
case when num_of_extents between 10 and 19 then a.tabname[2,20] end two,
case when num_of_extents between 20 and 29 then a.tabname[2,20] end three,
case when num_of_extents between 30 and 39 then a.tabname[2,20] end four,
case when num_of_extents between 40 and 49 then a.tabname[2,20] end five,
case when num_of_extents >= 50 then a.tabname[2,20] end six
from (
select
a.tabname,
count(*) num_of_extents,
sum( pe_size ) total_size_pg,
round(sum( pe_size*4),2) total_size_kb,
round(sum( pe_size*4/1024 ),2) total_size_mb,
round(sum( pe_size *4/1024/1024),2) total_size_gb
from systabnames a, sysptnext b, sysdatabases c
where a.partnum = b.pe_partnum
and a.dbsname = c.name
and a.dbsname = 'system'
-- a.tabname not like ' %'
group by 1
order by 3 desc, 4 desc
) a group by 1,2,3,4,5,6
I don't know how to make the section visible in bold on stackexchange, but see [2,20], that will be used for format.
Best Answer
Since the
datadbsfile is not anywhere near 128 MiB, I wonder if you were successful in adding that dbspace. One might be forgiven for thinking that in fact you were unsuccessful since the database server doesn't think it exists.Use 'oncheck' or 'onstat' (or both) to check whether the dbspace exists and is intact. If not, arrange to drop it, or simply remove the
datadbsfile and try again with theonspacescommand.