Results 1 to 2 of 2
  1. #1
    Join Date
    May 2015

    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;

  2. #2
    Join Date
    Jul 2013
    Moscow, Russia
    Provided Answers: 55

    Try this:
    update db cfg using connect_proc "schema"."iso_lock"
    This will be called for every new connection automatically and you don't have to call it explicitly.

    BTW, what's the strange jdbc driver which doesn't support defaultIsolationLevel & currentLockTimeout properties?

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts