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.

Determined to Boost your UDF Performance?

I started off this year highlighting how you can streamline the performance of SQL functions, procedures, and trigger, so it seems natural to end the year by spotlighting another option that can improve user-defined function (UDF) performance. 

 

Based on the SQL performance assessments that my IBM Expert Labs team performs for clients, the usage of UDFs by IBM i developers is on the rise. This increased usage of both SQL and external user-defined functions makes sense since UDFs are a vehicle to facilitate good modular programming and to enable SQL access to calculations and transformations already written in high-level language programs.

 

The SQL performance assessments also reveal that IBM i developers are often creating their UDFs with default options which unfortunately are not the best performing options. The default options of NOT DETERMINISTIC and FENCED limit Db2 for i’s ability to deliver maximum performance when a UDF is invoked. It’s definitely worth your while from a performance perspective to not just accept those default settings, but to explore if those options can be changed on your UDF definitions.

 

I discovered that while I was off working on IBM Watson that the Db2 for i development team delivered a new flavor of the deterministic option known as STATEMENT DETERMINISTIC. This new deterministic flavor provides more flexibility as you consider moving your UDF away from the NOT DETERMINISTIC default. This article that I wrote explains this newer deterministic option in more detail. 

 

If you need help determining how to streamline the performance of your functions - deterministic or not, IBM Expert Labs is here to help.

 

That’s a wrap on 2021 - have a Merry Christmas & wonderful holiday season and we’ll talk in 2022!

To Reorg or Not to Reorg

 After thinking about a “To Be Or Not to Be” type of title in last month’s entry on database masking, it was an easy title choice for this entry. I don’t think that a discussion of whether or not to perform Db2 table reorganizations should be as controversial as the topic of masking, but I've been surprised before…

The IBM Expert Labs team recently received a question from client asking if it is still worth the time to reorganize tables in light of today’s faster disk technologies. The main benefit of running the RGZPFM command is to remove/compress deleted rows from a Db2 table. And given that disk technologies such as IBM Flash Storage can retrieve database rows from disk significantly faster than spinning drives, are my applications really going to notice deleted rows being paged into memory? 

The short answer is: yes, application and system performance can be negatively impacted. While today’s disk technology is faster, disk operations on deleted rows are wasteful and those deleted rows unnecessarily increase the memory working set size for an application.

Deleted rows can also waste CPU resources when Db2 performs a scan operation on a table with deleted rows. Assume that the customers table being referenced in the following query contains 1 million rows and there are 300,000 rows in the table that are deleted.

SELECT * FROM customers WHERE company_name LIKE '%INC.'

A Table Scan is the only access method that Db2 can use in the runtime implementation of this query because of the wild-card search criteria that looks for company names ending with ‘INC.’ (This statement is mostly true, but the IBM OmniFind Text Search Server is a topic for another day). The Table Scan method has to process every row in the table whether it’s deleted or not. Obviously, no deleted rows will be returned in the query result set, but this query will waste CPU resources on 300,000 rows checking if the rows are active or deleted. This unneeded processing is definitely something that can impact the scalability of your applications and systems.

 

Of course, it goes without saying that deleted rows affect disk storage requirements. In addition, deleted rows also count against the maximum storage size and row count for a Db2 table.  And yes, there are clients that have gotten close to the 1.7 TB size limit and the 4.2 billion rows limit for a table. 

Another reason to perform table reorgs is that IBM has enhanced the RGZPFM command through the years to minimize the time that the table is unavailable to your applications. Parallel processing, reorganize while active, and the ability to suspend a reorg request are all recent additions that make it easier/faster to perform reorgs on tables. Here are some useful links that provide more details regarding these different types of reorg options:

• RGZPFM Options Comparison
• RGZPFM FAQ

Assuming that you’re now sold on the benefits of reorgs, the next logical question is: When should a table be reorganized? A good rule of thumb is to wait until the table has a deleted row percentage of around 20%. This site outlines a method to determine the deleted row percentage for a table. It's also important to remember that the Reuse Deleted Rows feature can be used to reduce the number of times that a table has to be reorganized.

 

Happy RGZPFM’ing & Happy Thanksgiving to my US readers!

Masking your Db2 Databases to Protect Sensitive Data

I thought about titling this entry “To Mask or Not to Mask” to generate more visitors to the blog, but then I quickly thought better of it😊  While I did pickup some medical knowledge during my 5+ years of working on Watson Health solutions, I definitely do not have the credentials to discuss medical masking.  However, with over 25 years of experience working on Db2 for i, I am qualified to talk about protecting sensitive data in your databases with the Db2 masking support.

