Overlooked Add-Ons for the Integrated Db2 for i

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.