Joachim Selke’s Blog

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 come up with a better solution in time.

ALTER TABLE tablename ADD COLUMN id INTEGER NOT NULL WITH DEFAULT 0
ALTER TABLE tablename ALTER COLUMN id GENERATED BY DEFAULT AS IDENTITY
REORG TABLE tablename
UPDATE tablename SET id = DEFAULT
ALTER TABLE tablename ALTER COLUMN id SET PRIMARY KEY

EDIT: I tested this code with DB2 9.7, but it should work on earlier versions of 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 Center, which takes some time. I just found out that there is an easier way to get at least a short explanation of what an error code means. For example, to look up the error code -161, you can use the following SQL query:

VALUES SQLERRM(-161)

Result:

1
------------------------------------------------------------------------------------------------------
SQL0161N  The resulting row of the insert or update operation does not conform to the view definition.

Nice.

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 1):

    WITH a AS (SELECT a.application_handle, a.system_auth_id, a.application_name, a.client_wrkstnname, a.workload_name, a.workload_occurrence_state, b.activity_id, b.uow_id, b.local_start_time, b.activity_state, b.activity_type, b.total_cpu_time, b.rows_read, b.rows_returned, b.query_cost_estimate, b.direct_reads, b.direct_writes
                 FROM TABLE(wlm_get_service_class_workload_occurrences_v97(NULL, NULL, NULL)) a
                      LEFT JOIN TABLE(wlm_get_workload_occurrence_activities_v97(NULL, NULL)) b ON a.application_handle = b.application_handle),
         b AS (SELECT t.*
                 FROM TABLE(mon_get_activity_details(FOR EACH ROW OF (SELECT application_handle, uow_id, activity_id, NULL FROM a WHERE activity_id > 0))) t),
         c AS (SELECT x."activity_id" activity_id, x."stmt_text" stmt_text
                 FROM b,
                      XMLTABLE('$m/db2_activity_details' PASSING XMLPARSE(DOCUMENT b.details) AS "m" COLUMNS "activity_id" INTEGER PATH 'activity_id', 
                                                                                                             "stmt_text" VARCHAR(1024) PATH 'stmt_text') x)
  SELECT a.*, TRANSLATE(c.stmt_text, ' ', CHR(10)) stmt_text
    FROM a
         LEFT JOIN c ON a.activity_id = c.activity_id
   WHERE stmt_text IS NULL OR SUBSTR(stmt_text, 1, 40) <> 'WITH a AS (SELECT a.application_handle, '
ORDER BY application_handle, a.activity_id

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 to re-create the whole table. However, I was surprised to see that there is a simple admin procedure in DB2 for doing what I wanted (see ADMIN_MOVE_TABLE). For example, to move the table myschema.mytable to data tablespace mydatatablespace, index tablespace myindextablespace, and LOB tablespace mylobtablespace, just run the following SQL statement:

CALL SYSPROC.ADMIN_MOVE_TABLE('myschema', 'mytable', 'mydatatablespace', 'myindextablespace', 'mylobtblespace', NULL, NULL, NULL, NULL, NULL, 'MOVE')

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.