SAIL is moving to a brand new DB2 implementation on new hardware. We're also making use of new functionality availabe available within DB2 tailored to analytics to analytics environments that IBM calls 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 optimistaion optimisation work that would previously have to be done by hand for you. Our tests suggest that well writen -written SQL qerys queries perform significaly significantly better on the new system, with 10x to 5000x reductions in query run time runtime compared to the current system.
BLU does 5 main things.:
- Organise Organises the data in columbs columns on disk (rather that than in rows)
- Automaticly indexs Automatically indexes everything
- Automaticly distrubtues eveything Automatically distributes everything across the partitions
- Does much more of the work in memory (making it faster)
- Improves the query prarrelism
...
- parallelism
SQL Querys
All your SQL querys that worked on the old system should work on the new system you dont need to create indexes. When you create tables you dont need to . As ever if you run into issues please contact helpdesk.
Changes you should be aware of
DB2 itself
DB2 server and clients have 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 automaticalyautomatically.
You no longer need to specify a columb column to "DISTRIBUTE BY" when creating tables as the system does this automaticalyautomatically.
You no longer need to explicitly specify "COMPRESS YES" when creating tables tables as this is the defualtdefault.
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 want need to drop or alter a
...
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