Assistance in Adjusting DB2 for iSeries Permissions

[EY_C]

Hi Support,

We encountered this error while running a task

Not authorized to object JRN_NAME in JRN_LIB.
RetCode: SQL_ERROR SqlState: 58004 NativeError: -443 Message: [IBM][System i Access ODBC Driver]SQL0443 - Not authorized to object JRN_NAME in JRN_LIB.
Failed(retcode-1) to execute statement: 'SELECT sequence_number FROM TABLE (QSYS2.Display_Journal('JRN_LIB','JRN_NAME ')) as J LIMIT 1

https://help.qlik.com/en-US/replicate/November2021/Content/Replicate/Main/IBM%20DB2%20for%20iSeries/...

Our DBA has followed the official permission settings, but the above error still occurs. Currently, the error is resolved only if the File Authority is set to *ALL.

We would like to confirm why such a high level of authority is required and whether there are alternative permission settings that can be applied to resolve this issue.

Looking forward to your guidance.

[john_wang]

Hello @EY_C ,

Welcome to Qlik Community forum and thanks for reaching out here!

Qlik Replicate uses the QSYS2.Display_Journal table function to capture changes from the DB2 for i (DB2i) journal. The required permissions for change data capture come from this function. If you encounter error messages, follow these steps to troubleshoot:

1. Log in to the AS/400 system using the same account that you configured for the DB2i source endpoint in Qlik Replicate.

2. Execute the following SQL query in STRSQL:

   SELECT sequence_number FROM TABLE (QSYS2.Display_Journal('JRN_LIB','JRN_NAME ')) as J LIMIT 1

3. If you receive the same error, I'd like to suggest involving your DBA to investigate the root cause. And please share your result.

4. If you can share the account creation script, please share with us. We'd love to confirm the behavior for you.

Regards,

John.

[EY_C]

Sorry for the late reply. The result of the SQL query is in the screenshot. 

The DBA mentioned that no script was used, so I am providing the User Profile. Could you please assist in clarifying the permission issue?

[john_wang]

Hello @EY_C ,

This is a permission issue, please check chapter Required permissions. You may narrow down the issue by a higher privileges account to confirm it's a permission issue.

Regards,

John.

[EY_C]

Hi @john_wang ,

We have confirmed that the required object authorities have been correctly granted, including *USE on the journal, *EXECUTE on the journal library, and *USE on all associated receivers.

However, the following query still fails with a permissions error:
SELECT sequence_number FROM TABLE(QSYS2.Display_Journal('JRN_LIB', 'JRN_NAME')) AS J LIMIT 1;

We would like to check with you:

1. Is it possible that certain low-level system routines—such as triggers, external procedures, or internal validation checks—might perform additional access validations on journal-related objects (e.g., journaled files, associated objects in journal entries) and cause the Display_Journal() function to terminate early when authority is insufficient?
2. Alternatively, could it be that due to version differences of the DB2 engine, certain environments require *ALL authority on the journal object or its receivers to allow the API behind Display_Journal() to operate properly?

We would appreciate it if you could provide a supportable reason for requiring *ALL authority on the journal object or its related resources, so we can communicate this clearly to our client.

Thank you for your help.

[john_wang · Accepted solution]

Hello @EY_C ,

Thanks for the detailed information.

It appears that there may be special configurations in the system that are causing behavior different from that of a standard AS400 environment. Since the issue lies outside of Qlik Replicate, we recommend involving your DBA or IBM support to review the AS400 system job logs and error messages to help isolate the root cause. Please consult IBM for further investigation.

Regards,

John.