DBA/DBE Appreciation Day

Not long ago, I discovered that DataBase Administrator(DBA)  Appreciation Day is observed annually on the first Friday in July which happens to be today!  I’ve recently discussed that although some of the traditional DBA tasks don’t apply to Db2 for i, that there is a real need for a Database Engineer (DBE) in IBM i shops. 

Based on our IBM Expert Labs interactions with clients, I think it will be difficult for IBM i shops to show appreciation to their DBEs on July 1 because far too many shops do NOT have a DBE or a DBE team.  Our team often sees the negative impact of the missing DBE during IBM i client engagements where we are brought into assess and analyze their SQL performance & Db2 for i databases. 

Because there’s not a DBE focusing on the database objects and data access, our team regularly sees issues like:

• Queries that are poor performers that can be easily fixed with the creation of an index
• Large tables that have grown past 90% of the Db2 size/row maximum limit – when the limit is reached no more rows can be added by your application!
• Overcommitment of system resources due to a system-wide parallel degree setting of *MAX for the Db2 SMP feature.
• Incorrect usage of SQL routine or program settings that are unnecessarily slowing performance
• Low SQL Plan Cache hit ratio due to runaway usage of QTEMP tables by developers
• Queries being run against tables with 60-70% of the rows deleted because tables are not being reorganized on a regular basis or they’re not configured to reuse deleted rows

It can be a challenge to find a qualified Db2 for i DBE, but that’s where IBM Expert Labs team can help with our DBE skills enablement offerings.  These services can help grown an existing IBM i team member into the DBE role or help convert a DBA from another platform into a DBE. So if you don’t have a DBE on your IBM i team to appreciate today, then make a goal to have one by DBA/DBE Appreciation Day 2023! 

I also discovered that July 1 holds the designation of International Joke Day as well, so I’d be remiss if I didn’t close out this July 1 focused entry with a database-related joke: 

Did you hear about the two relational databases that walked into a NoSQL bar. They left after 5 minutes.... 

    because they couldn't find a table! 🙃

No comments on the quality of my joke unless you’re willing to include your own database joke in the comment 😊

Got Shared CTEs?

This entry title harkens back to the Got Milk? advertising campaign. Who would have guessed that this campaign goes back almost 30 years?!? As they say, time really flies… now, back to the topic at hand.

If you’re reading this entry, hopefully you already know that the CTE acronym in the SQL world stands for Common Table Expression.  Even if you already knew what a CTE is, you may not understand what the term “shared CTE” is referring to.  A shared CTE is the term used to describe any CTE that is referenced more than once on a query definition.

Here’s an example of a query that contains a shared CTE.  In this query, the staff CTE is referenced twice in the main query definition which qualifies the staff CTE to be categorized as a shared CTE.

WITH staff (deptno, empcount) AS
   (SELECT deptno, COUNT(*) FROM employee
    GROUP BY deptno)
SELECT deptno, empcount FROM staff
WHERE empcount = (SELECT MAX(empcount) FROM staff)

In contrast, the following query contains multiple CTES, but no shared CTEs.  Each of the CTEs (top10_2020 & top10_2021) are only referenced once in the main query, so they don’t meet the criteria of a shared CTE.

