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.
