article Qlik Replicate DB2 z/OS - [SOURCE_CAPTURE ]E: RetCode: SQL_ERROR SqlState: 42501 NativeError: -551 Message: [IBM][CLI Driver][DB2] SQL0551N in Official Support Articles

Qlik Replicate DB2 z/OS - [SOURCE_CAPTURE ]E: RetCode: SQL_ERROR SqlState: 42501 NativeError: -551 Message: [IBM][CLI Driver][DB2] SQL0551N

Barb_Fill21 — Mon, 03 Apr 2023 09:04:41 GMT

Qlik Replicate with a DB2 z/OS source endpoint (using R4Z) displays the following errors:

SYSLOG :

DSNT408I SQLCODE = -551, ERROR: CHA814 DOES NOT HAVE THE PRIVILEGE TO
PERFORM OPERATION EXECUTION ON OBJECT R4ZUSERT.R4Z_CDC_UDTF

Task log:

[SOURCE_CAPTURE ]E: RetCode: SQL_ERROR SqlState: 42501 NativeError: -551 Message: [IBM][CLI Driver][DB2] SQL0551N The statement failed because the authorization ID does not have the required authorization or privilege to perform the operation. Authorization ID: "USERID". Operation: "EXECUTION". Object: "R4ZUSER.R4Z_CDC_UDTF__CDC1". SQLSTATE=42501 [1022502] (ar_odbc_stmt.c:4011)

Resolution

Verify that all permissions and authorizations are properly set.

Run job DO4GRANT. It will:
- GRANT SELECT to the ODBC user on the following tables:
  - SYSIBM.SYSTABLES
  - SYSIBM.SYSTABLESPACE
  - SYSIBM.SYSCOLUMNS
  - SYSIBM.SYSROUTINES
  - SYSIBM.SYSDUMMY1
  - ALTER SYSIBM.SYSTABLES enabling DATA CAPTURE.
Jobs should terminate with RC 0 or RC 4.

Authorizations:

The submitting user must have either of the following authorizations:
- SYSADM or SYSCTRL authority.
- TRACE/ MONITOR2 with grant option.
Provide security permissions for the WLM started task to cancel itself. In RACF this is done as follows:

Define a PROFILE for the OPERCMDS class called PERMIT MVS.CANCEL.STC.mbrname.*:

RDEFINE OPERCMDS MVS.CANCEL.STC.mbrname.* UACC(NONE) SETROPTS REFRESH RACLIST(OPERCMDS)

Where mbrname is the name of the started task.
Grant the USERID assigned to the WLM started TASK the authority to cancel the started task:

PERMIT MVS.CANCEL.STC.mbrname.* CL(OPERCMDS) ACCESS(UPDATE) ID(stcuser)

Where stcuser is the USERID associated with the WLM started task.

Required permissions

To enable Replicate to extract data from the source tables (Full Load and Change Data Capture), the user specified in the IBM DB2 for z/OS endpoint settings must be granted the following permissions:

EXECUTE on the IFI reading the UDTF (only required for Change Data Capture)
SELECT on the source tables and on the following system catalog tables:
- SYSIBM.SYSDUMMY1
- SYSIBM.SYSTABLES
- SYSIBM.SYSTABLESPACE
- SYSIBM.SYSPARMS
- SYSIBM.SYSROUTINES
- SYSIBM.SYSCOLUMNS
- SYSIBM.SYSLGRNX
- SYSIBM.SYSTABLEPART
MONITOR2 to be able to start IFI sessions (only required for Change Data Capture)

Environment

Qlik Replicate and corresponding R4Z( all versions)
IBM DB2 z/OS ( all supported versions)

Re: Qlik Replicate DB2 z/OS - [SOURCE_CAPTURE ]E: RetCode: SQL_ERROR SqlState: 42501 NativeError: -551 Message: [IBM][CLI Driver][DB2] SQL0551N

fabiobonetto — Thu, 06 Jul 2023 13:19:59 GMT

Hi all,

select privilege is used only in full load action, isn't it? As i understood in change data capture data will be captured from logs and user don't need to keep data from table. I'am wrong?

Best,

Fabio

Re: Qlik Replicate DB2 z/OS - [SOURCE_CAPTURE ]E: RetCode: SQL_ERROR SqlState: 42501 NativeError: -551 Message: [IBM][CLI Driver][DB2] SQL0551N

OritA — Mon, 10 Jul 2023 08:22:46 GMT

Hi Fabio,

The select privileges are not used ONLY in full load. Whenever a task is started Replicate reads the table metadata and other system information for the system tables. If you turn on TRACE logging on SOURCE_CAPTURE and METADATA_MANAGER you can see in the log the exact select commands that are performed by the task.

As mentioned above, and also indicated in the Replicate users guide, there are some permissions that are required ONLY for CDC and not for Full Load but not vice versa.

(https://help.qlik.com/en-US/replicate/May2023/Content/Global_Common/Content/SharedReplicateHDD/IBM4DB2-zos/prereq_db2.htm#Prerequisites)

Hope that this answers your question. IF you have more specific question regarding a specific permission requirement please open a case.

Thanks & regards,

Orit

Re: Qlik Replicate DB2 z/OS - [SOURCE_CAPTURE ]E: RetCode: SQL_ERROR SqlState: 42501 NativeError: -551 Message: [IBM][CLI Driver][DB2] SQL0551N

fabiobonetto — Mon, 10 Jul 2023 08:45:32 GMT

Hi,

I've resolved/found issue. When I've a LOB data Replicate don't read data directly from logs but need to query data from table. With this behaviour in the meantime Replicate catch change from logs and the query, the LOB could be newly changed and replicate the wrong data.

For this reason I asked if SELECT grant was needed for read data changing.

Best,

Fabio

article Qlik Replicate DB2 z/OS - [SOURCE_CAPTURE ]E: RetCode: SQL_ERROR SqlState: 42501 NativeError: -551 Message: [IBM][CLI Driver][DB2] SQL0551N in Official Support Articles

Qlik Replicate DB2 z/OS - [SOURCE_CAPTURE ]E: RetCode: SQL_ERROR SqlState: 42501 NativeError: -551 Message: [IBM][CLI Driver][DB2] SQL0551N

Resolution

Required permissions

Environment

Related Content

Re: Qlik Replicate DB2 z/OS - [SOURCE_CAPTURE ]E: RetCode: SQL_ERROR SqlState: 42501 NativeError: -551 Message: [IBM][CLI Driver][DB2] SQL0551N

Re: Qlik Replicate DB2 z/OS - [SOURCE_CAPTURE ]E: RetCode: SQL_ERROR SqlState: 42501 NativeError: -551 Message: [IBM][CLI Driver][DB2] SQL0551N

Re: Qlik Replicate DB2 z/OS - [SOURCE_CAPTURE ]E: RetCode: SQL_ERROR SqlState: 42501 NativeError: -551 Message: [IBM][CLI Driver][DB2] SQL0551N