In the past couple of months we have had quite a bit of influx of new people trying out DB2. Most have previous experience with other DBMS like Oracle, Microsoft SQLServer, MySQL, and PostgreSQL. I see that reflected in the volume of the questions that appear quite simple for those of us who have been around DB2. However, if you paid for your kids braces with your Oracle SQL skill, the way you do things in DB2 may not be as apparent. Just today I got a lengthy list of questions from an ISV looking to make use of DB2 on the Cloud. So, I decided to write a few posts that may take DB2 people back to basics but, I hope, will make DB2 a bit more familiar to those who have not tried it before. This is the first post in what I hope will be a mini-series on how to get things done in DB2 for those that know how to get things done in other SQL databases.

One of the questions that I got was: “Can you define in the SQL itself a maximum number of retrieved rows (“TOP” in SQL Server, “rownum” in oracle)?” Let me start by saying that I love it when people ask this question. Why? Because for the longest time I would come across code where a programmer would use the simplest SQL to fetch out a huge result set, sort it in the application to find the top 10 rows and dump the rest. Every decent DBMS out there lets you do it right; there is absolutely no excuse for this type of sillines. I am being kind here.

For example, in Microsoft SQL Server you would use TOP:
SELECT TOP 10 column FROM table

MySQL and PostgreSQL SQL would use LIMIT like so:
SELECT column FROM table LIMIT 10

PostgreSQL v8.3 and later can also use this more standard SQL:

An Oracle programmer would write
SELECT column FROM table WHERE ROWNUM <= 10

In Sybase, you would set rowcount
SET rowcount 10
SELECT column FROM table

DB2, as you would expect, also has special SQL syntax to limit the number of rows returned by a query. You can simply append FETCH FIRST n ROWS ONLY to you query and you are set. By the way, this is SQL:2008 standard but I doubt many people care.


So, method number one is to simply use the SQL syntax that is native to your database to limit the number of rows returned. If your DBMS does not have that, consider switching to a database that does. DB2 Express-C is free and it is a great choice.

Now, it turns out that this is only one of the ways you can achieve the goal of improving performance of your SQL queries by limiting how much data travels from the database to your application. One very interesting and very unique thing about DB2 is that it is a bit of a chameleon when it comes to SQL. You can actually make DB2 pretend to be an Oracle database for example. An excellent feature if your application is written for Oracle or if you are more comfortable with using Oracle SQL. This capability was introduced in a year or so ago and has been greatly enhanced in DB2 v9.7.1 and v9.7.2 that was shipped in May of 2010. Making DB2 behave like Oracle simply requires typing in this command:


If you do that, you would be able to use Oracle SQL syntax to get the top 10 rows i.e.
SELECT column FROM table WHERE ROWNUM <= 10
Unfortunately, if you are using the free DB2 Express-C, you are not going to be able to set the DB2 compatibility vector to Oracle. At least not yet. However, all other editions of DB2 will allow you to do set the compatibility vector to Oracle. This means that you can upgrade to say a yearly subscription for DB2 Express and get extra resources (extra 2GB of memory and extra 2  CPU cores), IBM 24*7 support, and extra features including oracle compatibility. The price is exactly the same (actually $4 cheaper/year) as comparable subscription for MySQL.
DB2 v9.7.2 brought two additional personalities that DB2 can assume. With DB2 v9.7.2 we introduced an optional feature called “SQL Skin for Sybase ASE” which lets one use Sybase SQL syntax when working with DB2. SQL Skin for Sybase ASE. Take a look at this screencast on to learn more about SQL Skin for Sybase ASE. The other personality that we added in DB2 v9.7.2 is “MYS”, and yes, it stands for MySQL. But be careful how you pronounce it. You activate it by using the familiar command:

Unlike Oracle and Sybase compatibility vectors which adress a very extensive set of SQL for these DBMS, MySQL compatibility vector only addresses two SQL clauses LIMIT and OFFSET. We had to start somewhere! Unlike Oracle and Sybase compatibility vectors, MySQL compatibility vector is available in the free DB2 Express-C. It is actually enabled by default. Getting back to the topic of this post, you now know that you can use not just DB2, but also Oracle, Sybase and MySQL SQL syntax to limit the number of rows returned by a query. Personally, I really like MySQL syntax SELECT column FROM table LIMIT 10 because it just makes sense. And the best thing about using LIMIT from MySQL is that it can also be used in DELETE and UPDATE statements. And, for something completely off the wall, you should know that MySQL does not accept variables as part of the LIMIT clause (supposed to be fixed in the upcoming version of MySQL) but DB2 is quite content to use variables with LIMIT.

As interesting as it is to limit query results through SQL, I believe that the right way to do this is not SQL at all. Why not? Well, as you have seen, SQL syntax varies greatly. Every decent API out there supports database independent way of restricting the size of the result set. For example in ActiveRecord in in Ruby on Rails you would do something like this Person.find(:all, :limit => 10) to return ten people records. In Java, when using JDBC you would set statement property MaxRows like so:
String select = "SELECT * FROM MYTABLE";
java.sql.ResultSet rset = stmt.executeQuery(select);
to achieve the same result. In ODBC, you would use SQLSetStmtOption to set SQL_ROWSET_SIZE to 10. As I said, most semi-modern data APIs provide a database independent way to limit the number of rows returned by a query.

So, the 3 ways to to return top 10 rows by an SQL query are:

  1. use your DBMS’s native SQL syntax. For DB2 it is SELECT column FROM table FETCH FIRST 10 ROWS ONLY
  2. If working with DB2 v9.7.2 use SQL syntax of the database you are familiar with. For example, use MySQL SQL syntax SELECT column FROM table LIMIT 10
  3. Use DBMS independent way to limit result set size provided by your favorite API

PS. I do realize that I did not demonstrate how to do TOP 10. Simply add appropriate ORDER BY clause to any of the statements mentioned to sort results according to the desired criteria. Limiting sorted results to 10 will give you TOP 10.

Tagged with →