469,315 Members | 1,795 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,315 developers. It's quick & easy.

How to get Databases Name in DB2

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... =)
Feb 4 '08 #1
7 31429
r035198x
13,262 8TB
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
Expand|Select|Wrap|Line Numbers
  1. list database directory
command.
Feb 4 '08 #2
You do know how to connect to DB2 using JDBC right?
You could then try the
Expand|Select|Wrap|Line Numbers
  1. 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.
Feb 5 '08 #3
docdiesel
297 Expert 100+
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
Expand|Select|Wrap|Line Numbers
  1. SELECT
  2.   a.dbname
  3. from
  4.   meta.listofdatabases a
  5. order by
  6.   a.dbname  asc
you'll be able to offer a dropbox with the databases names easily.

Regards,

Bernd
Feb 5 '08 #4
r035198x
13,262 8TB
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
Expand|Select|Wrap|Line Numbers
  1. SELECT
  2.   a.dbname
  3. from
  4.   meta.listofdatabases a
  5. order by
  6.   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?
Feb 5 '08 #5
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
Expand|Select|Wrap|Line Numbers
  1. SELECT
  2.   a.dbname
  3. from
  4.   meta.listofdatabases a
  5. order by
  6.   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
Feb 6 '08 #6
docdiesel
297 Expert 100+
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):

Expand|Select|Wrap|Line Numbers
  1. db2 connect to mydb
  2. db2 list database db | grep alias | awk '{print $4;}' | xargs -i echo insert into my.table\(dbnames \(\'{}\'\) | db2 -v
  3.  
Don't know if there's some central database on z/OS and i5 DB2 systems, but somehow I doubt it.

Regards,

Bernd
Feb 6 '08 #7
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"

Expand|Select|Wrap|Line Numbers
  1.  
  2. import java.io.*;
  3.  
  4. public class Surya
  5. {
  6.     public static void main( String[] args )
  7.     {
  8.         String cmdStrBuff, DBAlias;
  9.         int n;
  10.         try
  11.         {
  12.             //Process command to command shell
  13.             Process cmdStrProc = Runtime.getRuntime().exec( "db2cmd -c -w -i db2 list db directory" );
  14.             //Get the result from command
  15.             DataInputStream cmdInpStream = new DataInputStream( cmdStrProc.getInputStream() );
  16.             try
  17.             {
  18.                 //Read the result line per line and store it in a String variable
  19.                 while( ( cmdStrBuff = cmdInpStream.readLine() ) != null )
  20.                 {
  21.                     /*
  22.                       Sometimes the result is just only a feedline
  23.                       So, we must get real information.
  24.  
  25.                       ex:  Database alias                       = UNIVERSITY1
  26.                             Database name                      = UNIV_1
  27.                             Node name                            = UNIVERSITY
  28.  
  29.                       So, we must search the information we need,
  30.                       I want to search Database alias.
  31.                       If variable n return more than 0, than we got the alias!
  32.                     */
  33.                     n = cmdStrBuff.indexOf( "alias" );
  34.                     if( n > 0 )
  35.                     {
  36.                         //Substring to get the DB alias name.
  37.                         DBAlias = cmdStrBuff.substring( 40 );
  38.                         //Show the DB alias name to the java console...
  39.                         System.out.println(DBAlias);
  40.                     }
  41.                 }
  42.             }
  43.             catch( IOException e )
  44.             {
  45.                 System.exit( 2 );
  46.             }
  47.         }
  48.         catch( IOException e1 )
  49.         {
  50.             System.err.println( e1 );
  51.             System.exit( 1 );
  52.         }
  53.  
  54.     }
  55. }
  56.  
  57.  
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
Feb 13 '08 #8

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

reply views Thread by Hans Forbrich | last post: by
reply views Thread by Hans Forbrich | last post: by
reply views Thread by Cara | last post: by
1 post views Thread by Prakash RudraRaju | last post: by
2 posts views Thread by Jonathan Villa | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by suresh191 | last post: by
reply views Thread by harlem98 | last post: by
1 post views Thread by Geralt96 | last post: by
reply views Thread by harlem98 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.