Error at SetMatchingByteOrder: Unexpected Error. Original SQLCODE -2018: message SQL2018N

[OritA]

When working with Qlik Replicate and DB2 LUW source endpoint and using DB2 trusted context, you may encounter the following error:

[SOURCE_CAPTURE  ]E:  Error at 'SetMatchingByteOrder': Unexpected Error. Original SQLCODE -2018: ' message SQL2018N The utility encountered an error "" while attempting to verify the user's authorization ID or database authorizations.

This error is received although the db2 user that is used by Replicate to access the DB2 endpoint is granted DBADM via DB2 Role.

Environment

• Qlik Replicate working with DB2 LUW source endpoint using trusted context

Resolution

To solve the problem, the user used by Qlik Replicate to access DB2 has to be granted DBADM directly (and not through a role).

 

Cause 

Based on information received from IBM support concerning granting DBADM via DB2 Role vs directly on the user, there is a documented differentiation when DDL statements are issued (create/drop/alter tables etc), and when ReadLog API is called. When ReadLog API is called (as in case of Replicate) : “the only way to grant authorization on the readlog API is to grant DBADM directly to a user, or make the user a SYSADM”

Additional detailed explanation for root cause:

In DB2, when issuing a data manipulation language (DML) SQL statement (insert/update/delete/etc.) using a trusted connection, the privileges held by a context-assigned role in effect for the authorization ID within the definition of the associated trusted context are considered in addition to other privileges directly held by the authorization ID of the statement, or indirectly by other roles held by the authorization ID of the statement.
The privileges held by a context-assigned role in effect for the authorization ID within the definition of the associated trusted context are not considered for data definition language (DDL) SQL statements. For example, to create an object, the authorization ID of the statement must be able to do so without including the privileges held by the context-assigned role.

In the case of Replicate ReadLog API call, the account might have DBADM through the trusted context for DML purposes, and it might show in UTH_LIST_AUTHORITIES_FOR_AUTHID, but it is not valid for sqlpgReadLogAPI. That is the reason that you get the error.

To conclude,  there's no way to grant the authority to use the read log API directly to the user while keeping DBADM through the trusted context. Currently, when working with trusted context, the only way to grant authorization on the readlog API is to grant DBADM directly to a user, or make the user a SYSADM.