Results 1 to 9 of 9
  1. #1
    Join Date
    Jan 2004
    Posts
    70

    Unanswered: refresh table in a stored procedure?

    if I put a "refresh table mytable;" anywhere in a stored procedure I get a
    "SQL0551N An unexpected token 'refresh' was found following 'the previous line'..."

    Even if it is the only line in a procedure. And all of my procedures are
    modifies sql data
    not deterministic
    language sql

    IBM's documentation says a refresh table should work in a procedure. If I do the refresh table line regularly it works fine.

    What am I doing wrong?

  2. #2
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    SQL0551N "<authorization-ID>" does not have the privilege to
    perform operation "<operation>" on object
    "<name>".
    Explanation:
    Authorization ID "<authorization-ID>" attempted to perform the
    specified "<operation>" on "<name>" without the proper
    authorization.
    If creating or altering a table with referential constraints,
    this message (SQLCODE) can indicate that the user does not have
    the REFERENCES privilege to create or drop a FOREIGN KEY. In
    this case the "<operation>" is "REFERENCES" and the "<name>"
    is the object the constraint references.
    If attempting to run a DB2 utility or CLI application, the DB2
    utility programs may need to be rebound to the database because
    the userid who created the database no longer exists or no longer
    has the required privileges.
    If this error occurs during invocation of a routine, then
    authorization ID "<authorization-ID>" does not have the EXECUTE
    privilege on any candidate routine in the SQL path. "<name>" is
    the name of a candidate routine in the SQL path.
    Federated system users: if this message is returned when the
    user is changing the remote_pw column of the SYSCAT.USEROPTIONS
    view, the user is not authorized to change a password for another
    user. The user performing the alter operation must have either
    SYSADM authority or an authorization ID (the value in the USER
    special register) that matches the value of the authid column in
    the rows being updated. Some data sources do not provide the
    appropriate values for "<authid>", <operation>, and <name>.
    In these cases the message tokens will have the following format:
    "<data source> AUTHID:UNKNOWN", "UNKNOWN", and "<data
    source>:TABLE/VIEW", indicating that the actual values for the
    authid, operation, and name at the specified data source are not
    known.
    The statement cannot be processed.
    User Response:
    Ensure that "<authorization-ID>" has the authorization necessary
    to perform the operation.
    Federated system users: this authorization can be at the
    federated server, the data source, or both.
    If the DB2 utility programs need to be rebound to the database,
    the database administrator can accomplish this by issuing one of
    the following CLP command from the bnd subdirectory of the
    instance, while connected to the database:
    o "DB2 bind @db2ubind.lst blocking all grant public" for the
    DB2 utilities.
    o "DB2 bind @db2cli.lst blocking all grant public" for CLI.

    sqlcode : -551
    sqlstate : 42501
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  3. #3
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    BTW, do you know if privileges on tha table have been granted to you as individual user or at group level ?

    For deploying Static SQL in SP, you need priivleges explicitly defined for the user

    HTH

    Sathyaram
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  4. #4
    Join Date
    Jan 2004
    Posts
    70
    I dont know how I was getting SQL0551N

    Now whenever I try to do it, I get the same text but SQL0104N

    I dont know if its group or user level. The ID I am using is the same as the schema I am working in. It has full privileges in that schema.

  5. #5
    Join Date
    Dec 2005
    Posts
    18
    try using full table name including schema,

    refresh table db2admin.mytable

  6. #6
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by sathyaram_s
    BTW, do you know if privileges on tha table have been granted to you as individual user or at group level ?

    For deploying Static SQL in SP, you need priivleges explicitly defined for the user

    HTH

    Sathyaram
    Do you know where this is documented? I don't see anything in the SQL Reference Vol 2, under GRANT (Routine Privileges).

    Does this apply to UDF's also?
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  7. #7
    Join Date
    Jan 2004
    Posts
    70
    Quote Originally Posted by sun4u
    try using full table name including schema,

    refresh table db2admin.mytable
    Same error.

    Now my UDB DBA is saying "refresh table" cannot be called in a procedure...

    SQL-statement
    All executable SQL statements can be contained within the body of an SQL procedure, with the exception of the following:
    ALTER
    CONNECT
    CREATE any object other than indexes, tables, or views
    DESCRIBE
    DISCONNECT
    DROP any object other than indexes, tables, or views
    FLUSH EVENT MONITOR
    REFRESH TABLE
    RELEASE (connection only)
    RENAME TABLE
    RENAME TABLESPACE
    REVOKE
    SET CONNECTION
    SET INTEGRITY
    SET PASSTHRU
    SET SERVER OPTION




    Guess I was looking at the wrong publib.boulder.ibm.com page :shrug:

  8. #8
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    Applies to any static SQL statement being bound as a package (and therefore, UDFs excluded)
    Here is a Technote explaining this in more detail:

    http://www-1.ibm.com/support/docview...id=swg21224422


    The underlying reason is that changes to group memberships are not communicated to DB2. DB2 has no way of knowing if the authorization ID of the user who created the package is still part of the group when the package is executed. This is in constrast to if the authorization ID was explicitly granted access - DB2 keeps track of this and will invalidate the package if that privilege was revoked from the package creator.


    For dynamic SQL, all of these checks are made at runtime.


    Cheers

    Sathyaram





    Quote Originally Posted by Marcus_A
    Do you know where this is documented? I don't see anything in the SQL Reference Vol 2, under GRANT (Routine Privileges).

    Does this apply to UDF's also?
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  9. #9
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    JamesAvery22 - Do you know your version number and platform your db2 server is on ?

    On the server, use db2level command to get this information

    Cheers
    sathyaram
    Visit the new-look IDUG Website , register to gain access to the excellent content.

Posting Permissions

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