SAIL is moving to a brand new DB2 implementation on new hardware. We're also making use of new functionality available within DB2 tailored to analytics environments that IBM calls BLU Acceleration.
What this means for you
Data / Tables
All tables in SAIL and BASE layer schemas will be migrated and available just as on the old system.
No tables from user schemas will be migrated. If these tables are still required they must be moved into the new project schema areas prior to migration.
Speed
BLU does a lot of the optimisation work that would previously have to be done by hand for you. Our tests suggest that well-written SQL queries perform significantly better on the new system, with 10x to 5000x reductions in query runtime compared to the current system.
BLU does 5 main things:
- Organises the data in columns on disk (rather than in rows)
- Automatically indexes everything
- Automatically distributes everything across the partitions
- Does much more of the work in memory (making it faster)
- Improves the query parallelism
SQL Querys
All your SQL querys that worked on the old system should work on the new system. As ever if you run into issues please contact helpdesk.
Changes you should be aware of
DB2 itself
DB2 server has been upgraded to 11.1 from 9.7. For all desktop types other than dedicated, the client will be updated for you. Dedicated desktop users will need to run a helper script to update their drivers. This can be found in Software Centre.
Creating tables
You no longer need (or are able) to create indexes. This is because the system does this automatically.
You no longer need to specify a column to "DISTRIBUTE BY" when creating tables as the system does this automatically.
You no longer need to explicitly specify "COMPRESS YES" when creating tables as this is the default.
Altering tables
There are restrictions around altering tables with BLU. Columns in a table cannot be dropped or altered but can be added too. If you need to drop or alter a column you'll need to create a new table with your new layout and load the data into it.
Database level changes
The codepage has changed to "UTF-8".
Geeky details
The improvements that BLU provides come with some restrictions. We don't think they'll be an issue for anyone but just in case, you can read about them on IBM's website - Restrictions, limitations, and unsupported database configurations for column-organized tables