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

问题描述

我发现一个有趣的问题与DB2 v9.7和SQL LIKE 运算符。请查看：

   - 这个工作并返回一条记录
从SYSIBM.DUAL中选择1 
 
其中'abc'like concat（'a'，'bc'）
 
  - 这不起作用
 select 1 
 from SYSIBM.DUAL 
 where'abc'like concat（cast（'a'as varchar（2001）），cast（'bc'as varchar（2000）））
 
  - ）：
  - 没有授权的命名为LIKE的类型FUNCTION的例程具有兼容性
  - 找到参数.. SQLCODE = -440，SQLSTATE = 42884，DRIVER = 4.7.85 
 

我玩过的长度，似乎问题出现一旦长度加起来更大 4000 。如果我将整个连接字符串截断回长度 4000 ，则问题消失：

  select 
从SYSIBM.DUAL 
其中'abc'像
 cast（concat（cast（'a'as varchar（2001）），cast（'bc'as varchar（2000）））
 as varchar（4000））
  

有趣的是，与 CONCAT 函数相关。以下同样适用：

 从SYSIBM.DUAL中选择1 
 
其中abc （'abc'as varchar（32672））
  

有没有人遇到这样的问题？这是DB2中的一个错误吗？还是一些没有文件的限制？注意：我在这里发现了类似的问题：

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

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

解决方案

编辑：

在信息中心搜索 VARCHAR s，我在字符数据类型页：

函数在SYSFUN模式中以VARCHAR作为参数
不会接受大于4 000字节长的VARCHAR作为参数。
然而，许多这些函数也有一个替代签名
接受一个CLOB（1M）。对于这些函数，用户可以显式地
将大于4 000个VARCHAR字符串转换成CLOB，然后将结果重新转换成所需长度的VARCHAR。

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

我做了一些额外的测试，看起来像上面提到的解决方法适用于Linux / Unix / Windows上的DB2，但不适用于主机上的DB2。

---

关闭信息中心的SQL和XML限制页面，如果你看看表7（第三行），它说，行的最大长度包括所有开销的表空间与4k页面大小是4005字节。

我的猜测是 SYSIBM.DUAL 是在4k页大小的表空间，这导致您的错误。您可以检查 SYSCAT.TABLESPACES ，可能会确认或拒绝此怀疑。

使用以下查询获取信息：

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

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?

解决方案

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'

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