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:
SELECT column FROM table FETCH FIRST 10 ROWS ONLY
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.
SELECT column FROM table FETCH FIRST 10 ROWS ONLY
Read more: FreeDB2.com
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:
SELECT column FROM table FETCH FIRST 10 ROWS ONLY
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.
SELECT column FROM table FETCH FIRST 10 ROWS ONLY
Read more: FreeDB2.com