JDBC channel selecting different rows and updating differnet rows

[former_member214909]

Hello All,

I am working on JDBC scenario in which we are picking the data from third party database.

Data is huge so we were facing java memory issue and decided to split the data in 25k batch.

Now I am trying to pickup the data with maxxium 25K rows using rownum < 250001

But we are facing issue of picking the duplicate records.

Can anyone please help us on this, we are using following select and update query ?

SELECT T.*, CSBP.BATHNM, CSBP. QTYREG 

FROM cxl_sap_batch_process CSBP  LEFT OUTER JOIN (SELECT * FROM  gl_events_view  WHERE  status = 'P') T

ON  CSBP.ID = T.CXL_SAP_PROCESS_ID

WHERE CSBP.status = 'P'AND CSBP.OBJECT = 'EVENT_EXTRACTION_RUN'
AND rownum <20001

UPDATE gl_events_view SET STATUS = 'C'

  WHERE ID IN

  (SELECT T.ID FROM cxl_sap_batch_process CSBP  LEFT OUTER JOIN  (select * from gl_events_view  WHERE status = 'P') T

  ON CSBP.ID = T.CXL_SAP_PROCESS_ID

  WHERE CSBP.status = 'P' AND CSBP.OBJECT ='EVENT_EXTRACTION_RUN' AND rownum <20001)

Thanks ,

Anant

[Former Member]

Hi Anant,

Use ORDER BY statement on primary fields in both SELECT and UPDATE query, it will ensure your selection is same for both queries.

Regards,

Pranav

[former_member184720]

Please check the below blog and refer to the issue 1:

http://scn.sap.com/community/pi-and-soa-middleware/blog/2013/03/13/pi-jdbc-sender-adapter--some-lear...

Also your inner query "SELECT * FROM  gl_events_view  WHERE  status = 'P" does it always return the same rows?

[Former Member]

Hi,

Set the Transaction Isolation Level as serializable in advanced mode.

Regards,

Veerendra