DB2 LIKE 运算符的奇怪长度限制 [英] Strange length restriction with the DB2 LIKE operator

问题描述

我发现 DB2 v9.7 和 SQL LIKE 运算符有一个有趣的问题.看看这个:

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

我玩过长度，似乎只要长度加起来大于 4000，问题就会出现.如果我将整个连接的字符串截断"回长度 4000，问题就会消失:

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))

有趣的是，它似乎真的与 CONCAT 函数有关.以下方法也有效:

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))

有没有人遇到过这样的问题?它是 DB2 中的错误吗?还是一些无证限制?N.B:我在这里发现了一个类似的问题:

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

鉴于另一个 IBM 产品在 2010 年为这个问题创建了一个解决方法，我想这不是一个真正的错误，否则它会同时修复?

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?

推荐答案

啊哈！

在信息中心搜索有关 VARCHAR 的另一点知识时，我在 字符数据类型页面:

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:

SYSFUN 模式中的函数以 VARCHAR 作为参数不接受长度超过 4000 字节的 VARCHAR 作为参数.但是，其中许多函数也有替代签名接受 CLOB(1M).对于这些功能，用户可以明确将大于 4 000 个 VARCHAR 字符串转换为 CLOB，然后重新转换结果返回到所需长度的 VARCHAR.

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.

所以，它看起来像是 DB2 的一个已知特性".

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

我做了一些额外的测试，看起来上面提到的解决方法"适用于 Linux/Unix/Windows 上的 DB2，但不适用于大型机上的 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.

离开 SQL 和 XML 限制 页面来自信息中心，如果您查看表 7(第三行)，它表示行的最大长度包括表空间的所有开销4k 页面大小为 4005 字节.

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.

我的猜测是 SYSIBM.DUAL 位于 4k 页大小的表空间中，这导致了您的错误.您可以查看 SYSCAT.TABLESPACES，这可能会证实或否认这种怀疑.

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'

这篇关于DB2 LIKE 运算符的奇怪长度限制的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！