Query for list of databases in an instance 
Author Message
 Query for list of databases in an instance
Platform: DB2 UDB for AIX:

Trying to programmatically get the names of databases from a server.

In MS/SQL server it is :

1) SQL Server

SELECT name FROM master..sysdatabases ORDER BY (name)

Is there a comparable table in DB2 for Unix? or a way to do it as above?

TIA
Gabriel C.



Sat, 09 Jul 2005 10:31:02 GMT
 Query for list of databases in an instance

There is no all encompassing "master" database in DB2.  This script
may provide what you require:

#-------------------------------------------------------------------------#
#--  Get all instances on the machine.  Then loop through each
instance --#
#-------------------------------------------------------------------------#
DB2ILIST="`db2ilist`"
for DB2INST in $DB2ILIST
do

   #----------------------------------------------------------------------#
   #--  Run the "db2profile" of the current instance to set the DB2xxx
 --#
   #--  variables.  We could also do an "attach" from here instead.  
 --#
   #----------------------------------------------------------------------#
   . /db2server/${DB2INST}/sqllib/db2profile >/dev/null 2>&1
   echo "\nStarting Instance $DB2INST"

   #----------------------------------------------------------------------#
   #--  Get all databases in the current instance.                    
 --#
   #--  Then we loop through each database in the instance.          
 --#
   #----------------------------------------------------------------------#
   LOCDBNAMES="`db2 list database directory | grep 'Database alias' |
\
               awk '{print $4}' `"
   for DBNAME in $LOCDBNAMES
   do
      echo ${DBNAME}
      db2 connect to ${DBNAME}
      db2 list tablespaces

   done
   #----------------------------------------------------------------------#
   #--  Issue the terminate command to prepare for the next instance  
 --#
   #----------------------------------------------------------------------#
   db2 terminate
done

echo "\n${0} Complete"



Sun, 17 Jul 2005 05:32:50 GMT
 Query for list of databases in an instance

:)Platform: DB2 UDB for AIX:
:)
:)Trying to programmatically get the names of databases from a server.
:)
:)In MS/SQL server it is :
:)
:)1) SQL Server
:)
:)SELECT name FROM master..sysdatabases ORDER BY (name)
:)
:)
:)Is there a comparable table in DB2 for Unix? or a way to do it as above?

  Login as instance and execute following command:

$ db2 list database directory

 System Database Directory

 Number of entries in the directory = 2

Database 1 entry:

 Database alias                  = GBLCODE
 Database name                   = GBLCODE
 Local database directory        = /s9/db2gblcd
 Database release level          = 9.00
 Comment                         =
 Directory entry type            = Indirect
 Catalog node number             = 0

Database 2 entry:

 Database alias                  = REAC
 Database name                   = REAC
 Local database directory        = /s9/db2gblcd
 Database release level          = 9.00
 Comment                         =
 Directory entry type            = Indirect
 Catalog node number             = 0

:)
:)TIA
:)Gabriel C.

--
Hemant Shah                           /"\  ASCII ribbon campaign

                                       X     against HTML mail
TO REPLY, REMOVE NoJunkMail           / \      and postings      
FROM MY E-MAIL ADDRESS.          
-----------------[DO NOT SEND UNSOLICITED BULK E-MAIL]------------------
I haven't lost my mind,                Above opinions are mine only.
it's backed up on tape somewhere.      Others can have their own.



Sun, 17 Jul 2005 23:40:33 GMT
 Query for list of databases in an instance
There are also APIs that do "list database directory", so you can do
this programatically.
See the following APIs in the Administrative API reference:
sqledosd - Open Database Directory Scan
sqledgne - Get Next Database Directory Entry
sqledcls - Close Database Directory Scan

Quote:


> :)Platform: DB2 UDB for AIX:
> :)
> :)Trying to programmatically get the names of databases from a server.
> :)
> :)In MS/SQL server it is :
> :)
> :)1) SQL Server
> :)
> :)SELECT name FROM master..sysdatabases ORDER BY (name)
> :)
> :)
> :)Is there a comparable table in DB2 for Unix? or a way to do it as above?

>   Login as instance and execute following command:

> $ db2 list database directory

>  System Database Directory

>  Number of entries in the directory = 2

> Database 1 entry:

>  Database alias                  = GBLCODE
>  Database name                   = GBLCODE
>  Local database directory        = /s9/db2gblcd
>  Database release level          = 9.00
>  Comment                         =
>  Directory entry type            = Indirect
>  Catalog node number             = 0

> Database 2 entry:

>  Database alias                  = REAC
>  Database name                   = REAC
>  Local database directory        = /s9/db2gblcd
>  Database release level          = 9.00
>  Comment                         =
>  Directory entry type            = Indirect
>  Catalog node number             = 0

> :)
> :)TIA
> :)Gabriel C.

> --
> Hemant Shah                           /"\  ASCII ribbon campaign

>                                        X     against HTML mail
> TO REPLY, REMOVE NoJunkMail           / \      and postings
> FROM MY E-MAIL ADDRESS.
> -----------------[DO NOT SEND UNSOLICITED BULK E-MAIL]------------------
> I haven't lost my mind,                Above opinions are mine only.
> it's backed up on tape somewhere.      Others can have their own.

--

Kaarel Truuvert
DB2 UDB Development



Mon, 18 Jul 2005 22:40:48 GMT
 
 [ 4 post ] 

 Relevant Pages 

1. How can I list the databases of an instance from Unix prompt

2. Listing all databases in an instance of On-Line

3. db2ilist lists instances? what utility/CLP command lists database under an instance?

4. Distributed/remote query to a SQL 2000 instance on multi instance box

5. URGENT:Distributed query against a multi-instance SQL SErver 2000 instance

6. 2 Instances, 1 machine VS 1 Instance 2 databases

7. Query to get list of databases of which a user is the db_owner

8. Challengin SQL Query listing all non-empty tables in a database

9. Query for List of tables in Access database

10. Query for List of tables in Access database

11. Querying SQL server 7.00 (MSDE) to list it's databases

12. Listing Instances on a SQL 2000 Box with SQL-DMO


 
Powered by phpBB® Forum Software