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.

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!