DB2 for i 7.2 TR3 and 7.1 TR11 Features

I was writing up some exciting stuff on the new IBM i 7.3 features when I realized I never finished reporting about other recent DB2 features! Below are highlights of some newer features offered starting in DB2 for i 7.2 TR3 and 7.1 TR11.

Assign A System Name To A Global Variable

Recall that when you create a global variable, behind the scenes SQL Server implements the variable’s logic and value retrieval logic as a service program. The following variable definition with a long name (>10 characters):

CREATE OR REPLACE VARIABLE DEV.AVENGERS_TOWER VARCHAR(32)
DEFAULT (SELECT ADDRESS FROM DEV.HERO WHERE NAME='Tony Stark');

This is implemented as a C ILE service program with the following system name and text:

AVENG00001  SQL VARIABLE AVENGERS_TOWER

Of course, it can be annoying to have your objects named this way. As with many other SQL objects, there is now an option to control the system name of the variable’s service program:

CREATE OR REPLACE VARIABLE DEV.AVENGERS_TOWER
FOR SYSTEM NAME A_TOWER VARCHAR(32) 
DEFAULT (SELECT ADDRESS FROM DEV.HERO WHERE NAME='Tony Stark');

With the new syntax, the global variable still retains the long name but the system name is now A_TOWER.

Updateable Views That Reference Global Variable(s)

As long as we’re on the topic of global variables, if you have used them in a view you probably noticed that the reference to the global variable caused DB2 to consider the view as read only. If you’re on the latest DB2 PTFs, you can create a view that uses global variables.

CREATE OR REPLACE VARIABLE LARGE_ORDER_VALUE
DEC(19,4) DEFAULT 10000;

CREATE OR REPLACE VIEW v_SalesOrderHeader_LargeValue
AS
SELECT *
  FROM SalesOrderHeader
 WHERE SUBTOTAL>=LARGE_ORDER_VALUE;

You can verify that the view is updatable by issuing some DML statements against it (INSERT/UPDATE/DELETE/MERGE), or query the view’s properties in the SYSVIEWS system catalog:

SELECT TABLE_NAME AS VIEW,
       IS_UPDATABLE,IS_DELETABLE,IS_INSERTABLE_INTO
  FROM QSYS2.SYSVIEWS
 WHERE TABLE_SCHEMA=CURRENT_SCHEMA
   AND TABLE_NAME='V_SALESORDERHEADER_LARGEVALUE'

There is a little quirk with SYSVIEWS in that columns IS_UPDATABLE and IS_DELETABLE return a Y or N, while IS_INSERTABLE_INTO returns YES or NO.

The combination of global variables and views is powerful because it allows views to have a limited yet flexible parameterization-like ability (similar to table functions) yet maintain the view’s simplicity (many database tools can’t access table functions) and updatability.

LOCATE_IN_STRING function

LOCATE_IN_STRING is a new scalar function in DB2 for i that is useful for locating the position of a specific substring within a string (similar to the LOCATE function). This function accepts up to four parameters (the last two are optional): source string, search string, start position (within the source string), and instance. In case your search can potentially find multiple strings, the last parameter, instance, is an integer that specifies which occurrence of the find you want to return.

The sample below uses LOCATE_IN_STRING to examine XML text and return the position of the fourth equals (=) symbol within the string. Thereafter, the SUBSTRING function is used to extract the zip code that follows:

WITH CTE_DATA(ADDRESS) AS (
VALUES 
('<address street="123 My Way" city="Beverly Hills" state="CA" zip="90210" />'),
('<address street="456 Your Way" city="Grand Rapids" state="MI" zip="49503" />')
)
SELECT SUBSTRING(ADDRESS,LOCATE_IN_STRING(ADDRESS,'=',1,4)+2,5) AS ZIP
  FROM CTE_DATA

The result is:

ZIP
90210
49503

