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.

MySQL-style LIMIT and OFFSET in DB2

An "ancient" but not yet well-known feature in DB2 is the support of MySQL/PostgreSQL-style LIMIT and OFFSET in SELECT statements and searched UPDATE/DELETE. Unfortunately, it is not really documented and I am working on getting some more documentation added (think about the "mostly harmless" as in the Hitchhiker's Guide to the Galaxy).

To demonstrate what is possible, I created a small table full of good stuff:
db2 => create table sweets(id int, desc varchar(60), quant int)
DB20000I  The SQL command completed successfully.
db2 => insert into sweets values(1,'dark chocolate',4)
DB20000I  The SQL command completed successfully.
db2 => insert into sweets values(2,'marzipan bar',1)
DB20000I  The SQL command completed successfully.
db2 => insert into sweets values(3,'almond cookies',10)
DB20000I  The SQL command completed successfully.
db2 => insert into sweets values(4,'granola bar',1)
DB20000I  The SQL command completed successfully.
db2 => insert into sweets values(5,'nut chocolate',1)
DB20000I  The SQL command completed successfully.
db2 => insert into sweets values(6,'chocolate-covered peanuts',1)
DB20000I  The SQL command completed successfully.
db2 => select * from sweets limit 5
SQL0104N  An unexpected token "limit" was found following "".  Expected tokens
may include:  "FETCH FIRST ROWS ONLY".  SQLSTATE=42601

By default, DB2 does not know about LIMIT and OFFSET either. But setting the DB2_COMPATIBILITY_VECTOR to MYS (as in MySQL) or "04000" enables the syntactic alternative to the FETCH FIRST n ROWS ONLY. After restarting DB2 the SELECT works:

db2 => select * from sweets limit 5

ID          DESC                                                QUANT
----------- --------------------------------------------------- -----------
          1 dark chocolate                                                4
          2 marzipan bar                                                  1
          3 almond cookies                                               10
          4 granola bar                                                   1
          5 nut chocolate                                                 1

  5 record(s) selected.

The following two examples show the use of the OFFSET, i.e., where to start in the result set. I combined it with ordering on the quantity.

db2 => select quant,desc from sweets order by num limit 3 offset 2

QUANT       DESC                                                       
----------- ------------------------------------------------------------
          1 nut chocolate                                              
          1 chocolate-covered peanuts                                  
          4 dark chocolate                                             

  3 record(s) selected.

db2 => select quant,desc from sweets order by num limit 2,3

QUANT       DESC                                                       
----------- ------------------------------------------------------------
          1 nut chocolate                                              
          1 chocolate-covered peanuts                                  
          4 dark chocolate                                             

  3 record(s) selected.

Note that in the second example a special abbreviated syntax is used. The first number is the offset where to start, followed by the number of rows to return.

As said, there is not much in the DB2 Information Center (yet). Serge has some more background in his blog about different ways of paging through result sets.

PureSystems meets Data: Register for October 9th event

Something cool is coming up on October 9th. The following video includes a registration link for the announcement event. I am not allowed to tell you what it is, but it is neither a new iPhone, electric car, next version of DB2, nor anything related to any elections coming up. It is related to expert knowledge, hardware and software integration, and your future in IT...

Business trips and database migrations

As you might have guessed I have been travelling and I am going to travel again. Every business trip brings some risks and has associated costs, but each one also has its opportunities or specific goals. Many of my business trips are related to database migration projects, enabling customers or business partners to move from Oracle to DB2.

Depending on the trip destination there is more or less risk associated. There could be accidents, health issues, robbery or mugging, the risk of loosing your luggage, and of course of not getting enough sleep. To mitigate some of the risks, preparation is key, first of them knowing about the involved risks, what to watch out for. Often, you can benefit from the experience of others, e.g., reading a travel guide, reading hotel reviews, etc.

In terms of costs, usually there is a rough estimate based on data that can be quickly gathered and some travel experience. It helps to decide whether it is worth travelling. Would someone spend 5000 EUR for a 1000 EUR opportunity? What brings the trip in the long run?

Migration projects are similar to business trips. There are costs associated, there are risks, but also a benefit. Companies migrate from Oracle to DB2 because they want to save money, perform better, simplify their data centers and reduce the server and storage footprint, grow their businesses, enjoy competitive advantages, benefit from technology advances, or any combination of these. So they look into what it costs to get there and turn to IBM. Based on data that can be quickly gathered and migration experiences, a decision to move forward and to look into details can be made - similar to a business trip. Tools like MEET allow to analyse the level of compatibility and to quickly give an overview of problems. Experience with Oracle to DB2 migration projects, big and small, allows to attach costs to the issues found. A proof of concept allows to gain experience, similar to some business trips in a controlled environment to validate assumptions and to build up a portfolio of hotel and airline reviews, and to train the business travellers.

And finally comes the decision to embark, to start the journey. There are risks, but there are also best practices that can be followed and tour guides that can help if needed. As with business trips, there are tour reports of others: Coca-Cola about moving from Oracle to DB2 at the IOD conference in October, customers talking at the IDUG conferences about their migration projects, blog posts detailing why projects succeeded and lessons learned.

At some point everybody faces the question the first time: Do I want to travel? Especially for migration projects it is an unusual question that might only come up once and not everybody feels comfortable with. It might be similar to the first trip to a foreign country. But then there are resources to help and to assist with the decision and along the journey.

Everybody hates DB2, and pureXML is crap!

DB2 pureXML has only one XML type, can you believe it? There are no choices. To be more flexible I want one XML type for financial applications, one for report-generating applications, one for financial applications that are used for generating reports, and one for report-generating applications in a more financial context. Where are my choices in DB2? Only one XML data type, no options!
DB2 pureXML is following those quasi standards from W3C for updating XML data. It's not standard yet. Can't they do anything proprietary that will stay proprietary after the standardisation is finished? I want something different in my database system, not standards. It would be too simple to learn and too simple to port to or from other systems. I want to show that I have special skills that I can use only with one special system. I hate DB2, I hate standards.
DB2 has all this autonomic and automatic stuff which is too simple to use. Where are the knobs? I love configurations, optimizer hints and month-long fine tuning, why autonomics? I don't want a life. Autonomics does not work. Look at the DB2 pureXML benchmark results! They did only use autonomics during their 1 TB TPoX benchmark, no manual tuning. Look at the pureXML results, the numbers cannot be good.
Speaking of benchmark results, I hate them. Why can't the DB2 guys leave testing to enterprises alone? Why do they make benchmark results public? We all know that benchmarks are crap and that's why so far no really serious database vendor has published any benchmark results, ever! This is all so much crap!

==
Are you wondering whether I had too much coffee this morning or was trying substances used in professional "sports" or my wife made me sleep outside in the cold? Nice guess, but I am only testing something for the upcoming April Fools' Day. The above may have been a bit too obvious, may have been missing any "supportive" facts and links you may have asked for. I hope you enjoyed it nonetheless. If you are in for more subtleties you should try reading what Oracle lables "Oracle 11g XML DB vs. DB2 9.5 pureXML". It can be downloaded here. I enjoyed all the subtle humor and how they argue. Nice, a well done paper for April Fools' Day!

BTW: You can learn more about TPoX in one of my previous posts and of course at the official TPoX website. I also wrote about XML storage options before. If you have never touched DB2, I encourage you to try out DB2. Don't be fooled. DB2 Express-C can even be used in production systems free of charge and pureXML is included.