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 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')

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 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
    
Posted in DB2 | Leave a comment

How to Prevent Adobe Reader’s RPM File from Installing the Browser Plugin

Adobe provides an RPM file for installing the Linux version of Adobe Reader. Unfortunately, this RPM file automatically installs the Reader’s browser plugin, which I find quite annoying. I created a simple snippet of Bash code that modifies Adobe’s original RPM file by uncommenting the line that installs the browser plugin. It is designed for (and tested on Fedora 10 with) version 9.1.0-1 of the AdobeReader_enu package (file name AdbeRdr9.1.0-1_i486linux_enu.rpm). Just open a shell, change to the directory where you downloaded Adobe’s RPM to, and run the following code. If you don’t have the rpmrebuild tool installed yet, you will need to do so before running the code.

RPM_ORG=AdbeRdr9.1.0-1_i486linux_enu.rpm
NEWRELEASE="1new"

PATCHDIR=`mktemp -d`
export RPMREBUILD_TMPDIR=`mktemp -d`

cat <<"EOF" >$PATCHDIR/install_browser_plugin.patch
--- post.1
+++ post.1.patched
@@ -224,1 +224,1 @@
-"$ReadInstallDir/Adobe/Reader9/Browser/install_browser_plugin" -silent -global -user "$ReadInstallDir" >/dev/null 2>&1
+#"$ReadInstallDir/Adobe/Reader9/Browser/install_browser_plugin" -silent -global -user "$ReadInstallDir" >/dev/null 2>&1
EOF

setarch i386 rpmrebuild --release=$NEWRELEASE --change-files="patch $RPMREBUILD_TMPDIR/work/post.1 $PATCHDIR/install_browser_plugin.patch" --directory=. --package $RPM_ORG
rm -f $RPMREBUILD_TMPDIR
rm -rf $PATCHDIR

The new RPM will be saved to the subdirectory i486 of the current directory.

To install the browser plugin afterwards, run /opt/Adobe/Reader9/Browser/install_browser_plugin as root. To remove the browser plugin, delete the file /usr/lib/mozilla/plugins/nppdf.so.

Posted in RPMs | 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 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:

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, 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.

Posted in DB2 | 6 Comments

Using Fedora’s OpenJDK with MATLAB

Since MATLAB requires Java for some of its features (e.g. for displaying the GUI or running Java programs within MATLAB), it ships with Sun’s Java Runtime Environment (JRE). Most people already have installed a JRE on their system that usually is newer than the one MATLAB comes with. Therefore, it seems to be a good idea to let MATLAB make use of the installed JRE instead of its own. This easily can be done by setting the environment variable MATLAB_JAVA. That’s the theory.

In practice, changing MATLAB’s JRE makes some trouble—at least when you are running Linux. Currently, I’m running MATLAB R2008b (aka 7.7) on Fedora 10 and notice some problems with MATLAB’s own JRE: First, printing does not work. Second, when starting MATLAB I get some strange error (locking assertion failure), which luckily does not seem to have any further consequences. Therefore, I decided to switch to OpenJDK, which comes with Fedora. Setting MATLAB_JAVA to /usr/lib/jvm/jre should do the job. Unfortunately, with this setting MATLAB fails to start, giving me the following error message:

Unable to initialize com.mathworks.mwswing.MJStartup
Fatal Error on startup: Failure loading desktop class

Although I spent some time in debugging this issue, I still have no clue what’s going wrong. My hope is that this issue will be resolved in the next release of MATLAB.

And indeed, MATLAB R2009a (aka 7.8) can be started using OpenJDK. However, MATLAB’s help browser displays only empty pages, which actually are not very helpful. When opening the help browser I get the following Java error:

Exception in thread "Loader for _view_0" java.lang.VerifyError: class org.mozilla.javascript.IdScriptable overrides final method getAttributes.(Ljava/lang/String;Lorg/mozilla/javascript/Scriptable;)I
at java.lang.ClassLoader.defineClass1(Native Method)
at java.lang.ClassLoader.defineClass(ClassLoader.java:637)
at java.security.SecureClassLoader.defineClass(SecureClassLoader.java:142)
at java.net.URLClassLoader.defineClass(URLClassLoader.java:277)
at java.net.URLClassLoader.access$000(URLClassLoader.java:73)
at java.net.URLClassLoader$1.run(URLClassLoader.java:212)
at java.security.AccessController.doPrivileged(Native Method)
at java.net.URLClassLoader.findClass(URLClassLoader.java:205)
at java.lang.ClassLoader.loadClass(ClassLoader.java:323)
at sun.misc.Launcher$AppClassLoader.loadClass(Launcher.java:294)
at java.lang.ClassLoader.loadClass(ClassLoader.java:268)
at java.lang.ClassLoader.loadClassInternal(ClassLoader.java:336)
at java.lang.Class.forName0(Native Method)
at java.lang.Class.forName(Class.java:186)
at ice.util.Defs.getClass(OEAB)
at ice.util.Defs.newClassInstance(OEAB)
at ice.storm.DefaultFactory.createScripter(DefaultFactory.java:91)
at ice.storm.StormBase.getScripter(StormBase.java:1590)
at ice.pilots.html4.ThePilot.doScriptEval(ThePilot.java:405)
at ice.pilots.html4.DOMBuilder.loadIfGivenExternalScript(DOMBuilder.java:1003)
at ice.pilots.html4.DOMBuilder.endElement(DOMBuilder.java:944)
at ice.dombase.LexBase.endElement(OEAB)
at ice.dombase.LexBase.char_buf(OEAB)
at ice.dombase.LexBase.arraycopy(OEAB)
at ice.dombase.LexBase.parse(OEAB)
at ice.dombase.LexBase.parse(OEAB)
at ice.pilots.html4.ThePilot.do_parse(ThePilot.java:816)
at ice.pilots.html4.ThePilot.parse(ThePilot.java:717)
at ice.storm.StormBase.do_render_content(StormBase.java:1090)
at ice.storm.Viewport.runAsynchronousLoad(Viewport.java:373)
at ice.storm.LoadThread.run(LoadThread.java:52)

Fortunately, I found out how to fix this problem. The reason for the error is that the developers of OpenJDK decided to place Rhino (an open implementation of JavaScript) into OpenJDK’s bootclasspath, which essentially overrides the (quite old) version of Rhino used by (and shipped with) MATLAB with an incompatible one. Most likely this is a bug in OpenJDK, similar to a bug already reported for OpenOffice.org.

So, what can be done to get rid of this problem? As a workaround (until the developers of OpenJDK finally fix this problem) I suggest to delete the symlink /usr/lib/jvm/java-1.6.0-openjdk-1.6.0.0.x86_64/jre/lib/rhino.jar, which currently points to /usr/share/java/rhino.jar.

Now, OpenJDK works fine with MATLAB and I’m even able to print. Apart from that, the fonts look better.

To summarize: MATLAB R2008b does not work with OpenJDK, but MATLAB R2009a does if you remove rhino.jar from OpenJDK.

UPDATE: The IcedTea/OpenJDK guys managed to move OpenJDK’s version of Rhino to a different namespace (as of IcedTea6 1.8), which should solve the problem described in this post.

Posted in MATLAB | 5 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 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.

Posted in DB2 | 5 Comments