IBM Support

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

Troubleshooting


Problem

SQL0551N error can be thrown when issuing 'REFRESH TABLE

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

[{"Product":{"code":"SSEPGG","label":"Db2 for Linux, UNIX and Windows"},"Business Unit":{"code":"BU058","label":"IBM Infrastructure w\/TPS"},"Component":"Routines (SP & UDF) - SQL","Platform":[{"code":"PF002","label":"AIX"},{"code":"PF010","label":"HP-UX"},{"code":"PF016","label":"Linux"},{"code":"PF027","label":"Solaris"},{"code":"PF033","label":"Windows"}],"Version":"9.8;9.7;10.1;10.5","Edition":"","Line of Business":{"code":"LOB10","label":"Data and AI"}}]

Document Information

Modified date:
16 June 2018

UID

swg21665488