How to get Databases Name in DB2

[hosesurya]

Hello, all I am a newbie in DB2 (and thescripts.com) =P

I want to ask:
How can I get (list) Databases name / aliases in DB2 using Java?

I am using windows XP
DB2 v8.1.7.445
JDK 1.4.1

thx for the answer... =)

[r035198x]

Hello, all I am a newbie in DB2 (and thescripts.com) =P

I want to ask:
How can I get (list) Databases name / aliases in DB2 using Java?

I am using windows XP
DB2 v8.1.7.445
JDK 1.4.1

thx for the answer... =)

You do know how to connect to DB2 using JDBC right?
You could then try the

list database directory

command.

[hosesurya]

You do know how to connect to DB2 using JDBC right?
You could then try the

list database directory

command.

Hm...
I'd try that...
I can connect to DB2 using JDBC and I've tried that command...
But, the exception return this message:
"[IBM][JDBC Driver] CLI0637E QUERY cannot be found."

maybe you can give me further explanation or suggestion (with details)?
My goals is I want to list all DB2 databases that connect trough my JDBC.
i.e: I have DBs: UNIVERSITY1, UNIVERSITY2, UNIVERSITY3
and I want to get all the DBs name (UNIVERSITY1, UNIVERSITY2, and UNIVERSITY3) trough Java and stored in a variable.
So, I can displayed on Combobox all the DBs name.

As far as I know that command will works when we use it in DB2 Command Line Processor, but I think that command will not works when we used it like ordinary query.

[docdiesel]

As far as I know that command will works when we use it in DB2 Command Line Processor, but I think that command will not works when we used it like ordinary query.

That's right. The only commands accepted via JDBC are SQL statements. DB2 commands like Load, Runstats or "List db directory" will be rejected.

May I suggest a workaround: Add a table to DB UNIVERSITY1 and keep a list of your databases in there. With some statement like

SELECT
  a.dbname
from
  meta.listofdatabases a
order by
  a.dbname  asc

you'll be able to offer a dropbox with the databases names easily.

Regards,

Bernd

[r035198x]

That's right. The only commands accepted via JDBC are SQL statements. DB2 commands like Load, Runstats or "List db directory" will be rejected.

May I suggest a workaround: Add a table to DB UNIVERSITY1 and keep a list of your databases in there. With some statement like

SELECT
  a.dbname
from
  meta.listofdatabases a
order by
  a.dbname  asc

you'll be able to offer a dropbox with the databases names easily.

Regards,

Bernd

You're right, JDBC will take only SQL statements. Isn't there a database catalogue table already in DB2 like MySQL's information_schema?

[hosesurya]

Thanks for all replies i got... =)

May I suggest a workaround: Add a table to DB UNIVERSITY1 and keep a list of your databases in there. With some statement like

SELECT
  a.dbname
from
  meta.listofdatabases a
order by
  a.dbname  asc

you'll be able to offer a dropbox with the databases names easily.

It's truly a good idea, and i very appreciate that... =)
But, I think in my cases that idea cannot be applied...
Because, i have some databases and anybody (who have access) can create a new database and alter any table (he or she wants) for some purposes.
So, i want to previews all the exist databases without need any extra works (like adding information into new table or update a file).
I'd found that in DB2 the information about databases is stored in Instances or nodes so, i'm still struggling to get this information trough Java...

You're right, JDBC will take only SQL statements. Isn't there a database catalogue table already in DB2 like MySQL's information_schema?

I still search for this too... I hope this database catalogue table is really exist...
If yes, it's very helpful for me to develop in more simplicity... =)
At least, i don't need to think hard =)

Thanks for the help and suggestions =)

Best Regards,
Hose Surya

[docdiesel]

You're right, JDBC will take only SQL statements. Isn't there a database catalogue table already in DB2 like MySQL's information_schema?

No, for there's no central database - at least in the instance and on LUW systems. The only way to discover the existing databases is through the DB2 administration server, but that one isn't talking JDBC.

Within the instances all databases are visible. I'd create a shell skript to discover them and write them into my central table, by crontab every 10 min within working hours; something like this (english Db2 version):

db2 connect to mydb
db2 list database db | grep alias | awk '{print $4;}' | xargs -i echo insert into my.table\(dbnames \(\'{}\'\) | db2 -v
 

Don't know if there's some central database on z/OS and i5 DB2 systems, but somehow I doubt it.

Regards,

Bernd

[hosesurya]

Thanks for all your concern... =)

But, with the help of my mentor Mr. Haris...
I know how to get a list of DB directories...
You can use the command:
db2cmd -c -w -i db2 list db directory | findstr /c:"Database alias" > DatabaseList.txt
this command can export the database alias line to "DatabaseList.txt" file.
But, maybe the exec() function cannot process the pipelining argument
"| findstr /c:"Database alias" > DatabaseList.txt"
So, i choose to simplify the command to:
"db2cmd -c -w -i db2 list db directory"

 
import java.io.*;
 
public class Surya
{
    public static void main( String[] args )
    {
        String cmdStrBuff, DBAlias;
        int n;
        try
        {
            //Process command to command shell
            Process cmdStrProc = Runtime.getRuntime().exec( "db2cmd -c -w -i db2 list db directory" );
            //Get the result from command
            DataInputStream cmdInpStream = new DataInputStream( cmdStrProc.getInputStream() );
            try
            {
                //Read the result line per line and store it in a String variable
                while( ( cmdStrBuff = cmdInpStream.readLine() ) != null )
                {
                    /*
                      Sometimes the result is just only a feedline
                      So, we must get real information.
 
                      ex:  Database alias                       = UNIVERSITY1
                            Database name                      = UNIV_1
                            Node name                            = UNIVERSITY
 
                      So, we must search the information we need,
                      I want to search Database alias.
                      If variable n return more than 0, than we got the alias!
                    */
                    n = cmdStrBuff.indexOf( "alias" );
                    if( n > 0 )
                    {
                        //Substring to get the DB alias name.
                        DBAlias = cmdStrBuff.substring( 40 );
                        //Show the DB alias name to the java console...
                        System.out.println(DBAlias);
                    }
                }
            }
            catch( IOException e )
            {
                System.exit( 2 );
            }
        }
        catch( IOException e1 )
        {
            System.err.println( e1 );
            System.exit( 1 );
        }
 
    }
}
 
 

i so thanks to Bernd and r035198x for the replies...
and i hope my answers can complete the Bernd's.
Bernd's answer is maybe suitable for *nix platform...
and mine for Windows platform...

Best Regards,
Hose Surya