Can somebody help in obtaining tablename,dbspace name for a database in informix through a sql/script. If something is available like that.
Note: We have some tables fragmented in several dbspaces also.

Env: Informix 7.31UD5 on AIX4.3.3

Thanks in advance
Jagadish

This is what I use ( it gives me some more information than what you asked for)

dbaccess sysmaster << EOF
select c.name, a.dbsname, a.tabname, count(*) num, sum(size) tam
from sysextents a, syschunks b, sysdbspaces c
where a.chunk = b.chknum
and b.dbsnum = c.dbsnum
group by 1, 2, 3
EOF

hope this helps you.

When I try to run, it comes and tells me this

u060qsl2 prd /tmp>dbaccess sysmaster 1.sql

Database selected.


217: Column (chunk) not found in any table in the query (or SLV is undefined).
Error in line 3
Near character position 14

Database closed.

u060qsl2 prd /tmp>

It appears in sysextents tables there is no col by name chknum or chunk.


Actually I read another thread in this forum where Loyd answered.

I used his idea like this

select tabname, trunc(systabnames.partnum/1048576) dbspace,
sysdbspaces.name
from systabnames,sysdbspaces,
sysdatabases
Where sysdbspaces.dbsnum = trunc(systabnames.partnum/1048576)
and systabnames.owner="informix"
and tabname not like 'sys%'
and sysdatabases.name='qsl'
and sysdbspaces.name not in ('rootdbs');

I wanted all the tables,dbspace names for the database called qsl.

When I ran that, it even picks the indexes also. In our database we have several tables has got indexes placed in different dbspaces.

I am thinking, I can managed manually editing the unload file of the above query output.

It appears, you came up with a simple sql even.

I really appreciate,if you make it to work.

Thanks
Jagadish

my script works in IDS 9.40, but system tables are different from those in 7.31

sorry you can't use it,
but i think your script gets closer to what you wanted in the first place!

anyway, let's give it a try, from an older script.
this may work for you:

select b.name, a.dbsname, a.tabname, count(*) num, sum(size) tam
from sysextents a, sysdbspaces b
where b.dbsnum = trunc(a.start/1048576)
group by 1, 2, 3

I tried your new sql and the one I said, I copied from other thread.

The output is not correct in both the cases.

For example I have taken dbschema for a table
caoinactmov
dbschema -d qsl -t caoinactmov -ss > cao.sql

cat cao.sql
create table "informix".caoinactmov
(
mgt_div_no char(3) not null ,
sto_no char(5) not null ,
con_upc_no char(14) not null ,
cal_dt date not null ,
mjr_dpt_no integer not null ,
com_cd char(3) not null ,
cat_no char(3) not null ,
mov_qy integer not null
) extent size 446272 next size 44640 lock mode row;
revoke all on "informix".caoinactmov from "public";

create unique index "informix".caoinactmov_1ix on "informix".caoinactmov
(mgt_div_no,sto_no,con_upc_no);
create index "informix".caoinactmov_2ix on "informix".caoinactmov
(mgt_div_no,sto_no,cal_dt,mov_qy);

From this we can conclude that this table is sitting in the datbase dbspace
which is qsl01dbs.


I ran your sql and unloaded to a flat file

When I grep for that table in 1.unl

u060qsl2 prd /tmp>grep -w "caoinactmov" 1.unl
qsl04dbs|qsl|caoinactmov_dia|1.0|8.0|
qsl10dbs|qsl|caoinactmov|2.0|22320.0|
qsl09dbs|qsl|caoinactmov|6.0|66960.0|
qsl03dbs|qsl|caoinactmov|1.0|111568.0|
qsl05dbs|qsl|caoinactmov_vio|1.0|8.0|
qsl08dbs|qsl|caoinactmov|2.0|212040.0|
qsl07dbs|qsl|caoinactmov|1.0|11160.0|
u060qsl2 prd /tmp>

We can ignore those _dia and _vio since they are from HP load job.
But it is showing the table is sitting in so many dbspaces, actually it is sitting
only qsl01 dbs.

When I ran in another way, I am still getting the duplicates.

The new sql is this,
u060qsl2 prd /tmp>cat t.sql
unload to t.unl
select tabname, trunc(systabnames.partnum/1048576) dbspace,
sysdbspaces.name
from systabnames,sysdbspaces
Where sysdbspaces.dbsnum = trunc(systabnames.partnum/1048576)
and systabnames.owner="informix";
u060qsl2 prd /tmp>

u060qsl2 prd /tmp>grep -w "caoinactmov" t.unl
caoinactmov_vio|5.0|qsl01dbs|
caoinactmov_dia|5.0|qsl01dbs|
caoinactmov|5.0|qsl01dbs|
caoinactmov|14.0|qsl10dbs|
u060qsl2 prd /tmp>

Here it is comming closer, but I donot know why it is picking another
dbspace qsl10dbs as if this table is sitting there also. Actually this table
is sitting only in qsl01dbs .

Any thoughts.

Thanks
Jagadish