Compatibility of LIKE predicate on DB2 V9.7 with Oracle.

[Tonkuma]

One of my expectation for DB2 V9.7 was the subject issue.
I think that DB2 still does not support ISO SQL2003 Feature F281, LIKE
enhancements.
My test was:
------------------------------ Commands Entered
------------------------------
connect to SAMPLE ;
------------------------------------------------------------------------------

Database Connection Information

Database server = DB2/NT 9.7.0
SQL authorization ID = DB2ADMIN
Local database alias = SAMPLE

A JDBC connection to the target has succeeded.
------------------------------ Commands Entered
------------------------------
SELECT lastname
, match
FROM employee
, (VALUES 'SMITHERIN') p(match)
WHERE match LIKE '_%' || lastname || '%'
;
------------------------------------------------------------------------------
SQL0132N A LIKE predicate or POSSTR scalar function is not valid
because the
first operand is not a string expression or the second operand is not
a
string. A LOCATE or POSITION scalar function is not valid because the
first
operand is not a string or the second operand is not a string
expression.
SQLSTATE=42824

A workaround is like this:
------------------------------ Commands Entered
------------------------------
CREATE PROCEDURE like_enhanced
(IN match VARCHAR(254)
,IN pattern VARCHAR(254)
,OUT ret_val INTEGER )
CONTAINS SQL
DETERMINISTIC
NO EXTERNAL ACTION
SET ret_val
= CASE
WHEN match LIKE pattern
THEN 1
ELSE 0
END
;
------------------------------------------------------------------------------
DB20000I The SQL command completed successfully.

------------------------------ Commands Entered
------------------------------
CREATE FUNCTION like_enhanced
(match VARCHAR(254), pattern VARCHAR(254) )
RETURNS INTEGER
CONTAINS SQL
DETERMINISTIC
NO EXTERNAL ACTION
BEGIN ATOMIC
DECLARE ret_val INTEGER DEFAULT 0;
CALL general_like(match, pattern, ret_val);
RETURN ret_val;
END
@
------------------------------------------------------------------------------
DB20000I The SQL command completed successfully.

------------------------------ Commands Entered
------------------------------
SELECT lastname, firstnme
, match
FROM employee
, (VALUES 'DOLPHIN SMITH') p(match)
WHERE like_enhanced(match, '_%' || SUBSTR(firstnme,1,1) || '% %' ||
lastname) = 1
;
------------------------------------------------------------------------------

LASTNAME FIRSTNME MATCH
--------------- ------------ -------------
SMITH PHILIP DOLPHIN SMITH

1 record(s) selected.

[Tonkuma]

> ------------------------------ Commands Entered
> ------------------------------
> CREATE FUNCTION like_enhanced
> (match VARCHAR(254), pattern VARCHAR(254) )
> RETURNS INTEGER
> CONTAINS SQL
> DETERMINISTIC
> NO EXTERNAL ACTION
> BEGIN ATOMIC
> DECLARE ret_val INTEGER DEFAULT 0;
> CALL general_like(match, pattern, ret_val);
> RETURN ret_val;
> END
> @

> ---------------------------------------------------------------------------­---

> DB20000I  The SQL command completed successfully.

I made mistake(copied another test case). It should be:

CREATE FUNCTION like_enhanced
(match VARCHAR(254), pattern VARCHAR(254) )
RETURNS INTEGER
CONTAINS SQL
DETERMINISTIC
NO EXTERNAL ACTION
BEGIN ATOMIC
DECLARE ret_val INTEGER DEFAULT 0;

CALL like_enhanced(match, pattern, ret_val);
RETURN ret_val;
END
@

[Serge Rielau]

Tonkuma,

Correct. Simple predicates like yours can typically be transformed into
an INSTR(). So far we have not had this bubble up to an issue.
If you have your favorite list of Oracle functions please send them with
prioritization to me.

Cheers
Serge

--
Serge Rielau
SQL Architect DB2 for LUW
IBM Toronto Lab

[Tonkuma]

The INSTR function(a synonym for the LOCATE_IN_STRING function) has
not enough rich functionality as LIKE predicate.

If you know a pattern before execution of a query, you might be able
to construct equivalent expression for the pattern.
For example(like in my original post):

SELECT lastname, firstnme
, match
FROM employee
, (VALUES 'DOLPHIN SMITH') p(match)

WHERE match LIKE '_%' || SUBSTR(firstnme,1,1) || '% %' || lastname
;
would be replaced by:
WHERE INSTR(SUBSTR(firstnme,1,1), match, 2) < INSTR(' ', match)
AND INSTR(lastname, match) > INSTR(' ', match)

But, it would be very complex logic to construct such equivalent
expressions dynamically by a program code(SQL/PL or another host
language) for all possible patterns.
For example, I have asked sometimes to keep patterns in a table and to
edit the patterns by users.

So, I thought that this would be not a trivial issue.