Category Archives: DB2

Where to Download the DB2 JDBC driver

When you want to access a DB2 database from Java, you usually need IBM’s JDBC driver for DB2. In the past, I (and some other people I know) always had trouble in finding out where to download the recent version … Continue reading

Posted in DB2 | 2 Comments

DB2: How to Add an Identity Column to an Existing Table

Adding generated key columns to existing tables seems to be complicated in DB2. The following code adds a new generated primary key id to the table tablename. Granted, the code is ugly, but it works. I was not able to … Continue reading

Posted in DB2 | 5 Comments

How to look up SQL error codes quickly in DB2

Quite regularly DB2 complains about my SQL statements and usually returns a cryptic error message including an even more cryptic SQL error code. What I normally do in those situations is looking up the error code in the DB2 Information … Continue reading

Posted in DB2 | 2 Comments

What SQL statements are currently running on my DB2 database server?

To see some detailed info about which users are currently connected to your DB2 database server (including the SQL statements they are running), you can use the following query (tested with DB2 9.7, fixpack 3a):     WITH a AS (SELECT a.application_handle, … Continue reading

Posted in DB2 | 6 Comments

How to move a table to a different tablespace in DB2

Recently I had to move a table of some DB2 database to a different tablespace (building an index required a larger page size). At first, I suspected changing tablespaces to be one of those complicated operations that essentially require you … Continue reading

Posted in DB2 | 14 Comments

Computing the Levenshtein Distance with DB2

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 … Continue reading

Posted in DB2 | Leave a comment

Data Movement in DB2

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 … Continue reading

Posted in DB2 | Leave a comment

Grant Schema-Wide SELECT Permission in DB2

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, … Continue reading

Posted in DB2 | 6 Comments

TRUNCATE TABLE in DB2 LUW

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 … Continue reading

Posted in DB2 | 5 Comments