WITH top10_2020 (customer_name, total_sales
    (SELECT customer_name, SUM(sales_amt) FROM sales
     WHERE year=2020
     GROUP BY customer_name
     ORDER BY SUM(sales_amt) DESC
     FETCH FIRST 10 ROWS ONLY) ,
   top10_2021 (customer_name, total_sales) AS
     (SELECT customer_name, SUM(sales_amt) FROM sales
      WHERE YEAR=2021
      GROUP BY customer_name
      ORDER BY SUM(sales_amt) DESC
      FETCH FIRST 10 ROWS ONLY)
SELECT Y1.customer_name, Y1.total_sales AS sales2020, 

       Y2.total_sales AS sales2021
   FROM top10_2020 Y1 INNER JOIN top10_2021 Y2
       ON Y1.customer_name = Y2.customer_name                

Knowing whether or not a CTE is a shared CTE is significant because an SQL Standards compatibility fix was recently delivered in the IBM i 7.5 release for SQL statements with shared CTEs. This fix had to be made because there’s a possibility that some queries with shared CTEs may return incorrect results if the queries are run while the tables referenced by a shared CTE are being changed. The fix will guarantee as dictated by the SQL standards that each reference to the shared CTE generates the same result set. The fix delivered by IBM may cause some queries with shared CTE references to run slower and some queries with shared CTEs to run faster.

Now, you may be thinking that it will be a long time before your company installs the IBM i 7.5 release, so why pay attention to this change. The reason that you should pay attention is that the code fixes for shared CTEs that were made for the IBM i 7.5 release will eventually be delivered as PTFs for the IBM i 7.4 release (NOTE: Level 23 of the 7.4 Database Group PTF includes the shared CTE fix). IBM recommends analyzing queries with shared CTEs before the PTFs are delivered so that clients understand the possible impact of these PTFs and have time to change their SQL statements, if needed.

To help with this analysis effort, IBM in late 2021 delivered PTFs for IBM i 7.3 and 7.4 that flag SQL statements with shared CTEs in both Plan Cache Snapshot and SQL Performance Monitor collections. In addition, these flags classify whether or not a Shared CTE is estimated to generate a large result set. Shared CTEs that generate a large result set have a greater chance of having performance issues after the fix is delivered as compared to CTEs with a smaller result set size. However, all SQL statements using shared CTEs have the potential to perform differently once the PTFs are applied.

It is possible to predict the possible performance impact of the future PTFs on SQL requests containing Shared CTEs with a simple coding change.  This simple change involves adding the following predicate, AND RAND() IS NOT NULL, to the CTE that is shared (i.e., referenced multiple times) on the SQL request. This predicate forces the Db2 query optimizer to use the same CTE runtime implementation which is used by the fix in the IBM i 7.5 release.

The IBM development team has published a detailed writeup which includes details on the SQL statement flagging that will aid analysis on 7.3 & 7.4 along with possible coding changes that you may want to consider. Our Db2 team in IBM Expert Labs can also be engaged to provide additional assistance, so let me know if we can help.

Before I close, I also want to highlight that the licensing change that I highlighted in last month's entry is effective starting June 1, 2022.  This change converts Db2 Add-On features such as Db2 SMP and Db2 Multisystem into no additional charge features for all of the IBM i 7.x releases (7.1, 7.2, 7.3, 7.4, 7.5)

What's New with Db2 for i 7.5

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 IBM Expert Labs 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. 

Look Before you Merge

The more common version of this title phrase is look before you leap. However, this same recommendation applies to driving a car – if you don’t look before you merge your vehicle into traffic, there’s a good chance that eventually you will either damage your car or cause a car accident. In this entry, you will also see why this is also good advice from a performance perspective when using the SQL MERGE statement.

 

The MERGE statement has been available for use with Db2 for i since the IBM i 7.1 release. The merge support was designed for logic where you’re taking data from a source table/query and using that data to perform either an insert or update operation on the target table. If the row already exists in the target table, then you want to “add” to the existing row by performing an update; if the row doesn’t exist, then a new row needs to be inserted into the target table. In fact, some DBMS products provide this feature with an UPSERT statement since it is a database operation statement that support both insert and update operations.

 

The following MERGE statement merges data into an account summary table by updating the balance from the set of transactions for an existing account and inserting a new balance for the new accounts. 

                 

MERGE INTO account_summary AS a
   USING (SELECT id, SUM(trans_amount) sum_amount FROM trans 
          GROUP BY id) AS t 
     ON a.id = t.id
   WHEN MATCHED THEN UPDATE SET balance = a.balance + t.sum_amount
   WHEN NOT MATCHED THEN
      INSERT (id, balance) VALUES (t.id, t.sum_amount)

While the Insert & Update combo is the sweet spot for the MERGE statement, the syntax actually supports many different use cases including the ability to delete rows from the target table. While recently assisting clients with SQL performance tuning, our IBM Expert Labs team has encountered clients that are using the MERGE statement as an alternative syntax for performing update operations. The same data changes could have been coded with an UPDATE statement, but the developers chose to use the MERGE statement instead. 

 

Developers at these shops made this choice because they found the MERGE statement syntax easier to use.  When you compare these two equivalent data change operations, you can see how some developers may find the MERGE version to be simpler to understand.

MERGE INTO employee 
  USING (SELECT jobcode, jobrate FROM jobinfo) j
      ON (empcode = j.jobcode)
    WHEN MATCHED THEN UPDATE SET emprate = j.jobrate
    ELSE IGNORE
 

UPDATE employee 
  SET emprate = (SELECT jobrate FROM jobinfo j 
                  WHERE j.jobcode = empcode)
  WHERE empcode IN (SELECT jobcode FROM jobinfo)

 

A developer should definitely consider how easy an SQL statement is to code and maintain during the coding process, but a developer also needs to make sure they “look” at all coding factors before taking the “leap”.  Performance was a factor that was overlooked during the development process in the client situations that our team was recently involved with.

 

To be fair, the IBM documentation currently does not clearly spell out the performance considerations for the MERGE statement. So here are some key performance factors based on the internal Db2 implementation that should be considered when you weigh using MERGE as an alternative for an UPDATE request.

–       Query plan will create & populate a temporary data structure identifying the target rows in the target table

–       Merge processing applies update locks to every target row in the target table based on the ON clause before performing any updates. 

–       Merge update process results in extra read lock being applied to every updated row.

–       With No Commit, Update & read row locks are held until the end of the Merge statement

 

The net of these performance factors is that a MERGE statement will acquire twice as many row locks as the equivalent UPDATE statement.  In addition, all of those row-level locks are held by Db2 until all of the target rows have been updated by the MERGE statement even with an isolation level of No Commit.  In contrast, an UPDATE statement running under No Commit will release the row locks as soon as the change to the target row has been completed. 

 

If your MERGE statement only updates a small number of rows in the target table, the overhead causing by the database locking is probably not going to be noticeable. However, MERGE statements that end up changing thousands of rows have the potential to generate significant performance overhead on your system. One client used a MERGE statement to update about 550,000 rows – the statement held over 1.1 million row locks at the time the last row was updated even with an isolation level of No Commit! The equivalent UPDATE statement running No Commit would have held only a single row lock at any point in time while it changed the 550,000 rows. 

 

Like many things in IT, there are tradeoffs with the solutions that you can implement.  Hopefully, you now have a much better understanding of the performance tradeoffs between a MERGE and the equivalent UPDATE statement.

Tools for the Db2 for i DBE

In my prior entry, I reviewed the roles and responsibilities of a Db2 for i Database Engineer (DBE). This time around I want to highlight the tools that Db2 for i DBE should have in their toolbox.

The first tool in the list, IBM i Access Client Solutions (ACS), should come as no surprise. When it comes to Db2 for i SQL performance analysis and management, the ACS SQL Performance Center is the clear choice. This tool enables you to easily drill into the details of the longest running queries on your system with Visual Explain as well as get a reading on the overall health of SQL performance with the Plan Cache properties. 

 

In addition to supporting the creation & management of Db2 for i SQL objects, the ACS Schemas tool also houses the system-wide index advisor and index evaluator.  Index evaluator is not a term you’ll find within ACS, but it’s the label that I use to describe the “Work with -> Indexes” task you can access when right-clicking on a table object to quickly understand which indexes on your system are being used by the optimizer to improve query performance and which indexes are candidates for removal. 

 

The Schemas tool also serves as the launch point for the Db2 Health Center. The Health Center enables you to track how your database objects and activity are doing from the perspective of system and Db2 limits such as the maximum number of rows in table or the maximum size of a table.  My IBM Rochester colleagues recently released a new video highlighting how to access and use the Health Center.

 

The last ACS component to highlight is Run SQL Scripts.  This tool can be used to run SQL database creation scripts and provides access to Visual Explain for detailed SQL performance analysis. One can also launch a graphical debugger to debug procedural SQL objects such as functions and procedures.

 

Run SQL Scripts also facilitates interaction with the second DBE Tool – Db2 for i Services.  There are many services available to help with the managements of Db2 for i object management and performance. For example, there are services that can be used to regularly capture a plan cache snapshot which can be useful when trying to determine the cause of changes in SQL performance.  In fact, the Run SQL Scripts Edit pull-down menu makes it easy to find examples of these services in action so that you don’t have to start from scratch.

For the database modeling and design activities that I highlighted for a Db2 for i DBE, I think there are two viable modeling tools. Back in 2015 before I left for my 5 year stint working in IBM Watson Health, there was much a longer list of industry data modeling tools that I suggested to IBM i clients. After spending the last couple of months reviewing the Db2 for i support in industry data modeling tools, however, I found many of these tools have not kept current with all of the great enhancements made to Db2 for i.  As a result, my view is that IBM InfoSphere Data Architect and XCase for i are the only modeling tools that I would recommend Db2 for i.  The XCase support for Db2 for i also include other interesting data-centric tools that support test data generation and the anonymization of sensitive data. 

 

On the database security front, IBM Security Guardium is a tool that can be helpful to DBEs.  This tool can help find sensitive data in your Db2 for i databases and capture details on how the data in your Db2 for i databases is being accessed.

 

Good tools make any job easier by allowing you to complete tasks more efficiently. If you’re not using some of these DBE tools yet, then now is a great time to start and our IBM Expert Labs team can help you get off to a good start.

Db2 for i DBE Duties

 It’s not too hard to see that the alliteration was on my mind when I came up with the title for this entry. And I could have added one more “D” to the title with “Discussion” or “Definition”.  I view this as a discussion since the exact duties of a DBE (Database Engineer) can vary depending on how responsibilities are divided up within your IT organization. Even if there are other teams that own functions like security, a DBE should function as an extended member of those other teams focusing on the database-specific requirements.

 

On client engagements, our Db2 team in IBM Expert Labs is often asked “What should be the duties of a Db2 for i DBE?”. The short answer is that a DBE performs the same role as a Database Administrator (DBA) on other platforms minus the low-level administrative tasks. And that’s why our team uses DBE instead of DBA to highlight that difference.

 

Recently another blogger wrote about the responsibilities of a traditional DBA, so I thought it would be useful to compare that list with the to-do list of a Db2 for i DBE. You can find the complete DBA writeup here. I’ve summarized that article by copying some of the key high-level topics here and adding my Db2 for i commentary. I have used green to highlights those responsibilities that are exact matches for a DBE and the red for those tasks that are not as applicable to Db2 for i.

·        Data modeling and database design – Going back to the AS/400 days, this design work often was done by the application development team. Unfortunately, this also explains some of the subpar data models that our team encounters. Now that Db2 for i has much a wider set of technology that can be applied to the design, a DBE should be leading these efforts. 

·        Database schema creation and management – Allowing others to create and change Db2 objects is a mistake because it increases the risks that those without deep Db2 knowledge will perform the actions incorrectly resulting in database performance and security issues. 

·        Metadata management and repository usage – “Data about the data” is important. Probably a small part of a DBE’s job, but they must support the company’s efforts to document the business data. 

·        SQL code reviews and walk-throughs – Another intersection point between a DBE and the developers. These reviews are a great way to prevent query performance problems. In the process, developers learn more about SQL programming performance best practices.

·        Programming and development – A DBE doesn’t need to worry about the state of an SQL program object causing performance problems. Traditional DBAs often have to BIND/REBIND SQL programs when the database or server has changed to notify the optimizer about the change. The Db2 for i engine automatically recognizes changes that have been made to the database or server changes that may impact performance.

·        Procedural coding and debugging – With proliferation of procedural SQL, a DBE needs a good understanding of these SQL objects. In addition, procedural SQL makes it easy for a DBE to programmatically use Db2 for i utilities and services for database maintenance.

·        Performance management and tuning – This role is a no-brainer. On a system with SQL usage, a DBE should be continually focused on proactive and reactive performance tuning. A solid indexing strategy is a critical success factor in this area.

·        Ensuring availability, Backup and recovery – Both of these tasks are usually handled at the system-level in IBM i shops, but a DBE needs to be aware of the database impacts for the different types of system approaches to keep database availability high and providing input to the system strategy. 

·        Data security – A DBE is going to be working hand in hand with the security administrator to ensure the best database controls are used to meet and enforce the company’s security policy.

·        Data movement, Data archiving – DBE should be leading these efforts to ensure that data is moved/archived safely and efficiently to minimize impacts on system performance.

·        Ensuring data integrity – The other article highlights the use of database constraints and triggers to ensure data integrity. However, I would argue those are options a DBE would consider in the database design. The reason this topic is highlighted as not applicable for Db2 for i databases is because it talks about a DBA ensuring the structural integrity of database – looking for corruption in the disk page and structures associated with table and index objects.

·        Storage management techniques – Thanks to IBM i single-level storage, a DBE doesn’t have to spend time allocating disk storage for Db2 for i objects and trying to evenly spread the Db2 object data across disk devices to minimize hot spots. As Db2 for i objects grow or shrink in size, IBM i automatically handles the requirement for more or less disk space.

·        Capacity planning – Yes, a DBE needs to understand future data growth requirements for their databases, but this work is done at a much higher level. This is due to the fact that IBM i single-level storage simplifies the storage allocation and management for Db2 for i objects.

 

Hopefully, you can see how this list from the other article easily reinforces the proposition that a Db2 for i DBE should be focused on the same work as traditional DBAs minus a lot of the low-level administrative tasks. Thanks largely to Db2 for i and IBM i operating system automating many of those low-level tasks.

 

As I wrap up this discussion, I wanted to share a discovery that I made while putting this entry together. I learned that the DBA acronym is also used for the phrase “Don’t Be Afraid”.  One could make a compelling argument that IT organizations with a DBA don’t have to be afraid because the DBA role has their business data covered.  These orgs have less risk because they’ve dedicated resources to making sure that their databases are secure, available, extensible, and scalable. And that’s why our Expert Labs team continues to remind IBM i clients about the importance of staffing the DBE role… it minimizes the risks associated with your companies most valuable asset – the business data stored in Db2 for i.

NOTE: After this entry was originally published, a follow-on entry highlighted Tools for the Db2 for i DBE

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. 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  IBM Expert Labs. 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 IBM Expert Labs using these additional Db2 for i technologies.