Updated 6/26/2017 to include links on details for DSM 2.1.4 and a link to the DB2Night Show replay.
June 22 and the week surrounding it brought a number of announcements from IBM about DB2.
IBM has recognized that they need to invest more in marketing DB2. Somehow this translates to renaming things.
- DB2 for LUW is now Db2 (no platform specification, lower case b)
- DB2 for z/OS is now Db2 for z/OS
- DB2 for iSeries is now Db2 for i
Can you imagine the hours that IBM is putting into redoing the capitalization throughout all the documentation? While I am sure I make my share of mistakes in the blog, I am a bit of a stickler for grammar and capitalization. The lower case b drives me insane.
IBM renames things frequently. I’m used to that by now, but in the cloud renamings this round, they’re using the same name to refer to something different. What we used to know as dashDB for transactions is now called Db2 on Cloud. What we used to know as DB2 on Cloud is now Db2 Hosted.
Also, what we used to know as dashDB for Analytics is now Db2 Warehouse on Cloud. I do like that they are adding DB2 in to make it clear that we’re talking about DB2 in the former dashDB offerings.
This was announced in the spotlight session at IDUG, but IBM is now offering “Continuing Support”. This is different from “Extended Support”. “Continuing Support” means that you can keep paying your annual support cost for out-of-support versions of DB2 – meaning 9.7 and 10.1 when they go out of support this fall. This will allow you to open PMRs and IBM support will help you investigate the issue and identify workarounds, but there will be no fix packs, and no special builds, no matter how severe the issue is.
This is a new edition of DB2. It is completely free to use in non-production environments with no support, and includes all of the advanced-edition DB2 features. It is the same code base as the other editions of DB2, so the only change if you purchase licenses is to apply a license file. The biggest caveat is that you may only use it for non-production environments. It is limited to 4 cores and 16 GB of memory per server and total data size of 100 GB in table spaces. That likely rules out any complex testing of BLU, but allows for testing of things like pureScale or compression that could not previously be tested for free. Express-C continues to be available as a free version for small production environments with the reduced feature set. You can download Developer-C now in the traditional format. At that link, click to sign up and then agree to the license agreement. For Developer-C in a docker image, see the Download and Go section.
Fix Pack 2, Mod Pack 2 (22.214.171.124)
Fix pack 2, mod pack 2 was released this week as well. While 11.1 fix pack 1 mod pack 1 had to be treated like a version upgrade in a lot of respects, fix pack 2 mod pack 2 is more like a traditional fix pack. There’s a great technote on the compatibility and upgrade implications.
My Favorite Features
I have three favorites in fix pack 2, mod pack 2.
(More) Online Crash Recovery
For crash recovery, the redo phase is still offline. However, the undo phase only requires Z locks on specific tables instead of a full database outage. For anyone who has had to wait for a long undo phase in an emergency situation, this is awesome.
Reporting Table Space Problems from the Standby on the Primary
I had a scenario where users were doing LOADs with COPY YES on an HADR database. There was a location that was shared with the standby so that the load copy files would be available for roll forward. However, users started using a different location, and I only discovered it when I found odd messages in the diag log on the standby and investigated it further. These tables would have been unavailable had I failed over! Working on a way to monitor for this was difficult. IBM is making that easier by using the HADR_FLAGS field to report if there is a table space problem on the standby. They have additionally announced the ability to recover on a table space basis on the standby should this situation occur. Such a small thing, but this is by far my favorite feature in fix pack 2, mod pack 2.
Improved Handling of Redirected Restores that Run Out of Space
I don’t have all the technical details on the fix yet, but I describe the issue in detail in SQL0752N on Rollforward After Redirected Restore. IBM tells me that DB2 will stop telling me the RESTORE DATABASE command completed successfully when there is an issue with space for one or more table spaces.
There is more in this feature/mod pack than I expected. Small things but more than just a couple of things. The list includes:
- Faster transaction rollback (sounds neat, waiting for a rollback of a large transaction that caused a log full is horrendous)
- Faster pureScale member crash recovery
- Restore REBUILD in pureScale (rebuild from table space backups)
- Multi-core insert for BLU to speed up ETL/ELT, ingest, and data movement activities
- Adaptive compression’s decompression has improved decompression efficiency (they estimate ~30% cpu improvement for this process)
- Hardware acceleration for DECFLOAT
- Optimization for COUNT, COUNT_BIG, and SUM
- Better evaluation of TRUNC, MOD, and ROUND and where it is done in query processing
- Efficiency improved for RENAME TABLESPACE
- Exploitation of synopsis tables for LIKE predicates on BLU
- Improved text search performance for queries containing OR predicates used with the text search functions
- SAP improvements (also apply for non-SAP environments)
- Redundant function elimination (DB2 will only apply once if you specify it twice)
- Reduced overhead in unfenced C-UDF calls
- Increased size for CASE statements, and increased efficiency for CASE statements
- SAP BW Use of parallel insert
- SAP DBA Cockpit exploitation of DB2_GET_INSTANCE_INFO for Windows
- Extend CHANGE HISTORY event monitor to include more monitor elements
- New options for STMT_CONC to allow STMT_CONC to ignore comments
- Explain now shows which table function a GENROW is for, when a table function is involved
- Support for local certificate authority within KeySecure
- New check box in GUI for SSL in the MS ODBC Administrator window
- Option to encrypt sample database when you create it
- ADMIN_MOVE_TABLE updates
- DB2_AVOID_LOCK_ESCALATION registry variable to force errors instead of lock escalations
- COL_TABLE_SERIALIZE lock details externalized in all lock monitoring
- Improved logging for db2chkupgrade
- New sub-options for db2trc for cli
- XML enhancement for implicit names, simplifying xquery syntax
- Support for PostgreSQL and MySQL added to federation
- Ability to configure SSL to connect to DB2 family data sources
- JSON support enhanced by allowing direct access to JSON interfaces
- Technical preview (do NOT use in production, many limitations) of indexes on column-organized tables
DB2 11.1 Fix Pack 2, Mod pack 2 can be downloaded here: http://www-01.ibm.com/support/docview.wss?uid=swg24043789
The IBM DB2 Knowledge Center also lists out some of the enhancements in Fix Pack 2, Mod Pack 2.
A new Release of Data Server Manager
Download and Go – DB2 on Docker
You still cannot find this by searching Docker. However, there are now docker images available with Developer-C and Data Server Manager. Just a few clicks to set up a sandbox – I’ll sure be checking it out.
IBM Event Store
They’re announcing this as a project they’re working on. It is something that has been in a demo on the expo floor at IBM Insight/WoW for the last two years. It is designed to ingest data at a very high speed, and will use Parquet to store data in an HDFS format. I don’t think it will be integrated into DB2, but the insert rates they’re getting in testing are astronomical.
developerWorks has a new look today. Check it out and let me know what you think.