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?

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

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

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.

try using full table name including schema,

refresh table db2admin.mytable

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?

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:

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

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