DB2 HEX appears to do a similar thing as using the optional style parameter of CONVERT when converting from binary to a string type:

SELECT

CONVERT(char(4), 0xDEADBEEF), -- 'Þ­¾ï'

CONVERT(char(10), 0xDEADBEEF, 1), -- '0xDEADBEEF'

CONVERT(char(8), 0xDEADBEEF, 2) -- 'DEADBEEF'

Q1: Does the Column1 value really have length 42? (e.g. trailing non-spaces).

Q2: Hope this is now irrelevant. Possibly the DB2 binary storage format for the column.

Q3: Possibly. Would need to see the query plan and know the data types involved.

Q4: No answer needed now, I hope.