Hello,

my DB2-Version is 8.1.7.
My Problem: I need for my Java-Programm a list of all databasenames to view there in a combo box. So I can connect to one of there in a second step.

In the db2 command window I can type 'db2 list db directory' to get an output with all databases. For the programm I need an other solution. Is there a table with all names, which I can reed? Or a sql statement? Or something other?

Greetings
Holger
(Germany, so sorry for my english)

Well using a table to get the list of databases is impossible since you need a connection to a database to get access to the table and how are you going to connect to the database if you do not know its name? An SQL statement has the same problem. This is what you do (you will need to modify it to suit your needs):

private void fillInList(){
try {

Runtime runTime;
Process child;
java.io.InputStreamReader inStream;
java.io.BufferedReader buffReader;
String inLine;
String lookValue;
String addDB,curDB;
int exitVal,i;

// lookValue = new String(" Database alias = ");
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);
for (i=0; i<this.getItemCount(); ++i)
{
curDB = (String) this.getItemAt(i);
if (curDB.compareTo(addDB) > 0) break;
}
this.insertItemAt(addDB,i);
}

}
exitVal = child.waitFor();
this.setSelectedIndex(0);
// user code end
} catch (java.lang.Throwable exception) {
// user code begin {2}
// user code end
System.out.println("--------- UNCAUGHT EXCEPTION in ---------");
exception.printStackTrace(System.out);
}
}

Note: The value of lookValue is different depending on the version of DB2. Even the FixPack level may make it different.

Also this will only work if the java program is launched either from the CLP or with CLP environment setup properly.

HTH

Andy

Hi Andy,

thanks for Your solution. My Idea was similiar. I wrote the db output into a file where I can read the lines. But yours is better.
I wonder that there are no tables, for example in syscat or sysibm, where all the databasenames are saved. Particularly I have only access to a database which is in the catalog.

Greetings
Holger

Holger,
I think I can say with total certainty that the reason they are not in a system catalog table is that the database and node directories are client constructs. The client needs to know how to connect to a database before it can connect to it. So how could you connect to a database and then query it on how to connect to it (and other databases). If that still does not clear it up for you, try this analogy. Try calling me up on the phone. If I do not give you any information on how to do that, it would be impossible. You need that info ahead of time. So to connect to a database, you have a, local to the client, directory of the servers (nodes) and databases.

HTH

Andy

Hi Andy,

I belive that. For JDBC or SQL I must connect to a database before I can retrieve any information from the database tables.
But the control center of the DB2? Does it goes also this complicated way to retrieve the database names to show all in the window?

Greetings
Holger

The DB2 Control Center uses the local node and db catalog to determine what databases can be connected to. If the database is on a remote server (not on same machine as the client) the remote databases must be cataloged by the user (or use the Client Congfiguration Assistant) before they can be seen by the Control Center. However, it is feasible to create a client configuration file and distribute it to clients via software so that they can all see the same remote databases.

One other option is to use the "discover" function to actively search servers and databases on your network and automatically catalog them on the remote client. However, this requires that the database servers be configured to allowed such remote discovery. Allowing such discoveries is considered a security problem for some installations, so don't expect everyone to allow it.

Hi,

I have seen that there is a API for the DB2 for programming in C++. The following text stands in the documentation:

DB2 Application Programming Interfaces
Your applications may need to perform some database administration tasks, such as creating, activating, backing up, or restoring a database. DB2(R) provides numerous APIs so you can perform these tasks from your applications, including embedded SQL and DB2 CLI applications. This enables you to program the same administrative functions into your applications that you can perform using the DB2 server administration tools available in the Control Center.

Is there a possibility to retrieves the database names? Java can invoke a C-Methode.

Thanks

A further hint:
On the ibm homepage
http://publib.boulder.ibm.com/infoce...-dbcmt-cbl.htm

is a cobol example that uses the following DB2 APIs
** DB2 APIs USED:
** db2gDbDirOpenScan -- OPEN DATABASE DIRECTORY SCAN
** db2gDbDirGetNextEntry -- GET NEXT DATABASE DIRECTORY ENTRY
** db2gDbDirCloseScan -- CLOSE DATABASE DIRECTORY SCAN

Later in the program there are the lines:

if SQL-DBNAME-N equal "SAMPLE "
go to Change-Comment.

The DB2 Control Center can only access remote databases that are first catalogued on the local client where the Control Center is running. This requires an entry in the local node directory and db directory.

As I noted earlier, it is theorectically possbile to use the discover function to automatically find and catalogue remote databases, but this depends on whether the reomote database allows such discovery, and I also find that it doesn't work very well even when discovery is allowed.

Holger,
You have told about the technical requirement in the original post ..

But what is that you are really trying to achieve? Are you developing a query tool or else .... ???

This info will allow others to suggest solutions on those lines ...

Thanks

Sathyaram

Hi,

I want to write a java program which shows the possible db2 databases, connect to one and execute a sql-statement. Not more. I search for the best solution.

Thanks

Holger,
You have just two options. The one already posted, and to use JNI, which from my understanding you also need to do something in C/C++.

Andy