About Me

My photo
Singapore, Singapore, Singapore
I am currently working as a DB Consultant with Ashnik. We are into Open Source and Cloud based enterprise class solutions. Prior to this I was working as a DBA @ Misys India (Banking Services). Working on IBM DB2, Oracle Microsoft SQL Server. Selected as IBM Champion for Data Management 2011. I like bit of photography, cycling, poetry and I just love gadgets!!

Connect to me

See my profile on LinkedIn Visit my Ashnik Website

DB2 10 is here: What's new? The performance aspect!!!

Hi guys... So DB2 10 is here and is available for download...

I was involved in DB2 10 beta EAP and you can listen to my podcast with industry experts (which quite an honor)...

Well lets see what's new in v10 which can help in improving product performance...

It has an improved index scan which is termed as jump scan, for people from Oracle world this is similar to skip scan... I tried this out during EAP and have seen significant improvement in CPU timings and if you have wisely chosen your tablespace sizing e.g. extentsize and pagesize I/O also will decrease... This feature is specifically helpful if you have an index on two columns and you have queries on second key of the index... This wisely chooses the index keys which shall be evaluated for the match!

Second improvement is in terms of I/O, Smart Prefetching... This is particularly helpful for data spread over a highly clustered disk or if index pages have low density... In such cases it can automatically switch from sequential to readahead (a new type of prefetching) prefetch... This is more efficient and automatically switched to readahead if optimizer see that sequential prefetch is wasting I/O...


There are a lot of new SQL optimization enhancements, these are automatically taken care by optimizer when needed... For example now hash joins will be used by optimizer even if data types do not match, Early Aggregation of data, early removal of distincts, improvement in estimation of performance cost... There are various new indicators introduced in EXPLAIN_TABLES and EXPLAIN info generated by db2expln, which shall indicate if any of these features have been used or now... More on that latter...


Another good improvement is that RUNSTATS will now support index sampling, which shall avoid the need to scan the whole index for collecting statistic... Also, the new parameter auto_sampling will enable sapling for all background statistics gathering... Another improvement from statistics gathering perspective is the Automatic Stats gather for Statistical view when you set auto_stats_view... Talking of statistical view reminds that there are few other improvements e.g. now actual statistics from expression columns will be respected for generation of a more apt plan...


There are some improvements in intra-parallelism for query... Most catchy one is the ability to do parallel scans on range partitioned tables and indexes... Also the intra parallelism can be controlled from application by calling ADMIN_SET_INTRA_PARALLEL... Also degree of parallelism now can be controlled for a particular workload by using MAXIMUM DEGREE option in ALTER WORKLOAD...



There is an important improvement in STORAGE and I/O i.e. introduction of STORAGE GROUPS... Earlier till v9.7 if I was chosing ASM tablespaces, I had to loose on distribution of I/O... i.e. all the tablespaces with ASM were created in the path designated for automatic storage (which is provided at the time of DB creation or later by alter DB)... But now one has to create storage groups and attach one or more storage path to a group... At the time of tablespace creation one will have to attach them to a storage group... So now I can create all my ASM tablespaces on different disks... So if you have already read my previous posts on tablespace design (1, 2, and 3) then you will know how good is this feature...

I will wind up this post now and may be will try to put up all these features in little detail...

Till then keep optimizing...

So all those people who are running on v9 or prior should definitely plan to migrate to this new version...

Oh yeah, the compatibility as well has been improved and is as good as 98%... well do not trust me??? read for yourself this post by Serge Rielau...


Other posts/blogs regarding the new release:

Serge Rielau has done a series of post on new features... Follow him on twitter to read more...


DB2Night Show hosted by Scott Hayes has done an episode on same and there is a sequel to follow...