| |
|
RSS Feed - WebSphere MQ Support
|
RSS Feed - Message Broker Support
|
  |
|
| How to retrieve records from DB table in multiple parts |
« View previous topic :: View next topic » |
| Author |
Message
|
| kumar.shanj |
 Posted: Tue Jun 24, 2014 12:35 am Post subject: How to retrieve records from DB table in multiple parts |
 |
|
Apprentice
Joined: 18 Nov 2010
Posts: 49
|
Requirement is to retrieve set of records from DB table in multiple segments.
Ex : we have 3,00,000 records in DB table and requirement is to retrieve and process the records in segments of 25,000 and generate a 12 files with 25,000 records each.
Two approaches to follow.
1. retrieve from database table in 25k segments and generate file for each retrieval from DB table.
2. retrieve all 3,00,000 records from database and segment in ESQL code and generate file with 25k records each.
Please advise on how to implement above mentioned requirement. |
|
| Back to top |
|
 |
| smdavies99 |
| Posted: Tue Jun 24, 2014 3:28 am Post subject: |
|
|
Jedi Council
Joined: 10 Feb 2003
Posts: 6076
Location: Somewhere over the Rainbow this side of Never-never land.
|
There is a third option.
Read one record at a time from the DB and add that record to the file. Repeat until 25K limit reached and start next file.
you could read a batch of say 500 records and process them as a sort of halfway house between one record or 25,000 at a time.
You are going to have to decide if you are going to potentially use huge amounts of memory (if available) as per your two options or take a bit longer to process everything and use the option I presented.
Read some of the posts in this forum about Execution group abends and how to design a flow to use minimum resources.
These may help you not make simple mistakes when trying to process a potentially very large dataset.
_________________
WMQ User since 1999
MQSI/WBI/WMB/'Thingy' User since 2002
Linux user since 1995
Every time you reinvent the wheel the more square it gets (anon). If in doubt think and investigate before you ask silly questions. |
|
| Back to top |
|
|
| mqjeff |
| Posted: Wed Jun 25, 2014 6:20 am Post subject: |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 17447
|
Option 4: Stop writing batch processes.
Use a DatabaseInput node to monitor the table for changes. Convince the person who is *really* sure they *want* to receive a big giant batch file that we are in fact living in the year 2014, not in the year 1980, and that they may have heard of this thing called "the internet", and they should give you an endpoint that accepts a *single* record, not a *batch* of records.
Then send them a record to that endpoint each time the database has an insert or update event occur. |
|
| Back to top |
|
|
| smdavies99 |
| Posted: Wed Jun 25, 2014 6:55 am Post subject: |
|
|
Jedi Council
Joined: 10 Feb 2003
Posts: 6076
Location: Somewhere over the Rainbow this side of Never-never land.
|
| mqjeff wrote: |
Option 4: Stop writing batch processes.
|
Sadly there are parts of the world that have just graduated from punched cards and paper tape.
I'm involved with a project there where ALL, repeat ALL data transfer to external applications is via FTP. Not even SFTP but plain ordinary FTP with Anonymous connections. The Networking infrastructure is sometimes very iffy and there are cases where only part of the file transfer takes place.
I know that some of the applications we end up talknig to actually have WMQ Connectivity but we are not allowed to use it for so called security reasons.
At least is it XML and not flat file data.
There is a big sign up where I normally sit that says
Hit head hard here at least once an hour or more frequently if you have dangerous thoughts like 'connecting up applications with WMQ'.
Oh yes, there are a large number of 'Daily' and 'Hourly' batch that transfer essentially static data to the endpoints.
Yes I feel like I'm back in the 1960's.
SNA rules ok! 3270 Green Screens are for wimps!
_________________
WMQ User since 1999
MQSI/WBI/WMB/'Thingy' User since 2002
Linux user since 1995
Every time you reinvent the wheel the more square it gets (anon). If in doubt think and investigate before you ask silly questions. |
|
| Back to top |
|
|
|
|
|
| |
|
Page 1 of 1 |
|
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum
|
|
|
|
