Unanswered: Stored procedure does not set isolation on session
I am trying to set the default isolation and lock time out for a datasource.
I have tried datasource properties, but the driver we are using in production does not support these.
We also do not have a WebLogic-specific datasource property for these.
This can be achieved however by setting the "Init SQL" property of the datasource to "SQL set isolation to ?".
My problem is that i need both the isolation and the lock timeout set.
Enter the stored procedure ...
If i am able to write a stored procedure that does both of these things then i can set the "Init SQL" to "SQL execute procedure iso_lock()"
This however does not work.
If i open a connection / session to DB2 and manually execute the isolation or lock timeout, it takes effect.
if however i open a connection and manually execute the stored procedure, it does not take effect.
I have also tried using the EXECUTE IMMEDIATE method inside the stored procedure and this also does not work.
The stored procedure code is shown below.
CREATE PROCEDURE "schema"."iso_lock"
set isolation to UR;
set lock timeout to 10;