how to determine what dbspace houses a table
A user asked how to find which dbspace(s) a given table (and its indexes) lives in, having found nothing obvious in onstat/oncheck/onmonitor. Several answers were given: query sysmaster:systabnames with dbinfo('dbspace', partnum) (joining sysindexes/sysfragments to cover detached indexes and fragmented tables); run dbschema -d db -t table -ss, which shows the dbspace unless the table sits in the database's default dbspace; use oncheck -pe to list extents per chunk/dbspace; or use IIUG tools such as tab2dbsp.sh and Art Kagel's utils2_ak (myschema, listdb7 -t). The poster thanked the list and said he'd use the supplied script.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Sorry for endless questions here. I checked output from various onstat and
oncheck commands, tried onmonitor and dbaccess but I am having a hard time
answering a basic question....Give user table X how can one determine what
dbspaces house this table. Could someone let me know where to look for this?
Thanks.
Does anyone know a certified Informix DBA looking for a position in
Florida? If so call me at 813-281-8800 for details.
Jason
-----Original Message-----
From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of
WILL LANDSTROM
Sent: Tuesday, February 19, 2008 3:55 PM
To: ids@iiug.org
Subject: how to determine what dbspace houses a table [11340]
Sorry for endless questions here. I checked output from various onstat
and oncheck commands, tried onmonitor and dbaccess but I am having a
hard time answering a basic question....Give user table X how can one
determine what dbspaces house this table. Could someone let me know
where to look for this?
Thanks.
************************************************************************
*******
Forum Note: Use "Reply" to post a response in the discussion forum.
See you at the IIUG Informix 2008 Conference The Power Conference for
Informix Professionals April 27 - 30, 2008 Marriott Overland Park
(Kansas City), Kansas http://www.iiug.org/conf Registration Now Open!!
Actually that was a good question. In Informix there is no easy way to
immediately tell where or which dbspace(s) the table resides. You can cut and
paste this into dbaccess and replace 'database_name' with the database you are
working with, it should give you a table location report ...
Hope this helps...
Kern --
type dbaccess 'database_name'
--- cut from here -----
{temp table is for formatting the output...}
create temp table tmptablocation (
tabname char(18),
dbsname char(18)
) with no log;
set isolation to dirty read;{this 1st part is to generate a list of table and the
dbspace where it resides including attached index(es) }
insert into tmptablocation
select t.tabname[1,18], dbinfo('dbspace', n.partnum)
--select t.tabname[1,18], n.partnum
from sysmaster:systabnames n, systables t
where n.dbsname='database_name' andt.tabid > 99 and
t.tabtype = 'T' and
n.tabname=t.tabname;
{the 2nd part is to generate a list of table and the
dbspaces of its detached index(es) }
insert into tmptablocation
select t.tabname[1,18], dbinfo('dbspace', n.partnum)
from sysindexes x, sysmaster:systabnames n, systables t
where n.dbsname='database_name' andn.tabname=idxname and
x.tabid > 99 and
x.tabid=t.tabid;
{merging the 1st and the 2nd , we should have a complete list}
select * from tmptablocation
group by 1,2 order by 1,2;
drop table tmptablocation;--- end of cutting ---
----- Original Message ----
From: WILL LANDSTROM <willlandstrom@yahoo.com>
To: ids@iiug.org
Sent: Tuesday, February 19, 2008 3:54:30 PM
Subject: how to determine what dbspace houses a table [11340]
Sorry for endless questions here. I checked output from various onstat and
oncheck commands, tried onmonitor and dbaccess but I am having a hard time
answering a basic question....Give user table X how can one determine what
dbspaces house this table. Could someone let me know where to look for this?
Thanks.
*******************************************************************************
Forum Note: Use "Reply" to post a response in the discussion forum.
See you at the IIUG Informix 2008 Conference
The Power Conference for Informix Professionals
April 27 - 30, 2008 Marriott Overland Park (Kansas City), Kansas
http://www.iiug.org/conf
Registration Now Open!!
________________________________________________________________________________
____
Be a better friend, newshound, and
know-it-all with Yahoo! Mobile. Try it now.
http://mobile.yahoo.com/;_ylt=Ahu06i62sR8HDtDypao8Wcj9tAcJ
Dbschema with the -ss option will show you in dbspacename if that was
used with the create table. If this does not exist in the schema then
the table is in the dbspace that the database was created in.
Oncheck -pe will print the extents in the instance by chunk. You can
search this output for the table and see which chunk(s) and dbspace it
is in.
MW
-----Original Message-----
From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of
WILL LANDSTROM
Sent: Wednesday, 20 February 2008 9:55 a.m.
To: ids@iiug.org
Subject: how to determine what dbspace houses a table [11340]
Sorry for endless questions here. I checked output from various onstat
and
oncheck commands, tried onmonitor and dbaccess but I am having a hard
time
answering a basic question....Give user table X how can one determine
what
dbspaces house this table. Could someone let me know where to look for
this?
Thanks.
************************************************************************
*******
Forum Note: Use "Reply" to post a response in the discussion forum.
See you at the IIUG Informix 2008 Conference
The Power Conference for Informix Professionals
April 27 - 30, 2008 Marriott Overland Park (Kansas City), Kansas
http://www.iiug.org/conf
Registration Now Open!!
another useful script utility - tab2dbsp.sh from IIUG
http://www.iiug.org/software/archive/dbmon_pm
---- Kern Doe <kern_doe@yahoo.com> wrote:
> Actually that was a good question. In Informix there is no easy way to
> immediately tell where or which dbspace(s) the table resides. You can cut and
> paste this into dbaccess and replace 'database_name' with the database you
are
> working with, it should give you a table location report ...
> Hope this helps...
> Kern --
>
> type dbaccess 'database_name'
>
> --- cut from here -----
> {temp table is for formatting the output...}
> create temp table tmptablocation (>
> tabname char(18),
>
> dbsname char(18)
> ) with no log;
> set isolation to dirty read;> {this 1st part is to generate a list of table and the
> dbspace where it resides including attached index(es) }
> insert into tmptablocation
> select t.tabname[1,18], dbinfo('dbspace', n.partnum)
> --select t.tabname[1,18], n.partnum
> from sysmaster:systabnames n, systables t
> where n.dbsname='database_name' and> t.tabid > 99 and
> t.tabtype = 'T' and
> n.tabname=t.tabname;
> {the 2nd part is to generate a list of table and the
> dbspaces of its detached index(es) }
> insert into tmptablocation
> select t.tabname[1,18], dbinfo('dbspace', n.partnum)
> from sysindexes x, sysmaster:systabnames n, systables t
> where n.dbsname='database_name' and> n.tabname=idxname and
> x.tabid > 99 and
> x.tabid=t.tabid;
> {merging the 1st and the 2nd , we should have a complete list}
> select * from tmptablocation
> group by 1,2 order by 1,2;
> drop table tmptablocation;> --- end of cutting ---
>
> ----- Original Message ----
> From: WILL LANDSTROM <willlandstrom@yahoo.com>
> To: ids@iiug.org
> Sent: Tuesday, February 19, 2008 3:54:30 PM
> Subject: how to determine what dbspace houses a table [11340]
>
> Sorry for endless questions here. I checked output from various onstat and
> oncheck commands, tried onmonitor and dbaccess but I am having a hard time
> answering a basic question....Give user table X how can one determine what
> dbspaces house this table. Could someone let me know where to look for this?
> Thanks.
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
> See you at the IIUG Informix 2008 Conference
> The Power Conference for Informix Professionals
> April 27 - 30, 2008 Marriott Overland Park (Kansas City), Kansas
> http://www.iiug.org/conf
> Registration Now Open!!
>
>
>
________________________________________________________________________________
____
> Be a better friend, newshound, and
> know-it-all with Yahoo! Mobile. Try it now.
> http://mobile.yahoo.com/;_ylt=Ahu06i62sR8HDtDypao8Wcj9tAcJ
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
> See you at the IIUG Informix 2008 Conference
> The Power Conference for Informix Professionals
> April 27 - 30, 2008 Marriott Overland Park (Kansas City), Kansas
> http://www.iiug.org/conf
> Registration Now Open!!
Thanks Kern I'll run this script. I appreciate it.
WILL LANDSTROM wrote:
> Sorry for endless questions here. I checked output from various onstat and
> oncheck commands, tried onmonitor and dbaccess but I am having a hard time
> answering a basic question....Give user table X how can one determine what
> dbspaces house this table. Could someone let me know where to look for this?
> Thanks.
>
dbschema -d <mydatabase> -t <mytable> -ss
-- That will work unless the table was created in the default dbspace
the database was created in.
or
dbaccess sysmaster -
select dbinfo( 'dbspace', partnum )
from systabnames
where dbsname = 'mydatabase' and tabname = 'mytable';
or
Get my package utils2_ak it contains two utilities which will tell you.
My dbschema replacement utility, myschema, prints the dbspace for every
table even if it is contained in the default dbspace. My listdb7
utility with the -t option will also print out the dbspace containing
the table along with lots of other interesting information. Utils2_ak
is downloadable from the IIUG Software Repository.
Art S. Kagel
Oninit
================================================================================
===========
Please access the attached hyperlink for an important electronic
communications disclaimer:
http://www.oninit.com/home/disclaimer.php
================================================================================
===========
Here is a script that I put together....
cat tables_by_dbspace.ksh
#!/usr/bin/ksh
DBSPACE=${1}
if [[ ! -z ${DBSPACE} ]]; then
DBSPACE=" and d.name = \\\\"${DBSPACE}\\\\""
fi
dbaccess ${DBNAME} <<!
select substr(d.name,1,20) as DBSpace
,substr(t.tabname,1,30) as TableName
from systables t, sysmaster:sysdbspaces d
where d.dbsnum = trunc(partnum / 1048576)
and t.partnum != 0 -- Unfragmented tables
and tabid > 99
and tabtype = "T"
${DBSPACE}
union
select substr(d.name,1,20)
,substr(t.tabname,1,30)
from systables t, sysfragments f, sysmaster:sysdbspaces d where
d.dbsnum = trunc(f.partn/1048576)
and f.tabid = t.tabid
and t.partnum = 0 -- Fragmented tables
and t.tabid > 99
and tabtype = "T"
${DBSPACE}
order by 1, 2
!
-----Original Message-----
From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of
Art S. Kagel (Oninit LLC)
Sent: Tuesday, February 19, 2008 7:47 PM
To: ids@iiug.org
Subject: Re: how to determine what dbspace houses a table [11351]
WILL LANDSTROM wrote:
> Sorry for endless questions here. I checked output from various onstat
> and oncheck commands, tried onmonitor and dbaccess but I am having a
> hard time answering a basic question....Give user table X how can one
> determine what dbspaces house this table. Could someone let me know
where to look for this?
> Thanks.
>
dbschema -d <mydatabase> -t <mytable> -ss
-- That will work unless the table was created in the default dbspace
the database was created in.
or
dbaccess sysmaster -
select dbinfo( 'dbspace', partnum )
from systabnames
where dbsname = 'mydatabase' and tabname = 'mytable';
or
Get my package utils2_ak it contains two utilities which will tell you.
My dbschema replacement utility, myschema, prints the dbspace for every
table even if it is contained in the default dbspace. My listdb7 utility
with the -t option will also print out the dbspace containing the table
along with lots of other interesting information. Utils2_ak is
downloadable from the IIUG Software Repository.
Art S. Kagel
Oninit
========================================================================
===================
Please access the attached hyperlink for an important electronic
communications disclaimer:
http://www.oninit.com/home/disclaimer.php
========================================================================
===================
************************************************************************
*******
Forum Note: Use "Reply" to post a response in the discussion forum.
See you at the IIUG Informix 2008 Conference
The Power Conference for Informix Professionals
April 27 - 30, 2008 Marriott Overland Park (Kansas City), Kansas
http://www.iiug.org/conf
Registration Now Open!!
Please do not transmit orders or instructions regarding a UBS account by
e-mail. The information provided in this e-mail or any attachments is not an
official transaction confirmation or account statement. For your protection,
do not include account numbers, Social Security numbers, credit card numbers,
passwords or other non-public information in your e-mail. Because the
information contained in this message may be privileged, confidential,
proprietary or otherwise protected from disclosure, please notify us
immediately by replying to this message and deleting it from your computer if
you have received this communication in error. Thank you.
UBS Financial Services Inc.
UBS International Inc.
UBS Financial Services Incorporated of Puerto Rico