Friday, December 18, 2015

DB2: The last query...

Bodensee/Lake Constance in Winter
I am always amazed at what DB2 is capable of. Today, I would like to show you my last DB2 query for this year. All what is needed comes with the regular DB2 and is even included in the free DB2 Express-C. You could run it in your own DB2, locally or in the IBM DB2 on Cloud service on Bluemix. Enough of introduction, enough of my words for this year, here is my last DB2 query for this year...

[hloeser@magicmachine]$ db2sampl

  Creating database "SAMPLE"...
  Connecting to database "SAMPLE"...
  Creating tables and data in schema "HLOESER"...
  Creating tables with XML columns and XML data in schema "HLOESER"...

  'db2sampl' processing complete.

[hloeser@magicmachine]$ db2 connect to sample

   Database Connection Information

 Database server        = DB2/LINUXX8664 10.5.6
 SQL authorization ID   = HLOESER
 Local database alias   = SAMPLE

[hloeser@magicmachine]$ db2 "select substr(note_text,length(note_text)-36,18) from in_tray where source='CHAAS'"

Enjoy the holidays

  1 record(s) selected.

Monday, December 7, 2015

MySQL-Style LIMIT and OFFSET in DB2 Queries

I was recently asked whether DB2 support MySQL-style syntax to page through query result sets. The good news is that DB2 supports LIMIT and OFFSET in addition to its own syntax. The only drawback is that the extra syntax supports needs to be enabled before it can be used. In the following I am going to show you how.
MySQL syntax in DB2

To support features offered by other database systems like Oracle, Microsoft SQL Server, Sybase, or MySQL that are incompatible with existing DB2 features, the DB2_COMPATIBILITY_VECTOR registry variable was introduced in DB2 version 9.7. The vector is used to selectively enable features and there are predefined settings named ORA (enable all Oracle features), SYB (Sybase), and MYS (MySQL). With that background knowledge we can directly get started on enabling LIMIT and OFFSET.