By sin on Jan 30, 2008
It was fun working with DB2 after having already experienced Oracle and mySQL. It started with the installation of DB2 on a Solaris 10 SPARC machine. I was interested in running a command-line installation but sadly it doesn't configure the database for you. Installation is quite easy and I easily sailed past that. I desperately needed some reference to configure the db2. Nevertheless, a search on google brought me to http://www.ldas.ligo-wa.caltech.edu/doc/db2/doc/html/installDB2.html. It is a good site which explains the necessary steps like user creation etc to configure the db2 (You might want to be little cautious as it discusses an old release of db2). It also lets u know how to create a sample database.
Once the configuration was done, I wanted to test the sample database access using my java code. I set the db2jcc4.jar ( found in the /opt/IBM/db2/V9.5/java) in the CLASSPATH and a sample application below worked.
public static void main(String args) throws Exception
String url = "jdbc:db2://HOST_NAME:50002/sample";
Connection conn = DriverManager.getConnection(url, "ldasdb", "ldasdb"
Statement stmt = conn.createStatement() ;
// Execute the query
ResultSet rs = stmt.executeQuery( "SELECT \* FROM staff" ) ;
// Loop through the result set
while( rs.next() )
System.out.println( rs.getString(1) ) ;
// Close the result set, statement and the connection
Nice! I am almost done and I need to use the existing SQLs to create a database and some tables. See how it goes below:
1. go to the installation path and run db2 for a db2 prompt ( use ldasdb for this)
2. create a database or use sample database mentioned in the page
db2 => create database vdtest
3. Connect to the database
db2=> connect to vdtest
4. Create a table
Now my old SQL fails because you need to mention in the primary key that it is not null. Well, I don't know if it was obvious or not but I did change the SQL to "UID VARCHAR(15) NOT NULL PRIMARY KEY" from "UID VARCHAR(15) PRIMARY KEY" and the table is created.
5. Inserting records
Once again I hit a roadblock here. But it turned out to be the column names following the table name in the INSERT statement. Workaround was to take the column names from the INSERT.
INSERT INTO USER_HOBBY VALUES ('TEST000','Art',1)
Didn't have the time to investigate how to insert only for a few select columns...Saving it for some other time