DB2 string concatenation of longer varchars with space literal fails

DB2 string concatenation of longer varchars with space literal fails

The -171 sqlcode is happening because Db2-LUW may be converting name_long || || name2_long into a datatype LONG VARCHAR, which TRIM dislikes.
You may get a different outcome if you explicitly cast like this:

SELECT trim( varchar(name_long || || name2_long) ) from test;

You can also check what Db2 is doing via:
describe select name_long || ||name2_long from test;

I tried on Db2-LUW v11.1.3.3 , and casting to VARCHAR yields a correct result.

So you might want to apply the latest fixpack and retry.

As mao indicates, a CONCAT of columns whose Combined Length Attributes is greater than 4000 returns a LONG VARCHAR. See table 1 on this page https://www.ibm.com/support/knowledgecenter/en/SSEPGG_11.1.0/com.ibm.db2.luw.sql.ref.doc/doc/r0000736.html
so you will need to explicitly cast the type to VARCHAR.

Interestingly, on Db2 Warehouse in NPS compat mode does not need the cast.

set sql_compat=NPS;
SELECT trim( name_long ||   || name2_long ) from test;

DB2 string concatenation of longer varchars with space literal fails

CHR(32) works fine, like this example.

 select trim(authid)||chr(32)||trim(authidtype)from sysibmadm.authorizationids

Output.

PUBLIC G
SYSROLE_AUTH_DBADM R
SYSROLE_AUTH_EXPLAIN R

Leave a Reply

Your email address will not be published. Required fields are marked *