Db2使用了upper函数导致报错：SQL0433N Value "xxx" is too long. SQLSTATE=22001

环境：

DB2 10.5

 

问题现象及重现步骤：

Db2 的SQL语句报错：SQL0433N  Value "xxxxxxxxx" is too long.  SQLSTATE=22001，经过排查，发现是upper函数导致的，如果upper函数中的表达式超过32672个字符，就会出现这个问题，以下是重现步骤：

$ db2 "create table t1(stmt clob(2M))"
$ for i in `seq 1 32673`; do echo -e "A\c" >> t1.del; done
$ db2 "import from t1.del of del insert into t1"
$ db2 "select count(*) from t1 where upper(stmt) like 'abc%'"

1          
-----------
SQL0433N  Value "AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA" 
is too long.  SQLSTATE=22001

db2inst1@node01:~$ db2 "create table t1(stmt clob(2M))"
DB20000I  The SQL command completed successfully.
db2inst1@node01:~> rm t1.del
db2inst1@node01:~> for i in `seq 1 32673`; do echo -e "A\c" >> t1.del; done
db2inst1@node01:~> db2 "import from t1.del of del insert into t1"
SQL3109N  The utility is beginning to load data from file "t1.del".

SQL3115W  The field value beginning "AAAAAAAAAAAAAAAAAAAAAAAAAAAAAA" in row 
"1" and column "1" is longer than the longest allowable table column.  The 
value was truncated.

SQL3125W  The character data in row "1" and column "1" was truncated because 
the data is longer than the target database column.

SQL3110N  The utility has completed processing.  "1" rows were read from the 
input file.

SQL3221W  ...Begin COMMIT WORK. Input Record Count = "1".

SQL3222W  ...COMMIT of any database changes was successful.

SQL3149N  "1" rows were processed from the input file.  "1" rows were 
successfully inserted into the table.  "0" rows were rejected.


Number of rows read         = 1
Number of rows skipped      = 0
Number of rows inserted     = 1
Number of rows updated      = 0
Number of rows rejected     = 0
Number of rows committed    = 1

db2inst1@node01:~> db2 "select count(*) from t1 where upper(stmt) like 'abc%'"

1          
-----------
SQL0433N  Value "AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA" 
is too long.  SQLSTATE=22001

db2inst1@node01:~> db2 "select length(stmt) from t1"

1          
-----------
      32673

  1 record(s) selected.
  

 

如果有人问你是怎么知道数字是32673的？答：二分法查找的，先从2000000开始，往下减。