Merter Sualp's Weblog

A few months ago, I was requested to store and retrieve a text file which can reach to sizes of 40-50 Kilobytes. I was to use our DB2 database for those operations. The VARCHAR type is limited to 32Kb. Because of that limitation, I need to use Character Large Object (CLOB) type to store the file. The table creation process becomes this:

CREATE TABLESPACE <TABLE_SPACE>
 IN <DATABASE_NAME>
 USING STOGROUP <STORAGE_GROUP>;
 
CREATE LOB TABLESPACE <LOB_TABLE_SPACE>
 IN <DATABASE_NAME>
 USING STOGROUP <STORAGE_GROUP>;
 
CREATE TABLE <TABLE_NAME> (
 "ID" DECIMAL(19 , 0) NOT NULL GENERATED BY DEFAULT AS IDENTITY (
 NO MINVALUE 
 NO MAXVALUE 
 NO CYCLE
 CACHE 20
 NO ORDER ), 
 "DATA" CLOB(1048576) FOR SBCS DATA WITH DEFAULT NULL, 
 PRIMARY KEY("ID")
 )
 IN <DATABASE_NAME>.<TABLE_SPACE>
 AUDIT NONE
 DATA CAPTURE NONE 
 CCSID EBCDIC;
 
CREATE AUXILIARY TABLE <LOB_TABLE_NAME>
 IN <DATABASE_NAME>.<LOB_TABLE_SPACE>
 STORES <TABLE_NAME>
 COLUMN "DATA";

I put a 1MB limit but it is artificial. You can change that number however you see fit.

I was thinking to develop the simple application with Visual Basic but hit technical problems. I should use the getChunk() and appendChunk() methods but the CLOB field in DB2 seems not to be chunkable. It was weird. AT least I could not figure out what the underlying problem was. Therefore, I decided to code with Java.

My first task was to connect to DB2. To accomplish that, I imported the DB2 JDBC drivers from db2jcc4.jar. You can grab yours from here. So my code to connect to DB2 is like:

import com.ibm.db2.jcc.*;
import java.sql.*;
private static boolean connectToDb() {
  try {
    Class.forName("com.ibm.db2.jcc.DB2Driver");
    String url = "jdbc:db2://your.db2.host:port/node:";
    url += "user=user;password=password;";
    this.connection = DriverManager.getConnection(url);
    return true;
   } catch (ClassNotFoundException e) {
     e.printStackTrace();
   } catch (SQLException e) {
     e.printStackTrace();
   }
   return false;
 }

The db2jcc4.jar is enough to code and satisfy the compile time operations, but it is not enough to run on a client machine. That machine must have the necessary license jar files also. They can be obtained from your database administrators. For my case, they are named as db2jcc_javax.jar and db2jcc_license_cisuz.jar.

The second task is to read the text file and convert it to a CLOB. Here one important thing is your character set. My file was in Turkish so I used the “Cp1254”. This situation forced me to use the InputStreamReader class. My code is as follows:

private static String readFile() throws FileNotFoundException, IOException {
  FileInputStream fis = new FileInputStream(fileName);
  InputStreamReader isr = new InputStreamReader(fis,
  Charset.forName("Cp1254"));
  BufferedReader br = new BufferedReader(isr);
  String nextLine = "";
  StringBuffer sb = new StringBuffer();
  while ((nextLine = br.readLine()) != null) {
    sb.append(nextLine);
    sb.append(System.getProperty("line.separator"));
  }
  br.close();
  String clobData = sb.toString();
  return clobData;
}

I simply read the text file line by line and generate a String holding the whole file. How does it become a CLOB then? This code shows the CLOB creation and inserting it into DB2.

private static boolean insertTextFileIntoClob() {
  try {
    Clob clob = this.connection.createClob();
    String clobData = readFile();
    clob.setString(1, clobData);
    PreparedStatement pstmt = null;
    StringBuilder sb = new StringBuilder();
    sb.append("INSERT INTO ");
    sb.append(TABLE_NAME);
    sb.append(" (DATA) ");
    sb.append(" VALUES(?) ");
    pstmt = this.connection.prepareStatement(sb.toString());
    pstmt.setClob(1, clob);
    pstmt.executeUpdate();
    return true;
  } catch (Exception e) {
    e.printStackTrace();
  }
  return false;
}

I do not need to use PreparedStatement. Statement will also be fine.

When DB2 inserts a CLOB, it uses both the main table and the auxiliary table. To retrieve the real text data, we are to select the CLOB data and convert it to text again.

private static ResultSet selectClob() {
  try {
    StringBuilder sb = new StringBuilder();
    sb.append(" select data from ");
    sb.append(TABLE_NAME);
    Statement stmt = this.connection.createStatement();
    ResultSet rs = stmt.executeQuery(sb.toString());
    return rs;
  } catch (Exception e) {
    e.printStackTrace();
    return null;
  }
}

private static boolean clobToFile(ResultSet rs) {
  try {
    if (!rs.next())
      return false;
    Writer fw = new BufferedWriter(new OutputStreamWriter(
      new FileOutputStream(fileName), "Cp1254"));
    Clob clob = rs.getClob("DATA");
    Reader reader = clob.getCharacterStream();
    BufferedReader br = new BufferedReader(reader);
    String line;
    while (null != (line = br.readLine())) {
      StringBuilder sb = new StringBuilder();
      sb.append(line);
      sb.append(System.getProperty("line.separator"));
      fw.write(sb.toString());
    }
    br.close();
    fw.close();
    return true;
  } catch (SQLException e) {
    e.printStackTrace();
  } catch (IOException e) {
    e.printStackTrace();
  }
  return false;
}

I, again, generate the file by append all lines tail to tail. The character set is also important.

Now it is time to compile and deploy it to the user’s machine. My target machine uses 1.6. For the first time, I compiled with 1.7 and got this error while running. It was also a 32-bit machine. I decided to compile it with 1.6 32-bit JDK. Most probably, this is an overkill since I should only do

java -target 1.6 Code.java

but I wanted to be on the safe side. After creating the directory structure according to my package name, I compiled and run my code with the following commands:

The .class file(s) are ready and I am not required to generate exe files. So an executable jar is the way to go. Running this in terminal creates the executable jar.

jar -cf code.jar Code.class

The critical point is that, I need jar files, db2jcc4.jar, db2jcc_javax.jar and db2jcc_license_cisuz.jar namely, other than my own code to run properly. In Java, it is a little bit problematic. The solution I employed is to put those three files on my target machine. Furthermore, I add the line below inside the META-INF/manifest.mf file in the executable jar:

Class-Path: db2jcc4.jar db2jcc_javax.jar db2jcc_license_cisuz.jar

This enabled to use the classes in all those jar files. That manifest file consists both the class path and the main class which hosts the main(String[] args) method. We can define it by:

Main-class: <package-name>.<class-name>

After all those efforts, we are ready to run the application. In the terminal we run the following command and it is done.

java -jar code.jar <options>

Hope my experience can help you.

Tags: CLOB, DB2, Java

This entry was posted on 02/01/2016 at 00:32 and is filed under Uncategorized. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.