Results 1 to 4 of 4
  1. #1
    Join Date
    Aug 2002
    Posts
    14

    Unanswered: Sequence Privilege

    Hi everybody!!!

    I created a sequence using the user db2inst1, and granted usage for this sequence to public:
    DB2 => CONNECT TO SAMPLE USER db2inst1 USING ibmdb2;
    db2 => CREATE SEQUENCE SEQ_TEST AS BIGINT
    START WITH 53546
    INCREMENT BY 1
    MINVALUE 1
    MAXVALUE 9999999999
    NOCYCLE
    NOCACHE;
    db2 => GRANT USAGE ON SEQUENCE SEQ_TEST TO PUBLIC;

    But when I try to get the value of this sequence using another user, I get the following error:
    db2 => CONNECT TO SAMPLE USER monica USING password;
    db2 => SELECT NEXTVAL FOR SEQ_TEST FROM SYSIBM.SYSDUMMY1;
    SQL0204N "MONICA.SEQ_TEST" is an undefined name. SQLSTATE=42704

    Do I need to set the sequence owner schema? (SET SCHEMA...)
    Granting to public, is not enough?

    The db2level is:
    DB21085I Instance "db2inst1" uses DB2 code release "SQL07020" with level
    identifier "03010105" and informational tokens "DB2 v7.1.0.41", "s010426" and
    "U475375".


    Thanks in advance, and my apologies for my English.

  2. #2
    Join Date
    Apr 2002
    Posts
    188

    Re: Sequence Privilege

    Originally posted by Monica
    Hi everybody!!!

    I created a sequence using the user db2inst1, and granted usage for this sequence to public:
    DB2 => CONNECT TO SAMPLE USER db2inst1 USING ibmdb2;
    db2 => CREATE SEQUENCE SEQ_TEST AS BIGINT
    START WITH 53546
    INCREMENT BY 1
    MINVALUE 1
    MAXVALUE 9999999999
    NOCYCLE
    NOCACHE;
    db2 => GRANT USAGE ON SEQUENCE SEQ_TEST TO PUBLIC;

    But when I try to get the value of this sequence using another user, I get the following error:
    db2 => CONNECT TO SAMPLE USER monica USING password;
    db2 => SELECT NEXTVAL FOR SEQ_TEST FROM SYSIBM.SYSDUMMY1;
    SQL0204N "MONICA.SEQ_TEST" is an undefined name. SQLSTATE=42704

    Do I need to set the sequence owner schema? (SET SCHEMA...)
    Granting to public, is not enough?

    The db2level is:
    DB21085I Instance "db2inst1" uses DB2 code release "SQL07020" with level
    identifier "03010105" and informational tokens "DB2 v7.1.0.41", "s010426" and
    "U475375".


    Thanks in advance, and my apologies for my English.
    Hello monica

    Yes I think you have two choices AFIK

    1) set the schema to the appropiate schema owner of the object

    2) create an alias for the sequence object

    my two cents

    marcos oliva

  3. #3
    Join Date
    Aug 2002
    Posts
    14

    Re: Sequence Privilege

    Hi Marcos,

    Thanks very much for your attention,

    Is it possible to create an alias for Sequences? Maybe I need to apply some FixPack...because I didn't get it. Is your OS AIX 4.3? What is the DB2 version?
    In my case, I need to use the schema of the current user then, an alias would solve my problem.

    Thanks a lot,

    Monica.

  4. #4
    Join Date
    Apr 2002
    Posts
    188

    Re: Sequence Privilege

    Originally posted by Monica
    Hi Marcos,

    Thanks very much for your attention,

    Is it possible to create an alias for Sequences? Maybe I need to apply some FixPack...because I didn't get it. Is your OS AIX 4.3? What is the DB2 version?
    In my case, I need to use the schema of the current user then, an alias would solve my problem.

    Thanks a lot,

    Monica.
    Hello Monica,

    I have experience this behaiviour with DB2 7.2 FixPack 4 on Wind2000AS and AIX4.5.3 - 5.0
    What I did to resolve it is , since I am using a javaSwing app, is to create aliases of the objects I need to get access to, I tried to set the schema on the JDBC connection but for some reason I could not, then I went ahead and created a script with aliases on the objects, the only inconvinience is that if whenever I need to add another user, then I have to run this alias script so that new use have access to them too.
    Perhaps I doing double work here but that works for me.

    marcos oliva

Posting Permissions

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