title: DB2 string concatenation of longer varchars with space literal fails
body: <p>I've stumbled across a weird problem with varchar concatenation and the trim function. I'm using DB2 11.1.1.2.</p>
  
  <p>My table consists of short and long varchars:
  </p>
  
  <pre><code>CREATE TABLE test (
  id integer,
  name_short varchar(200),
  name2_short varchar(200),
  name_long varchar(2000),
  name2_long varchar(2000)
  )
  </code></pre>
  
  <p><strong>What works:</strong></p>
  
  <p>If I concatenate two (shorter) <code>varchars</code> and pass them to the <code>TRIM</code> functions, it works just fine:</p>
  
  <pre><code>SELECT trim( name_short ||  name2_short ) from test;
  </code></pre>
  
  <p>Adding a space between is also no problem:</p>
  
  <pre><code>SELECT trim( name_short || ' ' ||  name2_short ) from test;
  </code></pre>
  
  <p>Concatenating the two longer varchars works, too:</p>
  
  <pre><code>SELECT trim( name_long || name2_long ) from test;
  </code></pre>
  
  <p><strong>What doesn't work</strong>: Adding the space now fails!</p>
  
  <pre><code>SELECT trim( name_long || ' ' || name2_long ) from test;
  </code></pre>
  
  <p>Result: </p>
  
  <blockquote>
    <p>The statement was not processed because the data type, length or value of the argument for the parameter in position "string-expr" of routine "SYSIBM.TRIM" is incorrect. Parameter name: "".. SQLCODE=-171, SQLSTATE=42815, DRIVER=4.16.53</p>
  </blockquote>
  
  <p>Why does this fail - and how could it be fixed?</p>
  
  <ul>
  <li>Trim expects a varchar as the expression  to trim</li>
  <li>According to the <a href="https://www.ibm.com/support/knowledgecenter/SSEPGG_11.1.0/com.ibm.db2.luw.sql.ref.doc/doc/r0000736.html" rel="nofollow noreferrer">db2 docs for the concatenation operator</a>, the result of concatenating two varchars should be:
  
  <ul>
  <li>For z/OS: a varchar with length equal to the sum of the operands length (or 32764 if smaller).</li>
  <li>For LUW edition (Linux Unix Windows): a Longvarchar <strong>Edit: Linux version added</strong></li>
  </ul></li>
  </ul>
  
  <p><strong>Alternatives I've tried</strong></p>
  
  <ul>
  <li>Casting the string literal: <code>SELECT trim( name_long || cast( ' ' as varchar) || name2_long ) from test;</code> (same error)</li>
  <li>Casting the whole expression to varchar before passing it to trim</li>
  <li>Note: Changing one of the longer columns to a length of 2001 will fail the third example!</li>
  </ul>