Tuesday, November 26, 2013

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.

Monday, November 11, 2013

DB2 10.5: Overview of product editions, licensing and features

On the IBM developerWorks website is a new article discussing the different editions of DB2 10.5. It is titled "DB2 editions: Which distributed edition of DB2 10.5 is right for you?". It gives an overview of what editions are available, some of the top notch features, and what licensing options are available. Another new article, "Compare the distributed DB2 10.5 database servers" has a similar intent, but focusses on features by edition and is organized in a table format. The third installment, "Licensing distributed DB2 10.5 servers in a HA environment", does exactly that. :)

Thursday, November 7, 2013

TRANSFER OWNERSHIP: Pass objects on to others...

There are cases when ownership of database objects needs to be reorganized: People leaving the company, new rules from supervisory authorities (financial industry, ...), changes to applications, and much more. How do you change ownership in DB2? The answer is a statement introduced in DB2 9.1. The statement of question is TRANSFER OWNERSHIP.

The owner of an object or the security administrator (SECADM) can execute the statement and pass on the object to another user. SECADMs cannot transfer the object to themselves. There are over 20 different object types that can be handled this way. Even if you don't plan to change object ownerships it is a good idea to read the documentation for the TRANSFER OWNERSHIP statement as it nicely show what kind of objects are managed by DB2 and because for many of those their catalog tables are mentioned.

Why do I talk about such an "old" statement when I could talk about BLU Acceleration or the currently ongoing IOD Conference with all the announcements? Companies are planning DB2 version upgrades to exploit new features and to comply with new rules by certain deadlines.

Tuesday, November 5, 2013

BLU(e)! Cloud! In-Memory! FREE!!!

IBM has announced a so-called "IBM BLU Acceleration for Cloud". Right now it is a technology preview and FREE to use. Thanks to the combination of the BLU Acceleration technology and Cognos it is powerful. Simplicity is guaranteed through the presence of many tools and the "load and go" approach.

Right now you can sign up for free to test the cloud-based offering with your data.

After signing up you will receive an email with instructions and a link. Then you need to bring your own lawyer to analyze several agreements. ;) I accepted them and was ready to go. What is nice are several "tours" that are offered. The tours give an overview of the different buttons and screen areas within the dashboard, the system panel, etc.

After launching the web console different task and tools are provided for "data scientists" and "business analysts" as well as for warehouse developers and database administrators. You can hook up your Excel or use SQL to work with your own data or use a sample database.

More on this in a later blog, now I need to work with my data on the BLU cloud...