Results 1 to 12 of 12
  1. #1
    Join Date
    Dec 2003
    Posts
    51

    Unanswered: select all tables in a database

    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

  2. #2
    Join Date
    Mar 2004
    Location
    Toronto, ON, Canada
    Posts
    513
    Quote Originally Posted by Pinto
    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.
    --
    Jonathan Petruk
    DB2 Database Consultant

  3. #3
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    SYSCAT.SYSTABLES has the list of tables, views, aliases, nicknames etc

    Cheers
    Sathyaram
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  4. #4
    Join Date
    Dec 2003
    Posts
    51
    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 ?

  5. #5
    Join Date
    Apr 2004
    Location
    Inside Intel
    Posts
    165

    Talking

    It is SYSIBM.SYSTABLES

    ~ Newbie

  6. #6
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    Sorry, I meant, SYSCAT.TABLES

    Cheers
    Sathyaram
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  7. #7
    Join Date
    Dec 2003
    Posts
    51
    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 ?

  8. #8
    Join Date
    Mar 2004
    Location
    Toronto, ON, Canada
    Posts
    513
    Quote Originally Posted by Pinto
    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.
    --
    Jonathan Petruk
    DB2 Database Consultant

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

  10. #10
    Join Date
    Mar 2004
    Location
    Toronto, ON, Canada
    Posts
    513
    Quote Originally Posted by ARWinner
    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.
    --
    Jonathan Petruk
    DB2 Database Consultant

  11. #11
    Join Date
    Dec 2003
    Posts
    51
    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

  12. #12
    Join Date
    Dec 2003
    Posts
    51
    Find the select query which list all the active database names in DB2 Database server ...

Posting Permissions

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