Valentine's Day, DB2 pureXML, and XQuery - How To Impress Your Spouse

Tomorrow is Valentine's Day and there are different ways to collect important Brownie Points at your significant other. Today, I want to show you how you can do it as a DBA knowing your DB2 system catalog and some XQuery (mental note: DB2 was the right infrastructure decision, version 9.5 required).

The following XQuery will produce a string based on a procedure name, then within the query we use a simple regular expression to get rid of characters, and finally make use of string processing functions.

xquery for $i in
db2-fn:sqlquery("select xmltext(procname) from syscat.procedures where procname like 'WLM_%ESS'")
let $k:=replace($i,'(_)|(S|W|T|M|P|R|N|C|A|G)',''),
$j:=string-join((substring($k,4,1),concat(substring($k,1,1),
substring($k,string-length($k)-1,1),
substring($k,5,1),substring($k,3,1)),concat(substring($k,7,1),
substring($k,string-length($k)-1,1),substring($k,2,1))),' ')
return $j

The result should look like shown here:


There are probably simpler ways to send the same message, but sometimes the effort counts. When I tested it with my wife this morning, she was especially thankful for the "1 record selected" - something that is important to most relationships...

===
Valentinstag
Wer am morgigen Valentinstag seinem/seiner Liebsten tief beeindrucken und die Liebe bekunden moechte, hat viele Moeglichkeiten. DB2 stellt (wie immer) die passende Infrastruktur zur Verfuegung. Mit obiger Anfrage kann man seine Gefuehle ausdruecken und gleichzeitig ein wenig XQuery lernen. Wichtig ist nichts anbrennen zu lassen und "1 record selected" als Teil des Ergebnisses zu haben. Nichts ist schlimmer als wenn Fragen aufkommen koennen...