DB2 integer to Varchar autoconversion in Update

[manjuns]

Hi,

I am trying to update column(col1) which is of type varchar with below SQL, I am getting com.ibm.db2.jcc .b.nm: DB2 SQL Error: SQLCODE=-408, SQLSTATE=42821.

Same sql is working well with Oracle and Postgres, so does that mean DB2 auto conversion from integer to varchar is not supported??

update tabA set col1=0 where ...

[Shashank1984]

manjuns,
It's not allowed in DB2.
The data type of the value to be assigned to the column, parameter, SQL variable, or transition variable by the SQL statement is incompatible with the declared data type of the assignment target. Both must be:

* Numeric
* Character
* Graphic
* Dates or character
* Times or character
* Timestamps or character
* Datalinks
* The same distinct types
* Reference types where the target type of the value is a subtype of the target type of the column.
* The same user-defined structured types. Or, the static type of the value must be a subtype of the static type (declared type) of the target. If a host variable is involved, the associated built-in type of the host variable must be compatible with the parameter of the TO SQL transform function defined in the transform group for the statement.

You need to make sure the variable, expression, or literal value assigned has the proper data type for the assignment target. For a user-defined structured type, also consider the parameter of the TO SQL transform function defined in the transform group for the statement as an assignment target.

Cheers !
Shashank Kharche, IBM.
DB2 for Linux, Unix & Windows -Information Management Software