Checking what tables are created on any specific dbspace

Question

How to check what tables are created in any specific dbspace

Answer

The following command can be executed from the UNIX command line on the sysmaster database to find out what are all the tables created on any specific dbspace. The <dbspace> should be replaced with the real dbspace name.

echo "select tabname from sysptnext, systabnames
where pe_partnum = partnum and dbinfo( 'DBSPACE' , pe_partnum ) = <dbspace> group by pe_partnum, dbsname, tabname
order by tabname"|dbaccess sysmaster

For example, to list out all the tables created on dbspace3, the above query will be like below:

echo "select tabname from sysptnext, systabnames
where pe_partnum = partnum and dbinfo( 'DBSPACE' , pe_partnum ) = 'dbspace3'
group by pe_partnum, dbsname, tabname
order by tabname"|dbaccess sysmaster

Sample output:

Database selected.



tabname TBLSpace

tabname alcatel_a_interface_bssap_week

tabname alcatel_bearer_bearer_channel_month

tabname alcatel_bsc_fabric_gch_week

tabname alcatel_bsc_fabric_tbf_monitoring_per_gpu_month

tabname alcatel_bsc_overviewchannel_week

tabname alcatel_cell_agch_month

tabname alcatel_cell_bvc_week

tabname alcatel_cell_directed_retry_month

tabname alcatel_cell_dl_tbf_establishments_1_week

tabname alcatel_cell_dl_tbf_releases_month

tabname alcatel_cell_dtap_week

tabname alcatel_cell_established_phase_tch_month

tabname alcatel_cell_external_directed_retry_week

tabname alcatel_cell_handover_sdcch_intra_day

tabname alcatel_cell_handover_sdcch_outgoing_month

tabname alcatel_cell_handover_sdcch_per_cause_week

tabname alcatel_cell_handover_tch_incoming_raw

tabname alcatel_cell_handover_tch_month

tabname alcatel_cell_handover_tch_per_cause_1_month


NOTE: Please do note to source your environment before running the above, so that you have access to the Informix Database.

https://www-304.ibm.com/support/docview.wss?uid=swg21392665