Results 1 to 5 of 5
  1. #1
    Join Date
    Dec 2002
    Location
    cincinnati
    Posts
    18

    Unanswered: how to get table and dbspacename through a sql/script

    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
    jagadish dara

  2. #2
    Join Date
    May 2004
    Location
    Barcelona, Spain
    Posts
    54
    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.

  3. #3
    Join Date
    Dec 2002
    Location
    cincinnati
    Posts
    18
    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
    jagadish dara

  4. #4
    Join Date
    May 2004
    Location
    Barcelona, Spain
    Posts
    54
    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

  5. #5
    Join Date
    Dec 2002
    Location
    cincinnati
    Posts
    18
    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
    jagadish dara

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •