anonymous (sql) blocks in db2

SQL Server supports the execution of SQL not within the context of a stored procedure or trigger. This is quite a handy feature when debugging as it allows one to write thowaway code without the overhead of declaring a new stored procedure that later has to be dropped.

Although it is possible to do this in DB2 too, it isn’t as transparent as it is in SQL Server (because – I expect – DB2 is more ANSI SQL compliant than SQL Server). The trick is to declare the code that one wants executed in an ATOMIC block:

   begin atomic
   declare var_a char(1);
   set var_a = (select ibmreqd from sysibm.sysdummy1);
   end
   @

This may be executed from a Command Center session, or (having connected to a database, and the above dumped into a file called "script.sql") via the CLP:

   $ db2 -td@ -vf script.sql

Thanks to DHRUV for a detailed post on the subject.

play it again, sansa

I have recently acquired a Sansa Fuze and (paired with Sennheiser HD 435 headphones) and I’m very impressed. Its a good looking device with great build quality and – most importantly – fantastic sound quality. It’s so good that I passed up on a 2nd generation iPod Touch (those who know me will know how big a deal that is).

While reports suggest that it supports synching playlists in MTP mode flawlessly, my experience – as a Mac user – is that creating playlists with the device in MSC mode is tricky. Although the Fuze supports M3U playlists, it is very picky about the format. Get it wrong, and the Fuze will refuse (refuze?) to read it, rendering it simply as [Empty]. Thanks to various people on the Sansa forums I better understand the Fuze’s quirks and have hacked together the following script to generate playlists:

   #!/bin/bash

   # ---------------------------------------------------------------------------- #
   # Sansa Fuze M3U playlist creator                                              #
   #  - run from $SANSA_ROOT/MUSIC                                                #
   # ---------------------------------------------------------------------------- #

   # Don't descend more than 1 level.  In my collection each directory represents
   # an album.  Multi-disc albums - with the individual discs in subdirectories -
   # will have all discs in a single playlist, which is what I want.
   for file in $(find . -maxdepth 1 -type d)
   do
      if [ $file != "." ]
      then
         # Write the playlist in the main album directory
         cd $file
         # Tell find to render the names of the MP3s that it finds using the
         # Windows standard CR-LF line terminator.  (Sansa requirement)
         find . -name \*.mp3 -printf %p\\r\\n | \
         # Convert the '/' file separator to the Windows standard '\' (Sansa
         # requirement).
         sed -e '/\//s//\\/g' | \
         # The Sansa doesn't seem to handle relative paths correctly.  If the
         # filenames in the playlist contain a leading '.\' the device cannot read
         # the playlist and renders it as '[Empty]'.  So we cut those characters
         # from the filename/path produced by 'find'.
         cut -b 3- > $file.m3u
         # Head back for more ...
         cd - > /dev/null
      fi
   done

db2 drop schema … please!

The best documented way to drop a schema in DB2 is the

   db2 "DROP SCHEMA <schema_name> RESTRICT"

command. This, however, requires that all objects in the schema have been dropped before DB2 will allow the schema to be dropped. This is somewhat inconvenient for ad-hoc testing and fortunately in DB2 9 one can now use the stored procedure admin_drop_schema:

   db2 "call admin_drop_schema('<schema_name>', NULL, 'ERRORSCHEMA', 'ERRORTABLE')

Simple enough, but it took me long enough to track this down that I thought it worth posting.

regular expression matching without a state machine

Over the past few days I’ve been brushing up on my compiler theory, and in the course of my readings happened across an interesting article in the Scala blogs. The author presented a very concise (albeit not complete; see the comments following the article) implementation of a regex matching engine. I was intrigued and thought I’d try my hand at writing it in Common Lisp. My naive implementation:

(defclass regex () ())

(defclass phi (regex) ())

(defclass empty (regex) ())

(defclass l (regex) ((letter :initarg :letter)))

(defclass choice (regex)
  ((left :initarg :left)
   (right :initarg :right)))

(defclass seq (regex)
  ((left :initarg :left)
   (right :initarg :right)))

(defclass star (regex) ((expr :initarg :exp)))

(defgeneric accepts-empty (re))

(defmethod accepts-empty ((re phi)) nil)

(defmethod accepts-empty ((re empty)) t)

