Hello All,
I am trying to find the limitations for extracting records from Oracle DB using wM JDBC - CustSQL service. I tried to find it but I am still not finding the exact answer that I am looking for and I might be missing some places for an answer.

Req:
Daily Scheduled Job to pull records (850K +) from Oracle DB (10G) using wM 8.1 by custSQL adapter service.

Please provide your i/p’s ASAP.

Thanks.

You’ll want to devise an approach to iterate through the records without loading them all into memory. Some sort of paging mechanism. Get the first 1000, do whatever you need to do, get the next 1000, and so on.

Cursors won’t work (I don’t think) because the adapter will try to drain the cursor for you–and likely run out of memory with that many records (depending upon the record size).

Thanks Reamon for a prompt response.
Unfortunately, i don’t have an option to pull records in batches and updating the records that are already processed as I am using a CustomSQL which is extracting records from Oracle Standard tables.
So, I am looking for a solution to fulfill the requirement.

Any other thoughts/advises are appreciated.

You don’t need to update the records that have already been pulled. You need to construct a query that will page through the records a group at a time. The records will need a field that is a unique key for this to work. Search the web for paging techniques/queries. I just did this for a recent project. Works reasonably well.

If you try to read all 850K records at once, I can almost guarantee it will not work.

Another thought that comes to mind: batch jobs such as this really aren’t a fit for implementing in Integration Server. You may want to consider other toolsets.