Sender JDBC (I-Series) fetch first N rows only

[former_member434498]

Hi Team,

I'm trying to pull 1,000 rows at a time from an I-Series table via JDBC PI 7.31.

There are 10,000 rows from the table and I want to pick 1,000 at a time and create a PI message.

Then the next 1,000 row will be picked and so on..

The below Query statement works fine.

SELECT * FROM ISP#HB WHERE HBPROCESSD !='X' FETCH FIRST 1000 ROWS ONLY

However, I would also need to update the each rows with an 'X' to indicate that it was picked.

I tried to use the same syntax "FETCH FIRST 100 ROWS ONLY" but it does not work for update statement.

UPDATE ISP#HB SET HBPROCESSD = 'X' WHERE HBPROCESSD !='X' FETCH FIRST 1000 ROWS ONLY

Thanks,

Carlo

[former_member190293]

Hi Carlo!

Did you try this syntax (with changes according to your requirements):

UPDATE table1 SET field1 = 1
WHERE table1Key IN (SELECT table1Key
   FROM table1
   WHERE field1 <> 1
   ORDER BY field1
   FETCH FIRST 1000 ROWS ONLY)

I suggest to use ORDER BY class in both of your queries otherwise you can't be sure that you get identical recordsets.

Regards, Evgeniy.