(defmethod accepts-empty ((re choice))
  (or (accepts-empty (slot-value re 'left))
      (accepts-empty (slot-value re 'right))))

(defmethod accepts-empty ((re seq))
  (and (accepts-empty (slot-value re 'left))
       (accepts-empty (slot-value re 'right))))

(defmethod accepts-empty ((re star)) t)

(defmethod accepts-empty ((re l)) nil)

(defgeneric part-deriv (regex item))

(defmethod part-deriv ((re phi) atom)
  (make-instance 'phi))

(defmethod part-deriv ((re empty) atom)
  (make-instance 'phi))

(defmethod part-deriv ((re l) atom)
  (if (equalp atom (slot-value re 'letter))
      (make-instance 'empty)
      (make-instance 'phi)))

(defmethod part-deriv ((re choice) atom)
  (with-slots ((left left) (right right)) re
    (make-instance 'choice
                   :left (part-deriv left atom)
                   :right (part-deriv right atom))))

(defmethod part-deriv ((re seq) atom)
  (with-slots ((left left) (right right)) re
    (let ((rn (make-instance 'seq :left (part-deriv left atom) :right right)))
      (if (accepts-empty left)
          (make-instance 'choice :left rn :right (part-deriv right atom))
          rn))))

(defmethod part-deriv ((re star) atom)
  (make-instance 'seq
                 :left (part-deriv (slot-value re 'expr) atom)
                 :right re))

(defgeneric match-regexp (re word))

(defmethod match-regexp ((re regex) word)
  (if (eq 0 (length word))
      (accepts-empty re)
      (match-regexp (part-deriv re (first word)) (cdr word))))

; testing

(match-regexp (make-instance 'choice
                             :left (make-instance 'seq
                                                  :left (make-instance 'l :letter #a)
                                                  :right (make-instance 'l :letter #c))
                             :right (make-instance 'star :exp (make-instance 'l :letter #b)))
               (list #b #b))

It’s definately not as concise as the Scala version, partly because of my decision to use generic functions to imitate Scala’s case-class matching. If any Lispers happen across this, I’d really appreciate any comments about how to improve on this.

technicolor terminal : the Cygwin saga

Not too long ago I posted a snippet that explained how to enable the 256-color display capabilities of terminals that support it. I’ve since had a bit of trouble getting this to work in terminals under Cygwin. Applications like Vim understand the capabilities of such a terminal, but less and man would complain:

   WARNING: terminal is not fully functional

After bit of digging I finally realised that it isn’t enough to have entries for those terminal types in /usr/share/terminfo/*/*. Some apps expect to find the terminal capabilties described in a termcap file, either /etc/termcap or ~/.termcap. Fortunately, there is an easy way to export the capabilities described in the terminfo database entries to a termcap file:

   $ infocmp -C xterm-256color >> ~/.termcap

If you’re a screen user, its worth exporting the entry for screen-256color too. man infocmp for more information.

show me the matches

Something that I’ve never been particularly fond of is the way that Vim does filename completion. Completing with the first available match and then allowing one to cycle through the remaining matches with <TAB> feels less effecient than presenting only the longest matching portion of the filename, allowing one to further refine the partially-completed string or retrieve all candidates using <TAB><TAB>. The latter is, of course, the model used by bash and Emacs.

As it turns out Vim has options that control this behaviour:

   set wildmode=longest:full
   set wildmenu

This wildmode setting instructs Vim to complete only the longest common portion of the filename and, if wildmenu is enabled, display the candidates in a status menu. This wildmenu setting, of course, turns this feature on.

unlocking bash

I periodically manage to lock bash with an errant C-S (that’s <ctrl>+<s> in Emacs-speak). The only way to recover seemed to be kill the shell window (if I was fortunate enough to be in a windowing environment, or in screen).

Hannu Nevalainen posted a solution to this problem to the Cygwin mailing list. The problem relates to the use of C-S and C-Q as the ASCII XOFF and XON flow control characters respectively. Having hit C-S, simply hit C-Q to store the bash session.

It seems that my education is lacking some terminal history!

declaring db2 global temporary tables & cursors

Since v7.1 DB2 LUW (or UDB, if you prefer) has supported session-specific global temporary tables. Once declared, the table may be referenced until the session ends, at which point the rows are deleted and the description is dropped. (See the IBM Infocenter pages for details). The syntax for declaring these tables is, however, a little arcane, and I found using them in conjuction with cursor declarations somewhat tricky.

Specifically, if the cursor is opened over the temporary table, the cursor declaration must follow the table declaration. However, it seems that DB2’s parser expects only variable declarations to follow the table declaration. For example, the following code:

   connect to testdb@
   set current schema='testschema'@
   
   DROP PROCEDURE temp_proc@
   CREATE PROCEDURE temp_proc(
         IN input_var CHAR(1))
      LANGUAGE SQL
      DYNAMIC RESULT SETS 1
      CALLED ON NULL INPUT
   BEGIN
   
      DECLARE SQLCODE INTEGER DEFAULT 0;
      DECLARE GLOBAL TEMPORARY TABLE t_y_or_n (y_or_n char(1))
         ON COMMIT PRESERVE ROWS NOT LOGGED;
   
      DECLARE local_var CHAR(1);
      DECLARE crsr CURSOR WITH RETURN TO CLIENT FOR
         SELECT y_or_n FROM session.t_y_or_n WHERE y_or_n=local_var;
      INSERT INTO session.t_y_or_n
         SELECT ibmreqd FROM sysibm.sysdummy1;
      SET local_var = input_var;
   
      OPEN crsr;
   
   END@
   
   CALL temp_proc('Y')@
   terminate@

yields the error:

   DB21034E The command was processed as an SQL statement because it
   was not a valid Command Line Processor command. During SQL
   processing it returned:
   SQL0104N An unexpected token "<variable declaration>" was
   found following "". Expected tokens may include: "<SQL statement>".
   LINE NUMBER=12.
   SQLSTATE=42601

The solution seems to be to put the table declaration and the the cursor declaration in different scope. This is achieved using labels, i.e.:

   connect to testdb@
   set current schema='testschema'@
   
   DROP PROCEDURE temp_proc@
   CREATE PROCEDURE temp_proc(
         IN input_var CHAR(1))
      LANGUAGE SQL
      DYNAMIC RESULT SETS 1
      CALLED ON NULL INPUT
   P1 : BEGIN
      DECLARE SQLCODE INTEGER DEFAULT 0;
      DECLARE GLOBAL TEMPORARY TABLE t_y_or_n (y_or_n char(1))
         ON COMMIT PRESERVE ROWS NOT LOGGED;
      P2 : BEGIN
         DECLARE local_var CHAR(1);
         DECLARE crsr CURSOR WITH RETURN TO CLIENT FOR
            SELECT y_or_n FROM session.t_y_or_n WHERE y_or_n=local_var;
         INSERT INTO session.t_y_or_n
            SELECT ibmreqd FROM sysibm.sysdummy1;
         SET local_var = input_var;
         OPEN crsr;
      END P2;
   END P1@
   
   CALL temp_proc('Y')@
   terminate@

The above code can be run by copying it into a file and executing it with:

   db2 -td@ -v -f <script_name>

Thanks to nemali on the Dev Shed forums for his/her post on the subject.

retrieving large values without truncation in the DB2 CLP

The DB2 command-line processor (CLP; invoked from a shell with the command db2) truncates output greater than 8K. One way to retrieve the full text of a column value that exceeds that size (e.g. CLOB data) is to use the export command. For example, having connected a database and set the current schema, one could use a statement of the following form from within the db2 CLP (note that the "db2 =>" is the CLP prompt):

   db2 => export to result.del of del modified by chardel'' coldel; decpt, select <column> from <table>

The "of del" indicates that the output file should contain delimited data. The delimiters used, and how they are applied, is defined by the modified by clause and its qualifiers "chardel" and "coldel";

  • chardel: defines the character that will be used to delimit character data; in the above example a single quote (') is used.
  • coldel: defines the character that will be used to delimit columns; in the above example a semi-colon (;) is used.

The file produced by this statement would look something like:

   1;'foo';256;'KB'
   2;'bar';512;'KB'
   3;'baz';1;'MB'

Note, however, that if the column value that is being exported is longer than the default character column that can be exported, export will truncate the data and issue the warning:

SQL3132W The character data in column "DATA" will be truncated to size "32700"

To work around this, the lobfile parameter may be used to specify a base prefix for the files that will be used to store LOB data without truncation. The form of the command then becomes:

   export to result.del of del lobfile result_lob_ modified by chardel'' coldel; decpt, select data from debug_data

For more information please refer the IBM Infocenter pages that describe the EXPORT command, and its file-type modifiers.