Error while updating DB2 Database table on AS/400 using Business Objects Data Integrator 11.7

Question

we need to update records in DB2 database table on AS/400 using Business Objects Data Integrator 11.7. Data integrator Designer version is 11.7.3 Drivers installed are iSeries ACCESS ODBC Driver 11.0.2,CLIENT ACCESS ODBC DRIVER 11.0.2

We are able to do the update for a single record, but while doing Bulk Update, we are getting below error message, Can anyone help on this.

DBS-070401: ODBC DATA SOURCE <xxxx> Error Message for Operation
<SQLExecute>: <[IBM][iSeries Access ODBC Driver]Driver not capable.>
RUN-051005:
Execution of <Regular Load Operations> for target <XXX_XXXX> failed. Possible causes: (1) Error in the SQL syntax; (2) Database
connection is broken; (3) Database related errors such as transaction log is full, etc.; (4) The user defined in the datastore
has insufficient privileges to execute the SQL. If the error is for preload or postload operation, or if it is for regular load
operation and load triggers are defined, please check the SQL. Otherwise, for (3) and (4), please contact your local DBA.

If the select Query for the DI job is filtered for only one record, below update statement is generated and is executed through Data Integrator. In this case, no driver error is displayed.

UPDATE TIBCO_STG.PUB_AREA SET ADB_L_DELIVERY = 'C'   WHERE ADB_SEQUENCE = 22849415 

If the select query is not filtered, below four update SQLs are Generated by the DI Job, Driver error is displayed on this Bulk records update.

UPDATE TIBCO_STG.PUB_AREA SET ADB_L_DELIVERY = 'C'   WHERE ADB_SEQUENCE = 22849415 
UPDATE TIBCO_STG.PUB_AREA SET ADB_L_DELIVERY = 'C'   WHERE ADB_SEQUENCE = 22849416 
UPDATE TIBCO_STG.PUB_AREA SET ADB_L_DELIVERY = 'C'   WHERE ADB_SEQUENCE = 22849417 
UPDATE TIBCO_STG.PUB_AREA SET ADB_L_DELIVERY = 'C'   WHERE ADB_SEQUENCE = 22849418

Answer 1

I don't see any semicolons between your UPDATE statements.

You may also want to consider other methods. For example:

UPDATE TIBCO_STG.PUB_AREA 
  SET ADB_L_DELIVERY = 'C' 
  WHERE ADB_SEQUENCE in (22849415, 22849416, 22849417, 22849418)

or

UPDATE TIBCO_STG.PUB_AREA 
  SET ADB_L_DELIVERY = 'C' 
  WHERE ADB_SEQUENCE in (select seq from someFullSelectOrWorkFile)

If there is some way to code the WHERE condition as a fullSelect, you would be able to accomplish this in a single SQL statement. Set-at-a-time processing is far more efficient than row-at-a-time.