Before I highlight the Db2 masking support, I want to remind everyone that a solid object-based security model is the first step in securing all of the valuable business data that you store on IBM i. Masking is meant to be used as a granular security control after your Db2 for i objects have been secured with IBM i’s robust object-level security.

The column mask support was first delivered in the IBM i 7.2 release as part of the new security functionality known as Row and Column Access Control (RCAC). RCAC support is made available through the optional, no charge feature called IBM Advanced Data Security for i, also known as option 47 of the IBM i operating system.

A user authorized to a Db2 table object can view all of the column values stored in that table. A column mask enables you to provide another layer of protection around columns storing sensitive values like credit card numbers and tax identification numbers. This extra layer allows you to limit access to a column with sensitive data values to just a subset of the users that have object-level access.

For the moment, assume that you’re a retailer that stores credit card numbers in the orders table. The following mask definition only allows the card number to be visible to users that belong to the customer support group profile.  All other users (security administrator, programmers, etc.) are only allowed to view the last 4 digits of the credit card numbers stored in the cardnum column. 

CREATE MASK mask_cardnum ON orders FOR COLUMN cardnum
RETURN
   CASE WHEN VERIFY_GROUP_FOR_USER(SESSION_USER , 'SUPPORT') = 1
    THEN cardnum
    ELSE OVERLAY(cardnum PLACING '*************' FROM 1 FOR 12)
   END ENABLE 

Even though a column mask is defined with SQL, the masking is enforced on all SQL and non-SQL interfaces. Consult the Row and Column Access Control Support in IBM Db2 for i Redpaper for additional details. 

While a column mask is enforced on all interfaces that directly access the column, there is a security exposure that arises when a column is compared to a host variable or literal value on a query request.  A security exposure exists because the Db2 for i performance tools (e.g. Plan Cache) capture the value of the host variable or literal from the query execution since the actual value that is passed in can affect the query optimizer’s choice of data access methods.  

To highlight this exposure, let’s assume that this SQL statement is embedded within one of your programs.

   SELECT ordID INTO :hv1 FROM orders WHERE cardnum = :hv2

A developer that has access to the Db2 Performance tools on your system, uses that tooling to launch Visual Explain to analyze the performance of this Select statement. Notice below in the captured Visual Explain output that the both the SQL statement text and the host variable values are shown. 

Although the highlighted host variable value is not guaranteed to be stored in the orders table, there’s a high likelihood that it is one of the stored sensitive card numbers since the application program is searching for that value. 

To close this security exposure, IBM provides the SET_COLUMN_ATTRIBUTE service in the SYSPROC library. This service allows you to register your database columns that store sensitive data.  Once a column has been registered, Db2 for i will start masking the host variable or literal values compared to it during its collection of performance data. 

In our example, the following store procedure call is made to secure the cardnum column:  

CALL sysproc.set_column_attribute

                       ('DBLIB', 'ORDERS', 'CARDNUM', 'SECURE YES')

Now that this column has been registered, the following image shows Visual Explain displaying a masked value of *SECURE instead of a credit card number. 

The secure column attribute setting applies to data collected by the Plan Cache, Plan Cache Snapshot and the Database Performance Monitor tooling.  A value of *SECURE will be displayed for all users except the QSECOFR user.  

The SET_COLUMN_ATTRIBUTE should be used to prevent sensitive data leaks independent of any plans that you have for employing column mask definitions in your database.

Hopefully, you now see how Db2 masking functionality can add an extra layer of security to your database object-level security implementation.  Feel free to reach out if your company is need of assistance of securing its most important asset – data.

Give Web Service Calls a Rest with Newest Db2 Update

 One might imply from the title that I’m advocating that developers stop using SQL to invoke web services from their IBM i applications. That is not my intent at all! In fact, the Db2 enhancements associated with the newest IBM i Technology Refresh for the IBM i 7.3 and 7.4 releases make it even more compelling to use SQL to invoke web services from IBM i applications.

The reason that SQL invocation of services is more compelling with the latest updates is that Db2 for i now supports the following set of new HTTP functions in QSYS2. These HTTP functions allow SQL to use RESTful services without the overhead of creating a Java Virtual Machine (JVM) like the existing HTTP functions found in SYSTOOLS.

HTTP_GET	HTTP_GET_VERBOSE
HTTP_POST	HTTP_POST_VERBOSE
HTTP_PUT	HTTP_PUT_VERBOSE
HTTP_DELETE	HTTP_DELETE_VERBOSE

