Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2004
    Location
    earth
    Posts
    96

    Unanswered: Set session_user

    Hi,

    I am trying to switch a user from abc to def within begin / end in atomic.

    BEGIN ATOMIC
    DECLARE ADD CHAR(25) DEFAULT 'ADD400';

    SET SESSION_USER=abc;

    SET ADD = (SELECT address FROM "db2inst1"."customer"
    WHERE customer_id=112);

    SET SESSION_USER=def;

    UPDATE "db2inst1"."customer"
    SET address = ADD
    WHERE customer_id=114;

    SELECT * FROM "db2inst1"."customer";

    END@

    When I run the above,it will give me an error message as below here :
    "SESSION_USER" is not valid in the context where it is used.. SQLCODE=-206, SQLSTATE=42703, DRIVER=4.16.53


    But, it is successfully execute without Begin/End and Declare variable as below here :-

    set session_user = abc;
    select session_user from sysibm.sysdummy1;
    select * from db2inst1.customer;

    set session_user = def;
    select session_user from sysibm.sysdummy1;
    select * from db2inst1.customer;

    I have tried using execute immediate set session_user=abc; but it does not allowed.

    Please help. Thank you

  2. #2
    Join Date
    Jul 2013
    Location
    Moscow, Russia
    Posts
    666
    Provided Answers: 55
    Hi,

    SET SESSION AUTHORIZATION
    can only be issued as the first statement (other than a SET special register statement) in a new unit of work without any open WITH HOLD cursors (SQLSTATE 25001).
    You can use it only in a compiled sql like this:
    Code:
    BEGIN
    ...
    commit;
    SET SESSION_USER=abc;
    ...
    commit;
    SET SESSION_USER=def;
    ...
    END@
    Regards,
    Mark.

  3. #3
    Join Date
    Feb 2004
    Location
    earth
    Posts
    96
    Hello Mark,
    Thank you very much! It works, I can switch between the users and make use of the ADD variable for different users. Many thanks!

    BEGIN
    DECLARE ADD CHAR(25) DEFAULT 'ADD400';

    SET SESSION_USER=abc;

    SET ADD = (SELECT address FROM "db2inst1"."customer"
    WHERE customer_id=112);

    commit;
    SET SESSION_USER=def;

    UPDATE "db2inst1"."customer"
    SET address = ADD
    WHERE customer_id=114;

    SELECT * FROM "db2inst1"."customer";

    END@

Posting Permissions

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