Blast it! Another suite of custom code I have written and used over the years has recently been deprecated (or partially deprecated) by IBM. The good news is that regular expressions (abbreviated RegEx) are now a native part of DB2 featuring one new predicate (REGEXP_LIKE) and four new scalar functions: REGEXP_COUNT, REGEXP_INSTR, REGEXP_SUBSTR, and REGEXP_REPLACE.

For IBM i 7.1, TR9 must be installed and for IBM i 7.2, TR1 must be installed. The new Regular Expression Functions Require licensed product #39 5770-SS1 International Components for Unicode to be installed on both IBM i 7.1 and 7.2. (This product is

In Joining On Ranges, I demonstrated that range tables are a practical replacement for attribute columns. As a rule, ranges should not overlap. (Perhaps there are exceptions.) Here’s why, and also what to do to prevent overlapping values.

Overlapping ranges cause too many rows of the range table to join to a single row of another table, which in turn causes too many rows in the result set. For example, assume the following range table.

FROMITEMNUMBER  THRUITEMNUMBER  ITEMCATEGORY
    134             134999           1
    1341            134199           1
    488             488999           2
    2               299999           3

Item 134120 would join to the first two rows

I see developers make one mistake way more often than any other. They assume that the job user name also represents the user profile under which a job is currently executing. This is, and always has been, an invalid assumption. The job user name only represents the userID under which the job was originally started. The user profile that a job is executing under at any given point in time (i.e., the current user) may or may not be the same as the job user. This may seem like a trivial and harmless mistake. But it often isn’t.

In previous