title: Strange length restriction with the DB2 LIKE operator
body: <p>I found a funny issue with DB2 v9.7 and the SQL <code>LIKE</code> operator. Check this out:</p>
  
  <pre><code>-- 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
  </code></pre>
  
  <p>I've played around with the lengths and it seems that the problem appears as soon as lengths add up to be larger than <code>4000</code>. If I "truncate" the whole concatenated string back to length <code>4000</code>, the problem disappears:</p>
  
  <pre><code>select 1 
  from SYSIBM.DUAL
  where 'abc' like 
    cast(concat(cast('a' as varchar(2001)), cast('bc' as varchar(2000)))
    as varchar(4000))
  </code></pre>
  
  <p>Interestingly, it really seems to be related to the <code>CONCAT</code> function. The following works as well:</p>
  
  <pre><code>select 1 
  from SYSIBM.DUAL
  where 'abc' like cast('abc' as varchar(32672))
  </code></pre>
  
  <p>Has anyone experienced such an issue? Is it a bug in DB2? Or some undocumented restriction? N.B: I found a similar issue here:</p>
  
  <p><a href="https://www-304.ibm.com/support/docview.wss?uid=swg1PM18687" rel="nofollow">https://www-304.ibm.com/support/docview.wss?uid=swg1PM18687</a></p>
  
  <p>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?</p>