How to use EXECUTE INTO statement in db2 iSeries as400 db?

Question

I have a procedure with the following lines:

The value of ITERATOR is set dynamically in a loop

  DECLARE V_LOSS_ID integer;
  DECLARE V_STATE_CODE CHARACTER(2);
  DECLARE V_CLASS_CODE CHARACTER(4);
  SET V_QUERY = 'set (?,?,?) = (select LOSS_ID, STATE_CODE, CLASS_CODE from LOSS L limit 1 offset '|| ITERATOR || ' )';
  PREPARE STMT FROM V_QUERY;
  EXECUTE STMT into V_LOSS_ID, V_STATE_CODE, V_CLASS_CODE;

I am getting the following error in iSeries db2 - [SQL0104] Token V_LOSS_ID was not valid. Valid tokens: SQL DESCRIPTOR.

However, this procedure works on DB2 LUW database. Facing this issue only on DB2 iseries as400 database

Answer 1

EXECUTE in Db2 for IBM i has different functionality.
So, use something like below:

DECLARE ITERATOR ...;
DECLARE V_LOSS_ID integer;
DECLARE V_STATE_CODE CHARACTER(2);
DECLARE V_CLASS_CODE CHARACTER(4);
DECLARE C1 CURSOR FOR STMT;
...
--SET V_QUERY = 'set (?,?,?) = (select LOSS_ID, STATE_CODE, CLASS_CODE from LOSS L limit 1 offset '|| ITERATOR || ' )';
SET V_QUERY = 'select LOSS_ID, STATE_CODE, CLASS_CODE from LOSS L limit 1 offset '|| ITERATOR;
PREPARE STMT FROM V_QUERY;
--EXECUTE STMT into V_LOSS_ID, V_STATE_CODE, V_CLASS_CODE;
OPEN C1;
FETCH C1 INTO V_LOSS_ID, V_STATE_CODE, V_CLASS_CODE;
CLOSE C1;