hi all ,

Can I take all the tables from a DB2 database 'sample' ? (Any sql select statement )

Can I take all the "database names" from the DB2 Database Server through java program ?

I am using the DB2 Database v8.1.3.132 ?
I am using the driver ( COM.ibm.db2.jdbc.net.DB2Driver ) to connect through java program .

-------------------------------------------------------------
Class.forName("COM.ibm.db2.jdbc.net.DB2Driver");
dbconn = DriverManager.getConnection("jdbc:db2://localhost:6789/sample","administrator","password");
-------------------------------------------------------------

please help me !!!!!

thanks
Pinto

What do you mean "take"?

To get a list of tables, do a SELECT TABSCHEMA, TABNAME FROM SYSCAT.TABLES.

I don't believe there's a way to get a database listing using Java.

SYSCAT.SYSTABLES has the list of tables, views, aliases, nicknames etc

Cheers
Sathyaram

thanx for the reply .

Yes , to display the list of tables ...
like
"select * from tab" ( in Oracle for list the table )
"select name from sysdatabase" ( in MSSQL to display all the databases )

I applied the query

"SELECT DBNAME,NAME FROM SYSIBM.SYSTABLESPACE"

but getting an error :
--------------------
DBA2191E SQL execution error.

com.ibm.db.DataException: A
database manager error occurred. : [IBM][CLI Driver][DB2/NT]
SQL0204N "SYSIBM.SYSTABLESPACE" is an undefined name.
SQLSTATE=42704
--------------------

How can I use this SYSCAT.SYSTABLES to achieve this ?

It is SYSIBM.SYSTABLES

~ Newbie

Sorry, I meant, SYSCAT.TABLES

Cheers
Sathyaram

Thanks a lot to sathyaram_s & dsusendran ...
Yeah , It worked .
But Is there any technique to get the list of databases in a DB2 Database Server
using java program ?

I don't think so... although the Control Center does it somehow, I'd imagine they have special hooks. I don't believe there's anything in the JDBC specs for it.

It can be done if the java app is launched from the CLP. (This is how th CC operates).

You can try something like this:

private javax.swing.JComboBox getDatabaseName() {
if (ivjDatabaseName == null) {
try {
ivjDatabaseName = new javax.swing.JComboBox();
ivjDatabaseName.setName("DatabaseName");
ivjDatabaseName.setBounds(249, 43, 124, 20);
// user code begin {1}
Runtime runTime;
Process child;
java.io.InputStreamReader inStream;
java.io.BufferedReader buffReader;
String inLine;
String lookValue;
String addDB;
int exitVal;

lookValue = new String(" Database alias = ");
runTime = Runtime.getRuntime();

System.out.println("List databases");
child = runTime.exec("DB2 LIST DATABASE DIRECTORY");

inStream = new java.io.InputStreamReader(child.getInputStream());
buffReader = new java.io.BufferedReader(inStream);
inLine = null;
System.out.println("Output:");
while ((inLine = buffReader.readLine()) != null) {
// System.out.println(inLine);
if (inLine.indexOf(lookValue) >= 0) {
addDB = new String(inLine.substring(lookValue.length()));
System.out.println("Found DB: " + addDB);
ivjDatabaseName.addItem(addDB);
}

}
exitVal = child.waitFor();
// user code end
} catch (java.lang.Throwable ivjExc) {
// user code begin {2}
// user code end
handleException(ivjExc);
}
}
return ivjDatabaseName;
}

This works for Version 8, it differs slightly for V7.

HTH

Andy

The Java Gods are frowning at that solution.

If you're going that route, you could also use a native call to the CLI layer, SQLDataSources() should do it.

This works only in the machines having db2server or db2client .
I can't use this runtime.exec-streaming method , bcos my java program is running in a different machine which have no db2 client on it .

I am using the net driver (COM.ibm.db2.jdbc.net.DB2Driver) in java program to get the connection .
Any other method to do this !!!!

thanks

Find the select query which list all the active database names in DB2 Database server ...