JWP cannot truncate table during XEvents cleanup

Products

• CA Automic Workload Automation - Automation Engine

Issue/Introduction

On a AE system with DB2 database, the JWP cannot truncate XEVT table during XEvents cleanup.

In the JWP log the following error is displayed:

20221228/010000.355 - 22     U00045448 Planned cleanup of XEvents table could not be performed.
20221228/010000.355 - 22     U00003590 UCUDB - DB error: '42601', 'DB2 SQL Error: SQLCODE=-104, SQLSTATE=42601, SQLERRMC=END-OF-STATEMENT;TRUNCATE TABLE XEVT5;IMMEDIATE, DRIVER=4.31.10', '-104', 'com.ibm.db2.jcc.am.SqlSyntaxErrorException'
20221228/010000.355 - 22               SQL Statement which caused this DB error:
20221228/010000.355 - 22               TRUNCATE TABLE XEVT5
20221228/010000.356 - 22     U00045014 Exception 'com.automic.database.api.DBException: "TRUNCATE TABLE XEVT5
20221228/010000.356 - 22     An unexpected token "END-OF-STATEMENT" was found following "TRUNCATE TABLE XEVT5".  Expected tokens may include:  "IMMEDIATE".. SQLCODE=-104, SQLSTATE=42601, DRIVER=4.31.10"' at 'com.automic.database.impl.DBConnectionImpl.executeInternal():573'.
20221228/010000.357 - 22     U00045015 The previous error was caused by 'com.ibm.db2.jcc.am.SqlSyntaxErrorException: "DB2 SQL Error: SQLCODE=-104, SQLSTATE=42601, SQLERRMC=END-OF-STATEMENT;TRUNCATE TABLE XEVT5;IMMEDIATE, DRIVER=4.31.10"' at 'com.ibm.db2.jcc.am.b7.a():810'.
20221228/010000.357 - 22     U00003620 Routine 'com.automic.kernel.impl.DBAction' forces trace because of error.
20221228/010000.358 - 22     U00003450 The TRACE file was opened with the switches '0000000000000000'.
20221228/010000.452 - 22     U00003449 Output to the TRACE file is finished.

Environment

Release : 21.0.4

So far this problem has only been detected on a systems with a DB2 database

Cause

The AE sends the wrong command to the DB: "TRUNCATE TABLE XEVT5;IMMEDIATE" 
There is a wrong semi-colon before IMMEDIATE.

Resolution

This is a defect and will be fixed in a future version of the product.

Note that there is NO impact unless AAI is used.