Strange length restriction with the DB2 LIKE operator

Question

I found a funny issue with DB2 v9.7 and the SQL LIKE operator. Check this out:

-- this works and returns one record
select 1 
from SYSIBM.DUAL
where 'abc' like concat('a', 'bc') 

-- this doesn't work
select 1 
from SYSIBM.DUAL
where 'abc' like concat(cast('a' as varchar(2001)), cast('bc' as varchar(2000)))

-- It causes this error (from JDBC):
-- No authorized routine named "LIKE" of type "FUNCTION" having compatible 
-- arguments was found.. SQLCODE=-440, SQLSTATE=42884, DRIVER=4.7.85

I've played around with the lengths and it seems that the problem appears as soon as lengths add up to be larger than 4000. If I "truncate" the whole concatenated string back to length 4000, the problem disappears:

select 1 
from SYSIBM.DUAL
where 'abc' like 
  cast(concat(cast('a' as varchar(2001)), cast('bc' as varchar(2000)))
  as varchar(4000))

Interestingly, it really seems to be related to the CONCAT function. The following works as well:

select 1 
from SYSIBM.DUAL
where 'abc' like cast('abc' as varchar(32672))

Has anyone experienced such an issue? Is it a bug in DB2? Or some undocumented restriction? N.B: I found a similar issue here:

https://www-304.ibm.com/support/docview.wss?uid=swg1PM18687

Given that another IBM product creates a workaround for this issue in 2010, I guess it's not really a bug, otherwise it would have been fixed in the mean time?

Answer 1

Edit: Aha!

While searching the Information Center for another bit of knowledge on VARCHARs, I discovered this nifty tidbit of information on the character data type page:

The functions in the SYSFUN schema taking a VARCHAR as an argument will not accept VARCHARs greater than 4 000 bytes long as an argument. However, many of these functions also have an alternative signature accepting a CLOB(1M). For these functions, the user may explicitly cast the greater than 4 000 VARCHAR strings into CLOBs and then recast the result back into VARCHARs of desired length.

So, it looks like it's a known "feature" of DB2.

I did some additional testing, and it looks like the "workaround" mentioned above works for DB2 on Linux/Unix/Windows, but does not work for DB2 on the Mainframe.

---

Going off of the SQL and XML Limits page from the Information center, if you look at Table 7 (third row), it says that the maximum length of a row including all overhead for a table space with a 4k page size is 4005 bytes.

My guess is that SYSIBM.DUAL is in a 4k pagesize table space, which is causing your error. You can check SYSCAT.TABLESPACES, which might confirm or deny this suspicion.

You can get the information with a query like so:

SELECT ts.PAGESIZE
FROM SYSCAT.TABLESPACES ts
JOIN SYSCAT.TABLES tb
  ON tb.TBSPACEID = ts.TBSPACEID
WHERE tb.TABSCHEMA = 'SYSIBM'
  AND tb.TABNAME   = 'DUAL'