CODEWITHSUNDEEP
db2db2-luw
coder12349
coder12349

Reputation: 491

Data too long Error for a column of type Varchar(600) in db2(Windows and 10.0.22 version)

I have set a column size of type Varchar of size 600. But i get the below error while updating the table.

[Code: -404, SQL State: 22001]  THE SQL STATEMENT SPECIFIES A STRING THAT IS TOO LONG. SQLCODE=-404, SQLSTATE=22001, DRIVER=4.25.1301

I am using a update query as shown below.

update test set description= <here i am passing the description of charachters length 600> where id=1;

The above specified error doesn't appear if i pass the description of charachter count 597 charachters. But the error appears if the charachters count is greater than 597 even tough i have set the column size as 600. Is there any specific explanation for this?

Upvotes: 0

Views: 1933

Answers (1)

kkuduk
kkuduk

Reputation: 601

A quick test:

db2 "create table varchar_test(c1 int, c2 varchar(10))"
db2 "insert into varchar_test values (1, '0123456789')"
DB20000I  The SQL command completed successfully.

obviously I can insert 10-byte string here. But if I would replace the last character with multi-byte UTF character "ą" (2 bytes in utf-8), it fails:

db2 "insert into varchar_test values (2, '012345678ą')"
DB21034E  The command was processed as an SQL statement because it was not a 
valid Command Line Processor command.  During SQL processing it returned:
SQL0433N  Value "012345678ą" is too long.  SQLSTATE=22001

with one fewer it is OK:

db2 "insert into varchar_test values (2, '01234567ą')"
DB20000I  The SQL command completed successfully.

so I can test now:

db2 "select c1, length(c2) c2_len_bytes,CHARACTER_LENGTH(c2) c2_char_len, hex(c2) c2_hex from varchar_test" 

C1          C2_LEN_BYTES C2_CHAR_LEN C2_HEX              
----------- ------------ ----------- --------------------
          1           10          10 30313233343536373839
          2           10           9 3031323334353637C485

-> it confirms that the second row is 10 bytes in size but has 9 characters. I suggest to repeat the same exercise for the longest string you can fit and see whether indeed you have only single-byte characters in your VARCHAR. For a more detailed examination of utf-8 characters in Db2 database you can review my answer here: When I importing gujarati data using csv file that time data show like? (nothing Db2 specific there, just a regular "utf-8 troubleshooting")

Upvotes: 2

Related Questions