How to handle large data (around 100,000 records) in a file using a file listener.Is there any best option to achieve the same?
My requirement is as follows,
I have a file with large data around 100,00 records in a file. I need to take each record and verify in a DB table.If the record exists in that table,I need to update the record in the DB with the values which I get in the file ,if the record does not exist I need to insert a new record in the DB table.
Do I need to handle this in the listener activity itself or is there any best option to achieve this?
Also If there occurs any error while processing the file, say the listener has processed 1000 records successfully and it failed for 1001 record then how to handle it as we will be getting the file only once..
Thanks & Regards,
Saba Fathima
Accepted Solution
Pegasystems Inc.
IN
Hi Saba, Good Morning!
>>> I have a file with large data around 100,00 records in a file
- Processing performance of large data depends on number of Nodes or infrastructure in case of file listener.
- or multiple file listener rules...
>>> If the record exists in that table,I need to update the record in the DB
- would it be convincing to delete all the data and re-load from the file? instead of writing a validation logic to update the existing records?
- for large set of data - RDB method may be relatively faster than Obj-Delete.
- RDB's scripts are DB platform dependent
- if convincing to delete data first - this action can be separated.
- RDB's scripts are DB platform dependent
- for large set of data - RDB method may be relatively faster than Obj-Delete.
>>> Do I need to handle this in the listener activity itself or is there any best option to achieve this?
- yes listener activity should be handling for most of the use cases.
- options 'duplicate file name', 'empty file processing' are managed at process tab of file listener rule.
>>> Also If there occurs any error while processing the file, say the listener has processed 1000 records successfully and it failed for 1001 record then how to handle it as we will be getting the file only once..
Hi Saba, Good Morning!
>>> I have a file with large data around 100,00 records in a file
- Processing performance of large data depends on number of Nodes or infrastructure in case of file listener.
- or multiple file listener rules...
>>> If the record exists in that table,I need to update the record in the DB
- would it be convincing to delete all the data and re-load from the file? instead of writing a validation logic to update the existing records?
- for large set of data - RDB method may be relatively faster than Obj-Delete.
- RDB's scripts are DB platform dependent
- if convincing to delete data first - this action can be separated.
- RDB's scripts are DB platform dependent
- for large set of data - RDB method may be relatively faster than Obj-Delete.
>>> Do I need to handle this in the listener activity itself or is there any best option to achieve this?
- yes listener activity should be handling for most of the use cases.
- options 'duplicate file name', 'empty file processing' are managed at process tab of file listener rule.
>>> Also If there occurs any error while processing the file, say the listener has processed 1000 records successfully and it failed for 1001 record then how to handle it as we will be getting the file only once..
- recovery options can be configured at error tab of file listener rule.
- on specifying max recovery attempts would again start from beginning of the file and NOT 1001
- the cleanup option under error tab would keep the file with err as file extension.
- would it make sense to have one more file listener to process *.err files...
- otherwise on exception/error ...
- we could capture all the data and save/write the data back to a dedicated error database table
- initiate the data sync process to original table using agent rule to process data...
Please share your thoughts/comments, Thank you!
psahukaru