DB2 ZOS V11, TRUNCATE TABLE getting -551(privilege issue)

Vijay Kumar R

DB2 ZOS V11, TRUNCATE TABLE getting -551(privilege issue)

A user is trying to truncate a table which Do NOT have any triggers and Not participating in any constraints receiving -551 error when tried to truncate table. we observe the user ID has sel/ins/upd/del access on table.

User has mentioned set current SQLID properly.

Please advise here, I'm clueless.

Thanks,

Vijay

 

Michael Hannan

RE: DB2 ZOS V11, TRUNCATE TABLE getting -551(privilege issue)
(in response to Vijay Kumar R)

Try coding

RESTRICT WHEN DELETE TRIGGERS

This is not the default. Yes I know you said there are no triggers. Without this the manual says you need ALTER Auth.
 
In Reply to Vijay Kumar R:

A user is trying to truncate a table which Do NOT have any triggers and Not participating in any constraints receiving -551 error when tried to truncate table. we observe the user ID has sel/ins/upd/del access on table.

User has mentioned set current SQLID properly.

Please advise here, I'm clueless.

Thanks,

Vijay

 



Michael Hannan,
DB2 Application Performance Specialist
CPT Global Ltd

Kai Stroh

RE: DB2 ZOS V11, TRUNCATE TABLE getting -551(privilege issue)
(in response to Vijay Kumar R)

Also check if you are using CURRENT RULES = 'STD'. This affects the SQL errors that Db2 returns. For example, if there is a typo in the table name, Db2 issues a -551 instead of a more meaningful error (in this case -204). This can be a little confusing because the problem has nothing to do with missing authorization, even if the error message suggests that.

See https://www.ibm.com/support/knowledgecenter/SSEPEK_12.0.0/sqlref/src/tpc/db2z_currentrules.html

You can simply execute SELECT CURRENT RULES FROM SYSIBM.SYSDUMMY1 to check, it will return either 'STD' or 'DB2'.

--
Kai Stroh
UBS Hainer
Fast, efficient Db2 z/OS data migrations and renewals. That’s BCV5.
Learn how the Test Data Management Field Guide can help you to improve your own process.