Don’t “Fall” Behind, Rake In the Latest Db2 Enhancements

 This entry title should make it easy to guess that my recent activities in Rochester, Minnesota have involved cleaning up the colorful leaves that drop in the Fall and enjoying the great outdoors while raking. While this time of year is a great for enjoying the cooler temperatures outside and the unique colors produced by Fall, it’s also a time of year when IBM i Technology Refreshes (TRs) are normally announced – that in fact occurred this week for the IBM i 7.4 & 7.5 releases.

A new IBM i TR signifies there are new Db2 for i capabilities that can make your job easier which means you and your company will be left behind without a plan to load the latest Database Group PTFs. Here I highlight some of the more interesting Db2 enhancements associated with the newly announced IBM i TRs. You will want to reference the Db2 for i Technology Updates site for a complete list of the enhancements.

Normally the Database Group PTFs contain a set of smaller Db2 enhancements, but this latest iteration contains a couple of major enhancements. These doozies include the integration of Watson technologies into Db2 for i in the form of Geospatial Analytics support and a new SQL Error Logging Facility.

Instead of having to reach out to an IBM Watson service to analyze spatial data, Geospatial Analytics enables all of this data to be stored and analyzed in your Db2 for i databases. This integrated support includes new data types for storing spatial data and new functions to process, analyze, and compare this spatial data. 

The following example demonstrates usage of the ST_POINT data type to store the geographic location of a store in the location column and the ST_POLYGON type to capture the geographic area that store location serves in the sales_area column. 

CREATE TABLE stores
 (id INT, address VARCHAR(100), city VARCHAR(50), 

  postal_code VARCHAR(5), location ST_POINT, 

  sales_area ST_POLYGON)

The following Update statements shows one of the Spatial functions, ST_BUFFER, in action. This new function is used to update the sales area column for a specific store to a circular area around the store location with a diameter of 40 kilometers.

UPDATE stores
  SET sales_area = ST_ToPOLYGON( ST_BUFFER(location, 40000) )
  WHERE id = 33

With that geographic sales area now define, you can use the ST_WITHIN function as demonstrated on the following query to identify customers that are located with the store’s sales area (this assumes you have the geographic location information for your customers). The result set generated by this query would enable your company to easily target only the customers that reside near this new store location.

SELECT c.first_name, c.last_name, c.mailing_address, c.city,

       c.postal_code
FROM customers c, stores s
WHERE s.id = 33 AND ST_WITHIN(c.location, s.sales_area)=1

The new SQL Error Logging Facility is also known as SELF. The SELF acronym reinforces that this new Db2 feature makes it easier for you to pinpoint specific SQL errors or warnings. This facility can be used to log information for a specific SQL error systemwide or scope the tracking to a specific job. The logged information not only includes the SQL statement that caused the error/warning, but it also includes the call stack for the job that first encountered the error/warning.

 

The new REPLICATION_OVERRIDE global variable can be used to simplify the process of copying or deploying your database tables to new systems. Previously if your tables contained generated columns based on special registers such as CURRENT CLIENT_PROGRAMID or built-in global variables like QSYS2.JOB_NAME, there was no way to override the generated value behavior like you could for identity or row-change-timestamp columns. This new global variable can be used to control the automatic value generation for all generated column types.

On the performance front, there’s another new option to control the number of queries on your system that can the leverage the parallel processing enabled by the Db2 SMP feature. The new PARALLEL_MIN_TIME option adds another parallel processing control to complement the PARALLEL_MAX_SYSTEM_CPU option delivered with GA of the IBM i 7.5 release. Parallel processing is best applied to longer runner queries on your system because there’s overhead involved in Db2 breaking a query into multiple parts that can be run across multiple processors concurrently and then gluing the results from the parallel threads back together. The new PARALLEL_MIN_TIME option enables you to have the Db2 query optimizer only consider applying parallel processing to queries that run longer than the specified minimum. The default value for this option is 60 seconds and is only applicable when the parallel degree value is *OPTIMIZE.

The Db2 for i graphical interface provided by IBM i Access Client Solution (ACS) also provides several Db2-related enhancements. If you spend as much time using ACS Run SQL Scripts as me, then I think you’ll agree one of the biggest improvements is the ability to enable Auto Save for your Run SQL Script sessions.

I’ve highlighted just some of new Db2 for i features that will be available with the upcoming Database Group PTFs for the IBM i 7.4 and 7.5. You need to make time to review the complete list and to make a plan to get these enhancements loaded on your system, so that you don’t “fall” behind in terms of the Db2 features and functions available for you to use.