The elimination of the JVM requirement means that web service invocations will run faster and more efficiently on your IBM i server. IBM i applications can scale to support more users and more transactions with these new HTTP functions requiring less usage of CPU and heap temporary storage. More details from my IBM colleagues on these HTTP functions can be found here.

The latest Database Group PTFs also include a new Db2 for i service, ACTIVE_QUERY_INFO, that can be helpful in narrowing down your analysis and investigation of query performance issues.

While not delivered with the Database Group PTF, the newest IBM i Access Client Solutions (ACS) version also includes some nice add-ons for Db2 and SQL. These ACS updates include Run SQL Scripts supporting tighter transaction control as well as improved error feedback.

To demonstrate the improved Run SQL Scripts error feedback, let’s use a simple query that divides two integer columns: SELECT Col1/Col2 FROM Tab1. The following figure shows the output from the latest version of ACS Run SQL Scripts. In this example, the second row in table Tab1 has a Null value for Col2 and the fourth row in table Tab1 has a value of 0 for Col2. Those Col2 values explain why the result set returned by this simple query does not contain a numeric division result in the second and fourth rows. 

The “-“ value indicates a Null value was returned for the division result which makes sense since the divisor value in the second row was Null.  The “++++++++++++” value in the last row indicates an error occurred due to the divisor value being 0.  Prior to this latest enhancement, Run SQL Scripts would have returned the same “-“ value for both the second and fourth rows in the result set making it difficult for the user to detect that a divide by zero error had occurred during the execution of their query.  Disclaimer – this improved error feedback was actually part of the prior ACS update, but I forgot to highlight this enhancement in my coverage of the prior Db2 for i update.

These great enhancements are a good reminder about the importance of keeping both your Database Group PTF level and ACS versions current. 

Speed Dating Your Legacy Dates

Given that I’ve been happily married for over 30 years, I’ll have to admit that I have no experience with speed dating. However, I do have lots of experience seeing the many ways in which clients store date values in legacy formats in their Db2 for i databases.  These legacy date formats range from storing the entire date value in a single character or decimal column to storing the components of a date value (Year, Month, Day, Century) in separate columns.  This blog topic focuses on the intersection of performance (i.e., speed) and legacy dates because our team has also seen many customer SQL requests that consume a fair amount of system resources while transforming these legacy date values into SQL date values.

IBM certainly contributed to this use of legacy date formats since Db2 for i has not always supported a Date data type. The Date data type was not available to use until the V2R1.1 release of OS/400. While our Expert Labs team certainly works with many clients now using the Date data type, there are a significant number of clients still using Db2 for i tables that were designed and created prior to that release thanks to the strong backward compatibility of our great platform.

The large number of legacy date transformations on SQL requests are not a surprise due to the continued rise in SQL adoption by IBM i developers and SQL’s rich support for date processing. This support includes many SQL functions that make it easy to convert numeric and character date values into real SQL date values.

Here’s a sampling of some of the legacy date transformations that have shown up on IBM Expert Labs client engagements. Other clients have created their own user-defined functions(UDFs) to perform the legacy date conversions. 

   ==> TIMESTAMP_FORMAT(CHAR(DateDec8) , 'YYYYMMDD')
   ==> DATE((DTYEAR+2000) || '-' || DTMNTH || '-' || DTDAY)
   ==> ((YRVAL*10000) + (MTHVAL*100) + (DAYVAL))

While the built-in functions and UDFs make it easy to transform the legacy date formats, these transformations are CPU intensive.  If these transformations are performed on thousands of rows in your table, then the CPU costs start to add up. Especially, when you consider that it’s the same legacy date values being converted over and over again. And that’s exactly where speed dating of your legacy date values can help.

Speed dating of legacy date values involves pre-converting your legacy date values and storing them in a Date dimension or lookup table. The same SQL built-in and user-defined functions can be used to populate the table.  Once this lookup table has been populated, your SQL queries can simply join to the table whenever you want to use the equivalent SQL date value associated with your legacy date value.  Joining to a dimension table is a low-cost alternative to converting your existing Db2 tables to use the Date data type.

The following graphic visually depicts the join to a Dates lookup table from a legacy Orders table which has date values stored in a legacy character column.

