Welcome to 2022! New Year’s resolutions often involve trying new things, so in that spirit I want to highlight the benefits of some overlooked Db2 for i technologies that you might not have used before since they are not automatically installed with Db2 for i. While the built-in features & functionality of the integrated Db2 for i database engine are great, these overlooked Db2 for i add-ons are hidden gems that can make it easier for you to deliver on requirements from the business.
Db2
for i Symmetric Multiprocessing (SMP)
The Db2 SMP
licensed feature is one of oldest Db2 add-ons on this list – validated by the
fact that I was actually still writing code in the Rochester lab when this was
delivered😉. This
licensed feature enables you to employ parallel processing to speed up the
performance of long-running queries and accelerate database engineering tasks
such as index creation. Db2 SMP parallel processing can aggressively utilize
system resource such as CPU and memory, so make sure you check out this blog entry to learn how to best utilize the Db2 SMP feature. If you would like
to evaluate Db2 SMP on your system, then contact me and I can get you setup
with the IBM Try & Buy program. Effective June 1, 2022, Db2 SMP is a no charge licensed feature for the IBM i 7.x releases (7.1, 7.2, 7.3, 7.4, 7.5).
Db2 Multisystem
The name of the Db2 Multisystem licensed feature is a little misleading, but it can provide value on a single system. The Db2 Multisystem feature is invaluable when you have tables in your database that are approaching the maximum size limits for a single table of either 4.2 billion rows or 1.7 TB. Yes, there are clients that have hit these limits – in fact, I have an engagement later this month with a European client who has 2 tables in their SAP databases that are getting uncomfortably close to the 1.7 TB size limit. Db2 Multisystem enables you to extend these limits by breaking a single Db2 table into multiple partitions (i.e., members). When dealing these large amounts of data, it’s critical that best practices are followed in the table partitioning and data migration processes – in fact, we recommend that clients only partition their tables with the assistance of Lab Services. A Try & Buy program is also available for this license feature. Effective June 1, 2022, Db2 Multisystem is a no charge licensed feature for the IBM i 7.x releases (7.1, 7.2, 7.3, 7.4, 7.5).
OmniFind
Text Search Server for Db2 for i
The
OmniFind Text Search Server is a no charge feature of the operating system, but
like Db2 SMP & Db2 Multisystem it must be installed in order to use the
functionality. The functionality provided by this add-on is the ability to
perform high-speed linguistic text searches against text data – including those
stored in rich-text formats such as PDF and Word. Not only can these searches
be performed against data stored in Db2 table columns, searches can also be
performed against objects outside of Db2 including IFS files, spool files, and
source physical file members. One aspect of a linguistic search is that the
text search engine will find matches against all variations of a word – for
example, the input search string includes “give”, it will identify matches when
the target text contains gave or given. The engine provides a CONTAINS
function that makes it easy to integrate searches into your SQL as the
following example demonstrates:
SELECT feedSrc, feedDate FROM newsfeeds WHERE CONTAINS(feedDoc, 'California insurance settlement') = 1 AND feedDate > '01/01/2021'
In this example, the OmniFind server automatically recognizes California as a state and
also searches for the two-digit state abbreviation ('CA') at the same time that
it searches for the 'California' string. As a result, the following sets of
news feed text will be identified as a match by this OmniFind search
request: "$100 million insurance
settlement to CA firm" & "California man wins insurance settlement". These
simple examples just scratch the surface of the capabilities of the OmniFind
Text Search Server, check out this white paper
for more details.
IBM
Advanced Data Security for i
This no
charge security feature enables you to use security functionality known as Row
and Column Access Control (RCAC) to secure your Db2 databases. Last year, I blogged
about the column masking support provided by RCAC to protect sensitive data
values such as credit card numbers. RCAC also includes support for row
permissions which can be valuable when you want to store data from multiple
sites or tenants in a single table. In this situation, a row permission can
guarantee that a user from specific site (eg, SiteA) will only to be access
rows from their site and not any other sites (eg, SiteB, SiteC). One IBM i
client that is a company comprised up of multiple subsidiaries recently engaged
our team to implement row permissions to ensure that the users for each
subsidiary only can access the financial data for their subsidiary. You can
learn about RCAC in this excellent Redpaper.
HA
Journal Performance
At first glance, this chargeable high availability licensed feature may not appear to be a Db2 add-on, but is applicable to the many IBM i clients that journal their database objects to ensure data integrity and recoverability. This licensed feature supports both journal caching and journal standby mode. The journal caching feature can improve the performance of journaling by caching journal entries in memory and then bundle this group of journal entries into a single disk operation. Without this capability, individual journal entries are immediately written to disk one entry at a time – especially with batch jobs. Journal caching supports data queues, data areas, and stream files in addition to Db2 tables. Journal standby mode is generally enabled for the local journal on the target server when an object-replication-driven, high-availability environment is in place. This mode reduces the disk and CPU loads on the high availability replication software on the target side by sorting through journal entries and discarding them. Effective June 1, 2022, these journal features are no charge licensed feature for the IBM i 7.x releases (7.1, 7.2, 7.3, 7.4, 7.5).
IBM Db2
Mirror for i
Db2
Mirror is the most recent add-on to Db2 for i. This chargeable feature enables
continuous availability for mission-critical applications with its database
clustering technology. This technology synchronously mirrors database updates
between separate nodes. If one of the nodes hosting the Db2 Mirror cluster goes
down, the other node automatically and seamlessly picks up the database
workload from the application servers. Check out the product page for additional details if you have
an application requiring continuous availability.
IBM
Db2 Web Query for i
Last,
but not least is Db2 Web Query for i. Db2 Web Query is a low cost web-based business
Intelligence and data warehousing set of products. Modernize your Query/400 or
RPG reporting environment and deliver highly visual dashboards to your business.
Schedule reports to run in batch that then are distributed out as spreadsheets,
PDFs, or analytical reports that allow end users to “play with” the data. Use
the DataMigrator component to automate replication and transformation of data
into a data warehouse or some other target such as a cloud-based service. Import
existing Query/400 or Showcase Strategy (and others too) queries into a more
extensible and productive reporting solution than the old green-screen based
solutions. Check out some videos
of Db2 Web Query in action or read more about the latest enhancements here.
Hopefully,
this quick overview of Db2 add-ons has piqued your interest to kick the tires
on one of these Db2 hidden gems in 2022. Let me know if you need any assistance
from Lab Services using these additional Db2 for i technologies or the Try
& Buy program for the Db2 SMP & Db2 Multisystem features.
