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

[Barb_Fill21]

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. 

1. 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.
     
     
2. Provide security permissions for the WLM started task to cancel itself. In RACF this is done as follows:

1. 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.

2. 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) 

Related Content 

Installing and upgrading the R4Z product in z/OS | Qlik Replicate Help
Prerequisites | Qlik Replicate Help

[fabiobonetto]

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

[OritA]

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/IBM4D...)

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

Thanks & regards,

Orit 

[fabiobonetto]

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