IBM Support

When "set session authorization" is run within a federated connection users will hit SQL0428N

Technote (troubleshooting)


A "set session authorization" when run within a federated connection followed by a commit or a rollback statement users will still hit SQL0428N error.


This is because a Federated query has been executed in the current connection and the connection is not reset. DB2 does not allow users to run "set session authorization" in the Federated connection after a commit or rollback, as DB2 will still maintain the connection.

Diagnosing the problem

In a federation environment, when a "set session authorization" command is run as the first statement in a unit of work (UOW) but after a commit or rollback of a transaction the SQL0428N error might be returned. This error could be misleading as it should be returned only if the statement is not the first statement in a UOW.

For example if this command was run after commit or rollback, you may see the following message :

    set session authorization testID

DB21034E The command was processed as an SQL statement because it was  
not a valid Command Line Processor command. During SQL processing it returned:
SQL0428N The SQL statement is only allowed as the first statement in a unit of work.  SQLSTATE=25001

Resolving the problem

The recommendation is to avoid executing "set session authorization" statement after a federated query is run. Currently the connection is kept until a 'connect reset' or 'terminate' command is done and a new connection is started.

Related information

Terminate command
Connect Reset

Document information

More support for: DB2 for Linux, UNIX and Windows
Security / Plug-Ins - IBM Suplied/Default

Software version: 9.1, 9.5, 9.7

Operating system(s): AIX, HP-UX, Linux, Solaris, Windows

Software edition: Enterprise Server, Express, Workgroup Server

Reference #: 1441985

Modified date: 22 March 2016

Translate this page: