When is SqlState reset?

Hi all

I ran into some peculiar piece of code that didn't do what I expected and decided to run a small test.
The goal is to see if SqlState is being reset in an if-statement. My human brain says it shouldn't be reset in the example code below

begin
declare sqlState char(5) default '';
declare myLocalVar char(1) default 'X';
select IBMREQD into myLocalVar from sysibm.sysdummy1 where 0 = 1;
if SqlState = '02000' then
if SqlState = '00000' then
call systools.lprintf ('2:' || SqlState || myLocalVar);
end if;
end if;
end;

SqlState and myLocalVar are declared
I select IBMREQD from sysdummy1 with a where condition that assures it's not found (the value in sysdummy1 is 'Y'). My SqlState is therefore 02000, I checked that. So, my subsequent if-statement is then true.
In my mind the inner if-statement can't be true if the outer is, so it should never produce an entry in the joblog.

However, it does give "2:00000X" in the joblog.
I'm aware that the call to lprintf will alter the SqlState value, but I only expect a new value for SqlState after the call.

It seems that the if statement itself modifies SqlState or am I overlooking something? It just seems weird...


Kind regards,



Martijn van Breden

lead software architect