When "set session authorization" is run within a federated connection users will hit SQL0428N
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.
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