This example is a little contrived because the code expects that the zip code will always follow the fourth equal sign and that a U.S. zip code will always be five digits. I had a hard time figuring out a use for this function (as a single execution, not necessarily a loop) that couldn’t be accomplished more easily with the REGEXP_SUBSTR function. For example, REGEXP_SUBSTR can do the same thing and is more flexible in the event that the XML format changes (though I assume REGEXP_SUBSTR requires more overhead):

WITH CTE_DATA(ADDRESS) AS (
VALUES 
(...)
SELECT REGEXP_SUBSTR(ADDRESS,'zip="(d{5})"',1,1,'i',1) AS ZIP
  FROM CTE_DATA

As a second example, LOCATE_IN_STRING can be used to validate phone numbers or other strictly formatted values. In this case, the phone number is expected to have two dashes. If a second instance of a dash isn’t found the number is returned as “invalid”:

WITH CTE_DATA(PHONE) AS (
VALUES 
('636-405-2114'),
('624-891-3201'),
('616-7242400')
)
SELECT PHONE AS INVALID_PHONE
  FROM CTE_DATA 
 WHERE LOCATE_IN_STRING(PHONE,'-',1,2)=0

Returns one row:

INVALID_PHONE
616-7242400

Of course an invalid phone number of 616-4-234 would pass this test and further, developers don’t often have the luxury of depending on formatted data, so I’m still of the opinion that the REGEXP functions can do this type of work even better than LOCATE_IN_STRING.

OVERLAY Function

The new overlay function is a specialized type of string replace function that offers the ability to specify a source string position and length (rather than a search string) and a target string (or string compatible data type).

The parameters for the overlay function are:

• Source string
• Replacement string
• Source string starting position
• Number of characters to replace

Consider this example:

VALUES OVERLAY('Order ooo was created on '||CURRENT_DATE, 105320, 7, 3);

The text “ooo” in the source string is an arbitrary placeholder that is intended to show the position where an order number of varying length should be inserted into the string. The example’s order number is 105320. The replacement occurs at position seven in the source string and will replace the existing three characters (‘ooo’) at position seven. When run, the above expression returns:

Order 105320 was created on 2016-01-22

An alternative syntax for OVERLAY that produces the same result is shown here:

VALUES OVERLAY('Order ooo was created on '||CURRENT_DATE PLACING 105320 FROM 7 FOR 3);

This makes the function usage a bit lengthy but it is easy to understand the purpose of the parameters.

OVERLAY functions can be nested. Consider the following example where the placeholders “x” and “y” are intended for replacement with a library name and timestamp:

VALUES OVERLAY(OVERLAY(
'Library x was backed up at y',CURRENT_TIMESTAMP,28,1),'MYLIB', 9, 1);

The function returns something like this:

Library MYLIB was backed up at 2016-01-02-13.22.15.980095

When doing multiple substitutions with OVERLAY, it pays to work backward when you have variable length substitution values. In the above example I substituted the current time in the “y” portion of the source string starting in position 28 followed by the library name starting in position 9. If I had inserted the library name first, that would throw off the starting position of the timestamp marker. A noteworthy restriction of OVERLAY is the exact position must be supplied. If the position can change (i.e., a non-literal value) another function is required to calculate the position which can quickly make code difficult to understand.

Overall, except for trivial problems, I think the REGEXP functions offer more flexibility than LOCATE_IN_STRING and OVERLAY. Nevertheless, LOCATE_IN_STRING and OVERLAY are important additions because they exist in other DB2 products and therefore their membership in DB2 for i increases cross-platform code compatibility.

Keep Up With The Times

It’s important for developers and administrators to keep up with the tools in the DB2 toolbox. Using new features where appropriate is a boon when writing and maintaining DB2 code.

Michael Sansoterra is a DBA for Broadway Systems in Grand Rapids, Michigan. Send your questions or comments for Mike via the IT Jungle Contact page.

RELATED STORIES

Native Regular Expressions In DB2 For i 7.1 And 7.2

New in DB2 for i 7.1: Use Global Variables to Track Environment Settings