Strange length restriction with the DB2 LIKE opera

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

Could this possibly be related to the rowsize for the tablespace your table exists in?

I think if a a DB2 varchar extends beyond the rowsize for the table you're querying, it must be declared as a LONG VARCHAR, is stored external to the rest of the table data and referenced by a pointer, and LIKE queries are no longer possible. But I haven't used DB2 for some years. :)

Answer 2

The real Aha! is here.

Firstly, According to the rules for the concatenation operator result type, concatenating two VARCHARs with the combined length of 4000 bytes or less produces a VARCHAR of that combined length, e.g. concat(varchar(2000), varchar(2000)) = varchar(4000). Concatenating two VARCHARs with the combined length of 4001 bytes or more produces a LONG VARCHAR with the length of 32 700. Though the LONG VARCHAR type is deprecated, I guess the concatenation behaviour still uses the legacy logic.

$ db2 describe "values concat(cast('a' as varchar(2000)), cast('bc' as varchar(2000)))"

Column Information

Number of columns: 1

SQL type              Type length  Column name                     Name length
--------------------  -----------  ------------------------------  -----------
448   VARCHAR                4000  1                                         1


$ db2 describe "values concat(cast('a' as varchar(2001)), cast('bc' as varchar(2000)))"

Column Information

Number of columns: 1

SQL type              Type length  Column name                     Name length
--------------------  -----------  ------------------------------  -----------
456   LONG VARCHAR          32700  1     

Secondly, the LIKE predicate expects the pattern expression to be a VARCHAR with the maximum length of 32672 bytes.

Subsequently, when you unknowingly attempt to use a LONG VARCHAR as the pattern expression, you get an error. It's not so much about the length of the operand as it is about its data type. The following should work:

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

Answer 3

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'