Qlik Replicate task data synchronization failed from SQLMI to AS400: SqlState: 42000 NativeError: Attributes not valid

[wei_guo]

Qlik Replicate task data synchronization failed to replicate data from SQLMI to an AS400 (iSeries/DB2) target via an ODBC endpoint, with new tasks failing during initialization when generating and executing CREATE TABLE statements for internal control tables (such as attrep_apply_exceptions) on the target.

Example error:

Attributes not valid

Detailed log:

Stream component 'st_0_AS400 - PSFWEB - MC - Target' terminated
Stream component failed at subtask 0, component st_0_AS400 - PSFWEB - MC - Target
Error executing command
Cannot create Exception table
Cannot create Special table
execute create table statement failed, statement CREATE TABLE "attrep_apply_exceptions" ( "TASK_NAME" VARCHAR(128) NOT NULL, "TABLE_OWNER" VARCHAR(128) NOT NULL, "TABLE_NAME" VARCHAR(128) NOT NULL, "ERROR_TIME" TIMESTAMP NOT NULL, "STATEMENT" CLOB(0) NOT NULL, "ERROR" CLOB(0) NOT NULL )
RetCode: SQL_ERROR SqlState: 42000 NativeError: -604 Message: [IBM][System i Access ODBC Driver][DB2 for i5/OS]SQL0604 - Attributes not valid.
Failed (retcode -1) to execute statement: CREATE TABLE "attrep_apply_exceptions" ( "TASK_NAME" VARCHAR(128) NOT NULL, "TABLE_OWNER" VARCHAR(128) NOT NULL, "TABLE_NAME" VARCHAR(128) NOT NULL, "ERROR_TIME" TIMESTAMP NOT NULL, "STATEMENT" CLOB(0) NOT NULL, "ERROR" CLOB(0) NOT NULL )

Resolution

Set Provider Syntax to DB2 in the target endpoint.

• Go to your Target Endpoint
• Switch to the Advanced tab
• Set the correct Provider Syntax 

  Engage Qlik's Professional Services for more direct assistance for custom ODBC endpoint setups due to provider-specific SQL syntax rules.

Cause

SQL0604 / NativeError -604 means Db2 for IBM i rejected an invalid data-type attribute, typically a column or expression with an unsupported length, precision, scale, timestamp precision, or ALLOCATE value. This is normally an SQL-definition problem, not a network or ODBC connection problem. (on QR V2023.5.0.322)

The task fails because Qlik Replicate generates this invalid Db2 for i definition:

"STATEMENT" CLOB(0) NOT NULL,
"ERROR"     CLOB(0) NOT NULL

On Db2 for IBM i, a CLOB length must be between 1 byte and 2 GB. Therefore, CLOB(0) produces SQL0604 / -604 – Attributes not valid.  Other column definitions are valid.

That indicates the target endpoint is missing or uses the wrong configuration for the Endpoint --> Advanced tab --> Provider syntax setting.
 

Environment

• Qlik Replicate