DB2 group_concat and other figments

For all that DB2 has to offer, there remain some features found in other relational databases that DB2 still lacks.  Multi-version concurrency control (MVCC) and Oracle-style optimizer hints (between the /*+‘s) often top the list, but there are others.  I’m reminded of these when I find myself recommending something out of habit before I remember DB2 doesn’t have it (rhyme intended).

This happened today with a request for DB2 SQL to build an export command with an explicit column list (no “select *”).  The simple answer was quick:

select ‘export ‘ || group_concat(colname) || ‘ from ‘ || tabname from syscat.columns where … group by tabname

Unfortunately, DB2 doesn’t have group_concat, so I went googling for an alternative.  Some folks offered group_concat, concat_all, and rowtocol implementations as UDFs and stored procedures, while others suggested really ugly recursive SQLs or UNION/CASE alternatives.  None were very general.  One IBMer figured out a way to use DB2’s xmlagg function to hack a solution, which I applied as follows:

select ‘export ‘ || substr(replace(replace(xmlserialize(content xmlagg(xmlelement(name “COLNAME”, colname)) as varchar(1000)), ‘<COLNAME>’, ‘, ‘), ‘</COLNAME>’, ”),3) || ‘ from ‘ || tabname from syscat.columns where … group by tabname

It works, but it’s uuuugly!

I can understand some hesitation to add group_concat to DB2; for example, it’s very easy to exceed column limits.  And architectural differences mean that DB2 doesn’t always need to “clone” everything that other DBs have (there are other ways to do the same thing).  But it’s often helpful to keep up with the database Joneses in one area while you’re passing them in others.

Recent DB2 enhancements (currently committed semantics, literal replacement, optimization profiles, etc.) have shrunk many of these gaps, but many remain to be closed entirely.  With the continued rapid addition of new DB2 features, perhaps we’ll see some of these soon.

Share This:
  • Print
  • Digg
  • StumbleUpon
  • del.icio.us
  • Facebook
  • Yahoo! Buzz
  • Twitter
  • Google Bookmarks
  • Google Buzz
  • RSS