SQL0551N error when issuing 'REFRESH TABLE <table name>' command
SQL0551N error can be thrown when issuing 'REFRESH TABLE <table name>' against an MQT table stating that the user does not have the required authorization or privilege to
perform operation "SET CONSTRAINTS" on the object.
When issuing 'REFRESH TABLE' command you may get the following:
db2 "refresh table db2inst1.mqt_t1"
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0551N "DB2INST2" does not have the required authorization or privilege to perform operation "SET CONSTRAINTS" on object "DB2INST1.MQT_T1".
If DIAGLEVEL is set to 4, it can be observed that "REFRESH TABLE" command in turn executes "SET CONSTRAINTS" command:
2014-02-23-19.43.57.004067-300 I4024G749 LEVEL: Info
PID : 32582 TID : 3037525760 PROC : db2bp
INSTANCE: db2inst1 NODE : 000 DB : FOO
APPID : *LOCAL.db2inst1.140223235039
FUNCTION: DB2 UDB, oper system services, sqlofica, probe:10
DATA #1 : SQLCA, PD_DB2_TYPE_SQLCA, 136 bytes
sqlcaid : SQLCA sqlcabc: 136 sqlcode: -551 sqlerrml: 40
sqlerrmc: DB2INST2 SET CONSTRAINTS DB2INST1.MQT_T1
sqlerrp : SQLRL2FD
sqlerrd : (1) 0x801A006D (2) 0x00000000 (3) 0x00000000
(4) 0x00000000 (5) 0xFFFFFF88 (6) 0x00000000
sqlwarn : (1) (2) (3) (4) (5) (6)
(7) (8) (9) (10) (11)
SQL0551N is thrown when issuing 'REFRESH TABLE <table name>' by a user who does not have either of the following authorization / privilege:
- CONTROL privilege on the table
- DATAACCESS authority
The user who is issuing the 'REFRESH TABLE <table name>' command must have at least one of the above authorization / privilege .
Resolving the problem
Grant the required privilege or authorization to the user who is issuing the command (i.e: CONTROL privilege on the table or DATAACCESS authority).
More support for:
DB2 for Linux, UNIX and Windows
Routines (SP & UDF) - SQL
Software version: 9.7, 9.8, 10.1, 10.5
Operating system(s): AIX, HP-UX, Linux, Solaris, Windows
Reference #: 1665488
Modified date: 18 November 2015