How to compare CLOBs in DB2 with a UDF

Although sometimes it might be less than desirable to do CLOB comparison in SQL statements, suppose you need to write a SELECT that compares two CLOB fields in the where clause:

SELECT COUNT(*) FROM tableA, tableB WHERE tableA.clob = tableB.clob

Seems easy enough, except when you try to run it, DB2 will make a sad face:

SQL0401N The data types of the operands for the operation “=” are not compatible or comparable. SQLSTATE=42818

A few solutions come to mind:

• If you are writing an external application, you can do comparison in the application code – retrieve CLOBs, pass them to a method, get a result, carry on. This is kinda inefficient for remote applications (network overhead) plus not elegant, considering the extra logic you need to put in the application;
• You can use an additional column in both of the tables to store corresponding CLOB’s hash, update hash whenever CLOB value has changed, and use the value of the hash field in the comparison operations. This approach will obviously fail if you rely on an exact match since hash values may collide;
• You can use DBMS_LOB module that provides a COMPARE function. With this function, the query in question becomes:

  "SELECT COUNT(*) from tableA, tableB WHERE DBMS_LOB.COMPARE(tableA.clob, tableB.clob) = 0"

  This would be my solution of choice if not for the fact that it’s only available as of DB2 9.7;

• Write a UDF that will do comparison for you and will essentially replace the missing COMPARE function from DBMS_LOB module.

I had to deal with DB2 9.5 and didn’t have much options, so I decided to go with a UDF. Here’s how it worked out:

1. First, let’s lay down a few rules. In my case, two CLOBs were considered equal if:

• neither of the CLOBs was NULL;
• sizes of the two CLOBs were equal;
• pairs of characters at the corresponding positions in both CLOBs matched.

Further more, the function was to return 1 if two CLOBs were equal and 0 in all other cases.

2. Here is a Java class with a single method that solves the problem:

import java.io.IOException;
import java.util.Arrays;
import COM.ibm.db2.app.Clob;
import COM.ibm.db2.app.UDF;

public class ClobCompare extends UDF {

    /** Returns 1 if the both CLOBs are the equal, and 0 otherwise. */
    public void clobsEqual(COM.ibm.db2.app.Clob clobA, COM.ibm.db2.app.Clob clobB, int result) throws Exception{

        if (clobA == null || clobB == null){
            set(3, 0);
            return;
        }

       char[] clobCharArrayA = null;
       char[] clobCharArrayB = null;

       // Allocate space for CLOB arrays.
       try {
           clobCharArrayA = new char[(new Long(clobA.size())).intValue()];
           clobCharArrayB = new char[(new Long(clobB.size())).intValue()];
       } catch (IOException e){
           System.out.println(e.getMessage());
       }

       // Read CLOBs.
       try {
          clobA.getReader().read(clobCharArrayA);
          clobB.getReader().read(clobCharArrayB);
       } catch (IOException e){
           System.out.println(e.getMessage());
       }

       // Compare.
       if (Arrays.equals(clobCharArrayA, clobCharArrayB)){
          set(3, 1);
       } else {
          set(3, 0);
       }
    }
}

By the way, since it’s a UDF, you could handle exceptions better (namely, by signaling SQLSTATE) although I did not do it for the sake of keeping this example short.

3. Now we need to compile the code and drop the class file somewhere DB2 will be able to find it:

/opt/ibm/db2/V9.5/java/jdk64/bin/javac ClobCompare.java
cp ClobCompare.class /home/db2inst1/sqllib/function/

4. Almost there, just need to create a function within the specific database:

CONNECT DB TEST;

CREATE FUNCTION clobsEqual(a CLOB(1048576), b CLOB(1048576))
RETURNS INTEGER
LANGUAGE Java
EXTERNAL NAME 'ClobCompare!clobsEqual'
FENCED
THREADSAFE
NO SQL
NOT NULL CALL
NO EXTERNAL ACTION
DISALLOW PARALLEL
PARAMETER STYLE db2general;

5. Let’s check if it works:

db2 "values clobsEqual(clob('abc'), clob('abc'))"

If you did everything right, you will see a ’1′ returned. If DB2 dies, make sure you put class in the right folder and check the names of the class / method if you did any changes. Also, if you can’t find the problem right away, try looking in db2diag.log for more info (located in ~/sqllib/db2dump by default).

5. As a side note, if you recompile your code, you do not need to recreate the function. To make sure DB2 uses the latest compiled code available it is sufficient to do this:

db2 "call sqlj.refresh_classes()"

while connected to the database.

Assuming we’ve completed all of the above steps, we can now rewrite our query as:

"SELECT COUNT(*) from tableA, tableB equalsClob(tableA.clob, tableB.clob) = 1"

Enjoy!