Results 1 to 12 of 12
  1. #1
    Join Date
    Mar 2006
    Posts
    6

    Unanswered: List of all databasenames

    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)

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    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

  3. #3
    Join Date
    Mar 2006
    Posts
    6
    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

  4. #4
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    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

  5. #5
    Join Date
    Mar 2006
    Posts
    6
    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

  6. #6
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    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.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  7. #7
    Join Date
    Mar 2006
    Posts
    6

    Db2 Api

    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

  8. #8
    Join Date
    Mar 2006
    Posts
    6

    Db2 Api

    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.

  9. #9
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by holger winkler
    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
    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.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  10. #10
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    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
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  11. #11
    Join Date
    Mar 2006
    Posts
    6
    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

  12. #12
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •