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!
