Spring Forward with New Db2 for i Features

Spring Forward is a phrase that is often used in the United States to remind people which direction to move their clocks when Daylight Savings Time begins. I’m not going to debate Daylight Savings Time here since it’s a highly charged issue for many people. However, there is no disputing that many of us associate springtime with an adjustment to our clocks. 

Many in the IBM i world are also accustomed to IBM announcing new refreshes or releases for their favorite system around this time of the year. Spring 2023 follows that pattern with the recent announcement of Technology Refreshes (TRs) for the IBM i 7.4 and IBM i 7.5 releases. Like usual, these refreshes include new Db2 for i functionality to help you move your applications and databases forward.

Here are some of my favorite new Db2 capabilities in the recently announced TRs:

Those of you that have already upgraded to IBM i 7.5 will benefit from the extension of the RESTRICT ON DROP support. The RESTRICT ON DROP support initially could be used to prevent the accidental deletion of Db2 tables and physical files. Tables or physical files created with this option cannot be deleted with the SQL DROP statement or the DLTF CL command until the RESTRICT ON DROP setting is removed. Now, the RESTRICT ON DROP support can be used to prevent the accidental deletion of stored procedures and user-defined functions. This new support can be used with both the SQL and external flavor of stored procedures & functions.

Geospatial Analytics was a big addition to Db2 for i in 2022 and that support was also enhanced with a new scalar function - ST_GEOHASHVALUE.  The use of geohash values is a great way to speed up the performance of many solutions which integrates the Geospatial Analytics support.

As you’ve come to expect, new and enhanced IBM i Services are often found in IBM i TRs and that holds true for the 7.4 and 7.5 TRs announced this week. The new RDB_ENTRY_INFO view is a great addition from a Db2 perspective. Now, SQL can be used to easily retrieve your database directory entry details instead of having to run the Display Relational Database Directory Entries (DSPRDBDIRE) CL command.

IBM i Access Client Solutions (ACS) is a key tool for the support and management of your Db2 for i databases. Not surprisingly, it also includes several new enhancements that make working with SQL and Db2 for i easier. The biggest enhancement is the prompting support for Run SQL Scripts. Frequently, the SQL statements run by applications reference host variable or parameter markers like the following statements:

SELECT COUNT(*) FROM qsys2.systables WHERE table_schema = :H 

SELECT orderkey, quantity  FROM orders 

  WHERE shipdate BETWEEN ? AND ?

While this is a great SQL coding practice, it does make life difficult for anyone that wants to run these SQL requests from ACS Run SQL Scripts in order to analyze performance or debug an issue. Before these types of SQL statements can be run, all the host variables and parameter markers needed to be manually replaced with literal values. 

This cumbersome, manual replacement process has been eliminated with the new Run SQL Scripts prompting support. All you need to do is copy and paste the SQL statement text into Run SQL Scripts. When you go to run the SQL statement, ACS will automatically detect that there are host variables or parameter markers that need to be replaced and will prompt you for the replacement values as shown below.

You just enter the appropriate values into the input field(s) and hit the “OK” button. ACS will then run the SQL request with your input values.  Notice that the right-hand side of these two input windows include the data type definition of the column being compared (or assigned) which make its easier for you to supply a compatible replacement value.

Again, these are just a few of the highlights from the latest IBM i TRs, so make sure you take the time to review the complete list to see if there are other new features that can make your life easier and move your IBM i applications forward!