Qlik Replicate with DB2 iSeries source: excessive access attempts

[Shai_E]

When using IBM Db2 iSeries as a source endpoint, a Qlik Replicate task can, in rare cases, trigger a large number of access-attempt messages at the database level. These messages are recorded in the IBM i audit journal, generating several GB of audit data and placing a critical load on the AS/400 system. This results in resource overhead on the database.

The message at the database level may look similar to the following:

Message: The user-defined procedure or function OBJECT_STATISTICS in QSYS2 returned an SQLSTATE warning.

Cause: An SQLSTATE in the 01xxx format was returned by the user-defined procedure or function OBJECT_STATISTICS in QSYS2 (specific name QSQOBSTAT), along with the message text NOT AUTHORIZED TO OBJECT XXX/BBB *FILE.

Environment

• Qlik Replicate

Resolution

Contact the database administrator responsible for the IBM Db2 for i (AS/400) system and verify the library settings in DB2 for the current OBJAUD value. If OBJAUD is set to *ALL instead of *NONE, this is likely the cause of the excessive audit journal entries. Have the administrator adjust the value as appropriate for the environment.

Cause

This behavior is caused by a specific IBM Db2 for i library parameter: OBJAUD. For background, see Object auditing | IBM Documentation.

• If OBJAUD is set to *ALL on a library, or to *USRPRF on an object, an audit record is written every time a user reads or changes that object.
• If OBJAUD is set to *NONE, no auditing occurs.

The extensive object-access auditing on the database is typically the result of OBJAUD being set to *ALL rather than *NONE on the affected library.