Sunday, November 22, 2015

NetBeans and DB2 again

So for various reasons (mainly the 64bit v 32bit problem with the native driver) I changed my DB2 JDBC driver to type 4, i.e. the Universal driver.
However, when I connect from NetBeans with an URL like jdbc:db2://localhost:50000/sample the schema would stay empty.
Nothing. Zip. Zilch. Zero.
Nada.
Quite some googling and debugging - mainly with a little java program like this:

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;


public class MainDB2 {
    
    private static final String URL = "jdbc:db2://localhost:50000/SAMPLE";
    private static final String USER = "roman";
    private static final String PASSWORD = "pwd";
    private static final String SCHEMA = "roman";
    
  public static void main(String[] args) throws Exception {
        Class.forName("com.ibm.db2.jcc.DB2Driver");
        Connection conn = DriverManager.getConnection(URL, USER, PASSWORD);
        try {
            DatabaseMetaData dmd = conn.getMetaData();
            ResultSet rs = dmd.getTables(null, SCHEMA, "%", new String[] { "TABLE" });
            try {
                while (rs.next()) {
                    int count = rs.getMetaData().getColumnCount();
                    System.out.println("Column count: " + count);
                    int i;
                    for (i = 1; i <= count; i++) {
                        try {
                            System.out.println(rs.getString(i));
                        } catch (SQLException e) {
                            System.err.println("Exception reading column " + i);
                            e.printStackTrace();
                        }
                    }
                    System.out.println();
                }
            } finally {
                rs.close();
            }
        } finally {
            conn.close();
        }
    }
}



I was able to trace it back to an SQLCODE -443SQL0443N with diagnostic text "SYSIBM:CLI:-805". SQLSTATE=38553. Google this and you will get to here and learn that - again - a package was not bound, this time the db2schema.bnd file. Bind it as suggested in the article with the usual db2 bind db2schema.bnd blocking all grant public

and voila, NetBeans will find the schema.

Gets me every second year, it seams.