How to know the database name create on a dbspace?
I have lots of databases and dbspaces.
How to know the database name created on a special dbspace except "oncheck
-pe" and onmonitor? Do it has any SQL statement to do it? thanks.
Hello,
Try this SQL against sysmaster DB:
select a.name DBNAME, b.name DBSPACE
from sysdatabases a, sysdbspaces b
where trunc(partnum/1048576) = dbsnum;
The above SQL will get you the DB's home DBspace, You may want to remember
that table do not need to be in the home DBspace for a DB. Most large
systems spread a single DB across several DBspace. A query to find all
pieces of a database would be a little more involved, but if you understand
how the above query works it is not hard to generate a more involved one.
George.
From: "CHUAN LU" <luchuan114@sina.com>
To: ids@iiug.org
Date: 12/16/2010 06:50 AM
Subject: How to know the database name create on a dbspace? [22247]
Sent by: ids-bounces@iiug.org
I have lots of databases and dbspaces.
How to know the database name created on a special dbspace except "oncheck
-pe" and onmonitor? Do it has any SQL statement to do it? thanks.
*******************************************************************************
Forum Note: Use "Reply" to post a response in the discussion forum.
select sd.name
from sysmaster:sysdatabases as sd, sysmaster:sysdbspaces as ss
where ss.name = dbinfo( 'dbspace', sd.partnum )
and ss.name = 'somedbspacename';
name big_test
name superstores_demo
name sales_demo
3 row(s) retrieved.
Also, you can get my listdb7 utility which is in the package utils2_ak from
the IIUG Software Repository:
$ listdb7 -DThere are currently 8 databases available:
# Database/Table/Index/Owner DBSpace/Created Log/Lock Mode GLS?
=== ================================== =============== ============= ====
1 adtc_monitoring
art space16k
11/26/2010 UNBUFFERED No
2 big_test
informix datadbs
10/11/2010 UNBUFFERED No
3 sales_demo
informix datadbs
10/11/2010 UNBUFFERED No
4 superstores_demo
informix datadbs
10/11/2010 UNBUFFERED No
5 sysadmin
informix rootdbs
10/11/2010 UNBUFFERED No
6 sysmaster
informix rootdbs
10/11/2010 UNBUFFERED No
7 sysuser
informix rootdbs
10/11/2010 UNBUFFERED No
8 sysutils
informix rootdbs
10/11/2010 UNBUFFERED No
then (assuming you have a GNU compatible grep package that implements the -B
option):
$ listdb7 -D|fgrep -B1 datadbs
2 big_test
informix datadbs
--
3 sales_demo
informix datadbs
--
4 superstores_demo
informix datadbs
Art S. Kagel
Advanced DataTools (www.advancedatatools.com)
IIUG Board of Directors (art@iiug.org)
Blog: http://informix-myview.blogspot.com/
Disclaimer: Please keep in mind that my own opinions are my own opinions and
do not reflect on my employer, Advanced DataTools, the IIUG, nor any other
organization with which I am associated either explicitly, implicitly, or by
inference. Neither do those opinions reflect those of other individuals
affiliated with any entity with which I am affiliated nor those of the
entities themselves.
On Thu, Dec 16, 2010 at 7:49 AM, CHUAN LU <luchuan114@sina.com> wrote:
> I have lots of databases and dbspaces.
> How to know the database name created on a special dbspace except "oncheck
> -pe" and onmonitor? Do it has any SQL statement to do it? thanks.
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--001636c5a78fdcd3700497887161