SQL0551N error when issuing 'REFRESH TABLE <table name>' command

Technote (troubleshooting)


Problem(Abstract)

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.

Symptom

When issuing 'REFRESH TABLE' command you may get the following:

For example:

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)
sqlstate: 42501


Cause

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).

Related information

REFRESH TABLE statement
SET INTEGRITY statement
SQL0551N

Rate this page:

(0 users)Average rating

Add comments

Document information


More support for:

DB2 for Linux, UNIX and Windows

Software version:

9.7, 9.8, 10.1, 10.5

Operating system(s):

AIX, HP-UX, Linux, Solaris, Windows

Reference #:

1665488

Modified date:

2014-02-24

Translate my page

Machine Translation

Content navigation