Joachim Selke’s Blog

The Levenshtein distance is a standard tool in approximate string matching. Despite its popularity, DB2 does not provide a build-in function for computing Levenshtein distances. What seems to be most similar in spirit is DB2′s DIFFERENCE function, which measures the similarity between two strings using the Soundex algorithm.

However, it’s not difficult to create a user-defined function (UDF) that computes Levenshtein distances. One simply can integrate the Java implementation provided by the Apache Commons Lang library into DB2. The method we need is getLevenshteinDistance, located in the class org.apache.commons.lang.StringUtils. Here is a short guide:

1. Download the binary package of Apache Commons Lang and extract it. Copy the contained JAR file (commons-lang-2.4.jar) somewhere onto your DB2 server.
2. Use DB2′s command-line tool to connect to your database:

   
   db2 CONNECT TO yourdb

3. Deploy the JAR file into DB2:

   
   db2 "CALL sqlj.install_jar('file:/path/to/the/jarfile/commons-lang-2.4.jar', 'commons-lang-2_4')"
   

4. Create a corresponding user-defined function:
   

   
   db2 "CREATE FUNCTION yourschema.levenshtein(s VARCHAR(255), t VARCHAR(255)) RETURNS INTEGER EXTERNAL NAME 'commons-lang-2_4:org.apache.commons.lang.StringUtils.getLevenshteinDistance' LANGUAGE JAVA PARAMETER STYLE JAVA NO SQL NO EXTERNAL ACTION ALLOW PARALLEL"
   

5. Test it:

   
   db2 "VALUES yourschema.levenshtein('test', 'taste')"
   

   This should return the value 2.

6. Disconnect:

   
   db2 DISCONNECT yourdb
   

Some time ago, I wanted to backup a complete schema of my DB2 database (version 9.5) to a file such that I am able to restore it when needed. Up to then, I always used the db2move utility for such tasks (using the EXPORT and IMPORT actions, respectively). Unfortunately, the schema I had to deal with contained some XML columns and the db2move tool made some trouble when I tried to reimport the data from the created file into a different database. I don’t know exactly what error messages I got but the problem seemed to be related to the fact that the IMPORT command options CREATE and REPLACE_CREATE are deprecated since DB2 9.5. In other words, db2move has problems in creating tables from the backup files created by db2move using the EXPORT action.

Well, what’s the way IBM now wants data to be backuped and restored? I don’t know exactly, but here is how I solved the problem:

• First, save the schema’s DDL statements to a file using the db2look utility:
  db2look -d <em>dbname</em> -z <em>schemaname</em> -e -xs -x -o <em>outfile.sql</em>
• Second, backup all data using the db2move’s EXPORT action:
  db2move <em>dbname</em> EXPORT -sn <em>schemaname</em>

To restore the schema, I did the following:

• First, restore all tables and permissions:
  db2 -tvf <em>outfile.sql</em>
  Note that you might need to modify <em>outfile.sql</em> before doing this (e.g. in case you want to restore the schema in a different database).
• Second, restore the data:
  db2move <em>dbname</em> LOAD

That’s it.

For data movement tasks different from the one I described, there might be better-suited tools available. The following two pages give an overview of data movement in DB2:

• Data movement options available in DB2 V9.5
• Fast and easy data movement using DB2′s LOAD FROM CURSOR feature

When managing user permissions in DB2 (version 9.5), sometimes one wants to grant the SELECT permission to all tables within a given schema. Unfortunately, this isn’t possible; only the schema permissions ALTERIN, CREATEIN, and DROPIN can be granted in DB2, which is not what we want.

A workaround is to collect a list of all tables within the given schema and grant the SELECT permission to each of them. On Linux/UNIX systems, this can be done as follows using DB2′s command line processor:

DBNAME=<em>dbname</em>
SCHEMANAME=<em>schemaname</em>
USERNAME=<em>username</em>

db2 "CONNECT TO $DBNAME"
TABLES=`db2 -x "SELECT tabname FROM syscat.tables WHERE tabschema=UPPER('$SCHEMANAME')"`
for TABLENAME in $TABLES;
  do db2 "GRANT SELECT ON $SCHEMANAME.$TABLENAME TO USER $USERNAME"
done
db2 "DISCONNECT $DBNAME"

The unavoidable disadvantage of this approach is that you have to run the above script each time new tables are added within the given schema.

The SQL standard defines a TRUNCATE TABLE command, which deletes all data contained in a table. Unfortunately, DB2 for Linux, UNIX, and Windows does not support this command yet (version 9.5). Strangely, it is supported in DB2 for z/OS since version 9.1.

UPDATE: Version 9.7 finally introduced the TRUNCATE TABLE command.

Below you find three ways to simulate the behavior of TRUNCATE TABLE tablename on DB2 LUW.

Option 1: ALTER TABLE

ALTER TABLE tablename ACTIVATE NOT LOGGED INITIALLY WITH EMPTY TABLE

The documentation describes this operation as follows:

Causes all data currently in table to be removed. Once the data has been removed, it cannot be recovered except through use of the RESTORE facility. If the unit of work in which this alter statement was issued is rolled back, the table data will not be returned to its original state.

When this action is requested, no DELETE triggers defined on the affected table are fired. Any indexes that exist on the table are also deleted.

Option 2: IMPORT

IMPORT FROM /dev/null OF DEL REPLACE INTO tablename

In contrast to Option 1, this operation is fully recoverable. Note that on Windows systems, you have to replace /dev/null by NUL.

Option 3: DELETE

DELETE FROM tablename

This usually is very slow, so options 1 and 2 are to be preferred.