The ORDERDATE column is storing a date value in an 8-byte character field.  The Dates lookup table has that legacy date value stored in the DC_MDYY_CHAR column and the equivalent SQL date value in the DC_DATE column. When an SQL query wants to display or process the ORDERDATE column as an SQL date value, it can just join to the Dates dimension table on the ORDERDATE and DC_MDYY_CHAR columns and then reference the DC_DATE column.  The SQL date value is accessible with a simple lookup instead of a CPU intensive transformation.  Assuming that the proper indexes are covering the join columns, a join can be processed by Db2 very efficiently and quickly. Even if you have 50 years of dates stored in the lookup table, the table is only going to have just over 18,000 rows – a small table when it comes to join performance.

Notice that in this example that the Dates dimension table also is being used to store contextual information such as Day of Week number and Day Name.  These contextual date values were also pre-calculated so they are also ready to be used by your SQL request without any dynamic calculation.  You could easily add other contextual date values such as business fiscal year or quarter to the lookup table. This Db2 Web Query Redbook has more details on creating and using a Date dimension table. Even better, Db2 Web Query also ships with a utility to create your own dimension table.

So how much performance savings can be realized with speed dating? As with any performance question the answer is “It Depends”.  One client recently made the switch to using a Date lookup table because they had an SQL query which performed many legacy date transformations using this pattern: DATE((DTYEAR+2000) ||'-'||DTMNTH||'-'||DTDAY). Obviously, your mileage may vary depending on the query complexity and the number of rows being processed.  

Feel free to reach out if you need assistance applying this speed dating technique to the legacy dates stored in your Db2 for i databases.

Tale of the Tape - SYSDUMMY1 vs Tableless SQL Performance

 I thought about titling this entry “Tableless SQL for Dummies” to play off the old Dummies book series and the focus of this entry: the Db2 dummy table, SYSDUMMY1.  However, I thought that might be a little too abrasive for some readers.  Plus, that catchy title doesn’t reflect that SQL performance can be improved by simply replacing SYSDUMMY1 queries with a tableless SQL request. This Tale of the Tape comparison will help you better understand this performance difference.  

You may or may not know that Db2 provides a dummy table, SYSDUMMY1, in the SYSIBM schema.  The SYSDUMMY1 table contains a single row and column which as you can see from the following example makes it really easy for a developer to invoke an SQL function or retrieve the value of an SQL special register. 

SELECT UPPER(:hv), CURRENT TIMESTAMP
  INTO :hv1, :hv2
FROM sysibm.sysdummy1

Back when I was working with software vendors, the SYSDUMMY1 table was also quite handy when porting Oracle applications to the IBM i platform because some of those applications relied on using the Oracle dummy table - DUAL.  Database vendors often included a dummy table because all SQL DML (Data Manipulation Language) statements required a table reference. However, that’s no longer case because the SQL standard has been enhanced to support tableless queries.

The VALUES statement was the new addition to the SQL standard which enables you to run tableless queries.  Taking the previous example, you can see how it is implemented with the tableless support:

VALUES (UPPER(:hv), CURRENT TIMESTAMP)
  INTO :hv1, :hv2

These two examples are almost identical except for the FROM clause. Not only does the VALUES statement save you from having to type the FROM clause, it also will return the results faster because there is no FROM clause! Let’s dig into why the FROM clause causes a performance difference. 

 

Any time there is a table referenced on the FROM clause on an SQL request, the Db2 for i engine has to performance serialization actions to support concurrent access of the specified table and its rows. Even though the SYSDUMMY1 table is small and contains a single row, the required serialization steps makes it slower to process than the equivalent tableless query. 

 

To measure this performance difference, I created separate SQL procedures with each one of them running these two equivalent SQL statements 50,000 times. 

SELECT CURRENT TIMESTAMP INTO :hv1 FROM sysibm.sysdummy1

VALUES CURRENT TIMESTAMP INTO :hv1

The average run time for the SELECT statement procedure call was 1,430 milliseconds while the average run time for the VALUES statement call was 340 milliseconds – a 4x times difference in performance!  While you might be thinking that the average execution time of each method is very small when you consider each statement was executed fifty thousand times, remember the old proverb that “Little by little, a little becomes a lot”.

You might find that this type of SYSDUMMY1 request is being run over and over again on your system.  In fact, I recently reviewed a customer’s SQL Plan Cache Snapshot data and they had a SYSDUMMY1 query that has been run over 360 million times on their system.  So even if the tableless query approach was just 100 microseconds faster, the multiplicative impact of that small improvement will add up to make a difference on their system. 

The performance advantages that tableless SQL requests hold over SYSDUMMY1 queries should make it clear that tableless queries are the clear winner in this comparison.