What can be the cause that when the SP is runned with different Users, by one user the correct and actuall definition was runned
but by the second user the old version of the SP was runned?
There's another (old) copy of the SP in a different database and the 2nd called this.
Check dbc.TablesV for duplicate SP names.
Actually the issue at hand is that when the SP was call (it does not matter with which user) it sometimes uses old codes.
i am concerned about the fact that i am not sure of which code will be runned whenever the SP is called.
The SP has seen been dropped and recreated. However i need an assurance that whenever a SP or Function is created, it is the same version that runned across all nodes.
Secondly i would like to know what happened in those cases where the old SP called were run instead of the current code.
In OLD code it is like
SELECT coalesce(max(run_id), -1) + 1 FROM ADMINGHMI.t_loadlog WHERE loadtype = 'BED' INTO :run_id_new;
and in new code it is comming like :
SELECT coalesce(max(run_id), 0) + 1 FROM ADMINGHMI.t_loadlog WHERE loadtype = 'BED' INTO :run_id_new;
sometimes the old statement with -1 is comming whenrunning the sp and sometime the new statement is comming 0.
Could you please explain me why this is happening.
Did the dbc.tablesV analysis show you where the duplicate procedure was? and, of course, did you drop all other versions?
Other than ensuring that there is only one version in common databases like syslib, td_sysfnlib the only way to ensure what version is to hardcode the database prefix.
But, if you have cleaned up dupes should not be needed.