The announcement of a new software release is an exciting time since you get to discover all the new enhancements that can make your life easier as a software user or consumer. Without further ado, let’s dive into some of the new capabilities that are available with the Db2 for i 7.5 release.
You will
probably quickly notice that Db2 for i 7.5 doesn’t contain any heavy hitters like Temporal Tables & RCAC that were delivered in prior
releases. However, the latest release does contain an impressive of collection
of smaller improvements that enhance SQL development, data security, Db2
performance & scalability, and DBE enablement. So instead of having to make
room for a couple of big tools in your IBM i toolbox, you’ll be dropping in an
assortment of smaller tools.
On the
SQL development front, you will find a new data type and a built-in function.
Boolean is the new data type available to SQL developers. This new Boolean type
will enable your programs to be more strongly-typed and easier to understand.
CREATE TABLE supplier (suppID INT, suppName VARCHAR(10),
activeSupp BOOLEAN); INSERT INTO supplier VALUES(1, 'ABC CO', TRUE), (2, 'ACME', FALSE), (3, 'IBM', NULL); SELECT suppName FROM supplier WHERE activeSupp IS TRUE;
The new
TRY_CAST function enables easier identification of invalid column values since
it returns the NULL value when invalid data is detected as opposed to signaling
a hard failure like the CAST function.
The IBM
i 7.5 release contains several enhancements that tighten access to the valuable
business data stored in your Db2 for i databases. In addition, the new RESTRICT
ON DROP clause can be used to prevent accidental deletion of your data. Once
this setting is added to your Db2 tables, you no longer have to worry about
someone accidentally wiping out a table by typing in the wrong table name on an
SQL DROP statement or the DLTF CL command. Any tables using this setting cannot
be deleted until someone runs an SQL ALTER statement to remove the setting.
The Db2
performance and scalability improvements provide constructs to allow you to
both scale the size and performance of your databases. The maximum size of Db2
indexes has been increased from 1.7 TB to 4-16 TB. The new maximum size is dependent on the
logical page size of your existing indexes. 16 TB is the new max size for SQL indexes since they're created with a 64K logical page size. The good news is the maximum size
does not require a rebuild of your existing indexes. This larger index maximum
size removes one of the last limits to growth barriers for Db2 for i databases.
Prior to the new release, partitioned tables allowed clients to grow tables way
past the 1.7 TB size limit, but non-partitioned indexes over partitioned tables
were still stuck with the 1.7 TB max size. With Db2 for i 7.5, index size
should no longer be a concern when moving to a partitioned table
implementation.
Speaking
of table partitioning, the IBM i 7.5 release is the latest release to includes a licensing change
to the software feature, Db2 Multisystem, which enables the usage of
partitioning. Db2 Multisystem is now a no charge feature instead of the
chargeable feature. The no charge
licensing change also applies to Db2 SMP and the High Availability Journal
Performance features: journal caching and journal standby. It’s on my to-do
list to my update my recent Db2 Add-Ons entry to highlight that these four features as of June 1, 2022 are no longer
chargeable for the IBM i 7.x releases (7.1, 7.2, 7.3, 7.4 and 7.5). Just because Db2 SMP & Db2
Multisystem are simpler to obtain, does not mean that you should rush out to
use them. These two features work best when careful planning and preparation
are part of the deployment process and our Lab Services team has the expertise to assist in that process.
Db2 SMP
also has some interesting enhancements in the latest release. First, the optimizer
was enhanced to do a more accurate optimization with *OPTIMIZE % setting for the PARALLEL_DEGREE query option. This improvement should result in a more balanced use of
system resources when a query is running with this setting. Second, DBEs
can also protect their system CPU resources from being overrun by queries
using parallel processing with the new PARALLEL_MAX_SYSTEM_CPU query
option. Let’s say you set this option to 80 (which is the default) - once your
server CPU utilization exceeds 80%, Db2 will automatically start throttling
back the number of parallel threads used by Db2 for parallel processing until the server CPU
utilization drops below 80%. As you can see below, the Plan
Cache Properties were updated to give DBEs insights into how many SMP queries
have been automatically governed by Db2 due to this new query option. The Total Number of Queries with Runtime
Adjusted SMP Degree property is used to track this event – these properties
show that no automatic SMP throttling has occurred yet on the system.
Temporary index tracking and analysis is also simpler with Db2 for i 7.5 thanks to some new Plan Cache Properties as well as a new service – MTI_INFO. In the figure below, notice the MTI related properties now have their own properties section and there are new properties to track the usage of non-reusable temporary indexes.
If these temporary index
metrics start to rise, then DBEs will have a much easier time getting the
details for each MTI with the new MTI_INFO service. The following query shows how straightforward
it is to generate a list of all the MTIs currently created over the tables in a
specific schema. The Table_Schema parameter could be changed to *ALL, if one
needed a list of all the MTIs on the system.
SELECT * FROM
TABLE(MTI_INFO(Table_Schema => 'TOYSTORE3', Table_Name => '*ALL'))
This figure shows just a subset of the details returned by the new service for each MTI.
One last item to highlight is a
Db2 usability enhancement in the newest version of the IBM i Access Client Solutions (ACS). Usability of Run SQL Scripts is improved with the addition
of file tab support. As you can see in the figure below, this new support
allows you to easily jump back and forth between SQL scripts. Providing a big
productivity boost when you need to make similar changes to multiple SQL script
files. I’ve also found it quite useful when developing and testing a new SQL
routine. The file tabs allow me to keep my SQL routine source separate from the
SQL that I use to test out the new routine.
Hopefully, these Db2 for i 7.5
highlights have you gotten you excited to try out the new release. Although there are not any major Db2
enhancements in this release, remember that Aesop once said, Good things
come in small packages. More complete details
on the Db2 for i 7.5 release can be found here.
