Hi DB2 newsgroup,
I have encountered a difference between our development DB2 on Windows
and the test environment on AIX.
If using the LOCATE scalar function with a SMALLINT as LENGTH parameter
the same statement produces different results on Windows (correct) than
on AIX (wrong). The same statement using an INTEGER instead of SMALLINT
produces the correct result in both environments.
Is there a reason why using a SMALLINT instead of an INTEGER leads to
another result in both environments? I tried to specify the string unit
(CODEUNITS16, CODEUNITS32, or OCTETS) but the results still differ.
As you can see below the statement 4 and 5 shows that the error handling
differs and that the SMALLINT handling or casting may be problem here. I
see no reason why the SMALLINT casting should fail.
Both databases are on current FixPack level (V9 FP5), using the same
codepage (1208) and same codeset (but displays in differing cases: Win:
utf-8, AIX: UTF-8).
Any hints or tips?
Thanks in advance,
Michael
Test statements:
values locate('A', 'A', CAST(1 AS SMALLINT)); --different
values locate('A', 'A', CAST(1 AS INTEGER)); --same
values locate('A', 'A', CAST(CAST(1 AS SMALLINT) AS INTEGER)); --same
values locate('A', 'A', CAST(0 AS SMALLINT)); --nearly the same,
differing error message
values locate('', '', CAST(0 AS SMALLINT)); --different
Result on Windows:
db2 =values locate('A', 'A', CAST(1 AS SMALLINT));
==1
db2 =values locate('A', 'A', CAST(CAST(1 AS SMALLINT) AS INTEGER));
==1
db2 =values locate('A', 'A', CAST(1 AS INTEGER));
==1
db2 =values locate('A', 'A', CAST(0 AS SMALLINT));
==SQL0171N The data type, length or value of argument "start" of
routine "SYSIBM.LOCATE" is incorrect. SQLSTATE=42815
db2 =values locate('', '', CAST(0 AS SMALLINT));
==SQL0171N The data type, length or value of argument "start" of
routine "SYSIBM.LOCATE" is incorrect. SQLSTATE=42815
Result on AIX:
db2 =values locate('A', 'A', CAST(1 AS SMALLINT));
==0
db2 =values locate('A', 'A', CAST(CAST(1 AS SMALLINT) AS INTEGER));
==1
db2 =values locate('A', 'A', CAST(1 AS INTEGER));
==1
db2 =values locate('A', 'A', CAST(0 AS SMALLINT));
==SQL0171N The data type, length or value of argument "start" of
routine "" is incorrect. SQLSTATE=42815
db2 =values locate('', '', CAST(0 AS SMALLINT));
==1