DB2 SQL Error SQLCODE 551 SQLSTATE 42501 | Solved

If you’re getting DB2 SQL Error SQLCODE 551 SQLSTATE 42501, chances are your user account doesn’t have the required privilege. Perhaps, you use a different user account from other clients. So, try to grant access to the account you’re using to run the query.

The actual error message here is the SQLCODE -551, which corresponds to a security error that usually occurs when you move the database from one DB2 server to a different DB2 server.

What Is SQLCODE 551 SQLSTATE 42501 and Message Tokens?

Getting the SQLCODE 551 means there is an issue with the privilege level. It indicates that the user does not have access to the database or table referred to in their SQL query.

It can be due to a wrong security configuration on DB2, usually occurring when the database gets moved to a new DB2 server from an existing DB2 server. For example, you may use the following command to restore the database to a target server.

db2set DB2_RESTORE_GRANT_ADMIN_AUTHORITIES=
db2stop force
db2start
db2 restore db CCR_DEV FROM . ON /home/prod_system_account REPLACE EXISTING
db2 rollforward db CCR_DEV complete NORETRIEVE
db2 UPDATE db cfg FOR CCR_DEV USING LOGARCHMETH1 DISK:/home/prod_system_account/logs
db2 activate db ccr_dev

Here, CCR_DEV = Controller database, test_system_account = System Account for Test DB2 and prod_system_account = System Account for Production DB2.

When using Controller, the error message may look like:

SQLCODE "-551", SQLSTATE "42501" and message tokens "test_system_account|SELECT|SYSCAT.TABLES". SQLSTATE=56098

As you can see, the message is mentioning the user ‘test_system_account’. But there might be some other operating system user account used to configure the connection of Controller and the database. Hence, the error.

In this case, there are multiple databases stored on two different DB2 servers. Although the Test database worked fine, its copy, the Production database didn’t. Therefore, we can see the ‘test_system_account’ being mentioned in the error message, which does not exist in the Production DB2.

Generally, a user gets such access through RACF (Resource Access Control Facility) instead of a typical user level.

RACF is a security measure at the OS level responsible for access control and auditing. There are RACF groups for every mainframe user to control access.

How to Resolve SQLCODE 551?

Resolving the DB2 SQL Error SQLCODE 551 SQLSTATE 4250 will require you to use the following commands when restoring DB2 Controller backups.

db2set DB2_RESTORE_GRANT_ADMIN_AUTHORITIES=ON
db2stop force
db2start
db2 restore db CCR_DEV FROM . ON /home/prod_system_account REPLACE EXISTING
db2 rollforward db CCR_DEV complete NORETRIEVE
db2 UPDATE db cfg FOR CCR_DEV USING LOGARCHMETH1 DISK:/home/prod_system_account/logs
db2 activate db ccr_dev
db2 CONNECT TO ccr_dev
db2 REVOKE accessctrl, bindadd, CONNECT, createtab, create_external_routine, create_not_fenced_routine, dbadm, dataaccess, implicit_schema, LOAD, quiesce_connect ON DATABASE FROM USER test_system_account;
db2 CONNECT reset

Conclusion

To conclude, we can see that DB2 doesn’t seem to enforce group permissions to functions defined by the user. So, a possible solution would be to use roles instead of groups, as they are more robust when it comes to managing permissions. That way, you can avoid getting the SQLCODE 551 error.