10 Things to have in DB2: which shall improve performance..

Well I support development of a product which "claims" to be RDBMS independent... I know wat it takes to put forward a claim like that... I an more than me my senior colleagues have to break there head every time we have a porting... I remember the things I had faced, right from my senior porting the ER design from one db to other db and then creating the physical design of db and more than that security architecture... And haash how can i forget those incompatible functions and operators... No offences Mr Gates... But SQL Server has some out of the blue function arguement (what will be the most logical sequence of arguments if you wanna convert an expression to other data type, i bet most of you will miss the actual sequence used by SQL Server) and that concatenation operator X-( ... Well all of them have some or the things different... I can go on and on in blog post on that topic... Well lets see what features are good to have in DB2 (may or may not be inspired by other vendors), especially to from the performance angle... Lets try to list 10 of them (it's gonna be tuff for me, esp after the viper and cobra series)...

1) SQL Server 2008 has something called filter based indexes: Many of you might have faced an issue where you have multiple status to be stored in same column, but you will generally query on one/two of them as filter, other status are basically for display in inquiry. So how good it wud had been if you can index only those two status which are your filter criteria and save indexing cost and space... Your queries will be optimized for those two status only (like you say check and do not to enforce it and query is optimized for valid values)

2) Hierarchical Queries: I have worked with Oracle Hierarchical queries and have found them to be performing better than the recursive SQLs used to write pseudo-Hierarchical queries (yes that is what i will call them, for records recursive sql is available in Oracle 11g onwards)

3) Something as good as OEM in Oracle to monitor the database (and it should be free, hence optim is ruled out :P)

4) Forced and offline re-org after table/column alter should be fixed (no other RDBMS i have worked with requires this and yes i will count this as a performance issue in addition to being an availability issue)

5) Well there are queries generated by hibernate which uses ROW_NUMBER to window a table and fitler based on rownum<=N to select top N rows over a criteria, say last update date time. These kind of statements are automatically converted to SELECT TOP N for sql server 2008. It would had been nice if DB2 did the same, in an easier way (it is still possible with OPTIMIZEFORNROWS in db2cli.ini)

6) The optimizer should be able to guess, if the column(s) in join predicate is also part of some filter (where) predicate, and should apply the filter twice to get a better cardinality expectation. Well, I am not very sure if any RDBMS does this currently. But try it out and see the difference in expected cardinality yourself...

7) It will be good to have a better concurrency control and locking mechanism which can compete with that of oracle

8) Query Hints should be made more flexible and easy to use in prepared statements, on lines with Oracle and SQL Server

9) There should be function based indexes (currently the feature can be implemented with a little bit of work around by adding a computed column and indexing it)

10) Some good way of indexing low cardinality tables (like oracle has bit map indexes)


Well let's see how much db2 galelio has to offer!!! Have look:

DB2 Galileo Early Adoption Program