Settings for Implicit Datatype Conversion in DB2 9.7 FP5

[Satish Mullapudi]

Hi All,
The version of DB2 am using is 9.7.0.5.
We have two environments where we have deployed one stored procedure where it is working fine in one (Test env), but throwing the below error on the other (Stage env)
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0420N Invalid character found in a character string argument of the
function "INTEGER". SQLSTATE=22018

Figured out the reason for this:
CREATE OR REPLACE PROCEDURE classification_term_3308_dedupe
(
OUT OUT_SQLCODE INTEGER,
OUT OUT_SQLSTATE INTEGER,
OUT out_message_text varchar(1000)
)

where am assigning a CHAR value like '00000' to the OUT_SQLSTATE.

But now, this SP executed & compiled fine due to implicit type casting in DB2 9.7 . Now both the envs are having same DB2 version & FP , but not able to get on why it is throwing an error in the other env.
Do I need to update any settings ( registry, db cfg , dbm cfg etc ) for it to get affected !

Thanks in Advance,
Satish

[Jeremy Rickard]

SQLSTATEs can contain letters not just digits. Why not define it as CHAR(5) as per normal, then see what code is returned?

Also, as you can capture the returned SQLSTATE without an output parameter, unsure why you need this approach.

Regards,


Jeremy Rickard

[Mark A]

On Fri, 05 Apr 2013 16:22:50 -0700, Jeremy Rickard wrote:

> SQLSTATEs can contain letters not just digits. Why not define it as
> CHAR(5) as per normal, then see what code is returned?
>
> Also, as you can capture the returned SQLSTATE without an output
> parameter, unsure why you need this approach.

Absolutely correct that SQLSTATE is CHAR(5).

But my understanding is that it is best to return the SQLSTATE, etc to calling program, otherwise
sometimes you can't see what it is. What approach are you thinking of where that is not necessary?

[Jeremy Rickard]

The calling program should have a mechanism to detect any SQL error from a SQL statement, including CALL. For example SQLException in Java or a condition handler in an SQL procedure. There should be no need to devise a separate mechanism for capturing errors from a CALL versus other SQL, say an update or select.


Jeremy Rickard

[Mark A]

It appears to me that sometimes a generic SQL error is returned to the calling program, with only scant
info about the actual SQL error code, return code, and tokens captured within a SP for a specific error.