Maximum Data Retrieval

[Former Member]

Hi

I have to fetch more that 60,000 records from a Standard table of a 3rd party application(SQL DB). Will my interface be able to fetch such large volume of data. If no, then what will the other alternative to fetch large volume of data? Will stored procedures be helpful in this case? Kindly share your experience.

The source adapter is JDBC and target is either a file or proxy

Reg

Shru

[agasthuri_doss]

Hi Shruthi,

Set Pagesize 30(no of records you want to fetch ) Set Pause on.

Will help you to take the records in Batch wise.

Regards

Agasthrui Doss

[Former Member]

Hi,

Thanks for the reply. Is this page size done in the stored procedure or part of the query? I am kinda confused.

-Teresa

[agasthuri_doss]

Hi,

It is better to go for query to my knowledge concern.

Regards

Agasthuri Doss

[agasthuri_doss]

Hi Shruthi,

Similar kind of Scenerio we come acrossed since we where using XSLT mapping it was occupying more memory so we went for batches and is it fine.

Regards

Agasthuri Doss

Message was edited by: Agasthuri Doss Baladandapani

[Former Member]

Hi,

I am seeing your replies for batches. How do you set up the query for batches? Could you explain in more detail?

-Teresa

[Former Member]

Any comments on this issue??

[Former Member]

Hi Agasthuri

Can you let me know on how did you handle the load in batches?.

Thkx

[bhavesh_kantilal]

Shruthi,

What I meant by Batches was to pick data from the Database in batches of some N number of rows per interface run. This way, you would avoid queuing of data in XI .

Regards,

Bhavesh

[Former Member]

Hi Bhavesh,

I guess thats what I had in mind when you mentioned number of rows. But I am aware select and update run in a single DB transaction and if I need to update the same rows selected (WITH UPTO N rows parameter), I guess there might be a problem with the update statement as the where clause of the select and the update statement need to be the same to be handled in the same DB transaction. Or is it possible that I am not aware of.

Please correct me if I am wrong..

-Teresa

[bhavesh_kantilal]

Hi Teresa,

A SQL expert would sure be able to devise an SQL query ( both select and update ) in such a way that only N number of records are selected and updated during the interface run.

I am not an SQL expert, but, am pretty sure that this would be possible for sure.

Regards,

Bhavesh

[bhavesh_kantilal]

Shruti,

Am not sure if JDBC adapter can handle such high loads. One option would be to allow JDBC adapter to pick data in batches.

Regards,

Bhavesh