I have created a trigger, inside the trigger I am appending lot of varchar and creating a new varchar. if I append too many varchars then I am getting below Sql Exception. (if I append 4 or 5 varchar then I am not getting this error)
I have found out that varchar maximum size is based on page size of the table space. I have checked my page size, its 8K bytes.
But The content I am appending is not 8K bytes. Please help me to resolve this error.
An error occurred in a triggered SQL statement in trigger "ORDDBA.ORD_IR_IN". Information returned for the error includes SQLCODE "-433", SQLSTATE "22001" and message tokens "ACTION|AREA_UNIT|BAL_CHG_FLAG|CANCEL_DATE|".. SQLCODE=-723, SQLSTATE=09000, DRIVER=3.66.46
declare columnNames varchar(5000);
SET columnNames='';
SET columnNames = 'value1' ;
SET columnNames = CONCAT(columnNames, '|') ;
SET columnNames = CONCAT(columnNames, 'value2') ;
SET columnNames = CONCAT(columnNames, '|') ;
SET columnNames = CONCAT(columnNames, 'value3') ;
SET columnNames = CONCAT(columnNames, '|') ;
SET columnNames = CONCAT(columnNames, 'value14') ;
SET columnNames = CONCAT(columnNames, '|') ;
SET columnNames = CONCAT(columnNames, 'value5') ;
SET columnNames = CONCAT(columnNames, '|') ;
SET columnNames = CONCAT(columnNames, 'value6') ;
SET columnNames = CONCAT(columnNames, '|') ;
SET columnNames = CONCAT(columnNames, 'value6') ;
SET columnNames = CONCAT(columnNames, '|') ;
SET columnNames = CONCAT(columnNames, 'value8') ;
SET columnNames = CONCAT(columnNames, '|') ;
SET columnNames = CONCAT(columnNames, 'value9') ;
SET columnNames = CONCAT(columnNames, '|') ;
SET columnNames = CONCAT(columnNames, 'value10') ;
SET columnNames = CONCAT(columnNames, '|') ;
SET columnNames = CONCAT(columnNames, 'value11') ;
SET columnNames = CONCAT(columnNames, '|') ;
SET columnNames = CONCAT(columnNames, 'value12') ;
SET columnNames = CONCAT(columnNames, '|') ;
SET columnNames = CONCAT(columnNames, 'value13') ;
SET columnNames = CONCAT(columnNames, '|') ;
SET columnNames = CONCAT(columnNames, 'value14') ;
SET columnNames = CONCAT(columnNames, '|') ;
SET columnNames = CONCAT(columnNames, 'value15') ;
SET columnNames = CONCAT(columnNames, '|') ;
SET columnNames = CONCAT(columnNames, 'value16') ;
Answer
From your comment:
if I count the number of character in the varchar its less than 5000, seems DB2 counting in different way so its failing. Then I changed the datatype to CLOB, then its working fine
This is because the length of a varchar is given in bytes, while the length of a clob is given in characters (documentation).
Based on character encoding, a single character may be longer than one byte. Therefore it is not always possible to store 5000 characters in a varchar(5000).