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

Technote (troubleshooting)


Problem(Abstract)

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

Cause

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

SET SESSION AUTHORIZATION statement
Terminate command
Connect Reset

Rate this page:

(0 users)Average rating

Add comments

Document information


More support for:

DB2 for Linux, UNIX and Windows
Operating System Security - DCE/Authentication

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:

2013-08-26

Translate my page

Machine Translation

Content navigation