Results 1 to 3 of 3
  1. #1
    Join Date
    May 2008
    Posts
    7

    Unanswered: db2 procedure problem

    Hello Gurus,

    I have a db2 procedure as following, when I run :db2 -td@ -f , there is a error message "SQL0551N "FZHANG05" does not have the privilege to perform operation "INSERT" on object "FZHANG05.DATETAB". LINE NUMBER=21. SQLSTATE=42501". but when I run the inside statement one-by-one, it is ok. what's wrong with this script ?



    CREATE PROCEDURE registersample ( OUT p_start TIMESTAMP
    , OUT p_end TIMESTAMP
    , OUT p_c1 TIMESTAMP
    , OUT p_c2 TIME
    , OUT p_user CHAR(20))
    LANGUAGE SQL
    SPECIFIC registersample -- applies to LUW and iSeries
    -- WLM ENVIRONMENT <env> -- applies to zSeries
    BEGIN
    CREATE TABLE datetab (c1 TIMESTAMP,c2 TIME,c3 DATE);


    VALUES CURRENT TIMESTAMP INTO p_start; -- (1)
    INSERT INTO datetab VALUES( CURRENT TIMESTAMP
    , CURRENT TIME
    , CURRENT DATE + 3 DAYS); -- (2)
    SELECT c1,c2 INTO p_c1,p_c2 FROM datetab;
    VALUES CURRENT TIMESTAMP INTO p_end;
    SET p_user = USER; -- (3)
    DROP TABLE datetab;
    END

  2. #2
    Join Date
    Dec 2007
    Posts
    23
    probably it needs to excute privilages for the id

  3. #3
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Stored procedures don't take group privileges into account because DB2 cannot control when a user is removed from a group and, thus, the respective access plan would have to be invalidated. So my guess is that the statements work fine when running independently because you inherit he necessary privileges from some group.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

Posting Permissions

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