| Author |
Message
|
| Partha.Baidya |
 Posted: Wed Jun 01, 2016 11:57 am Post subject: ESB for Batch processing |
 |
|
Voyager
Joined: 05 Nov 2009
Posts: 97
|
We are considering a senarion to use IIBv10 for batch processing.
A frontend application will put some huge records to DB2 database. This records has to be transfered as file to a Mainframe system using IIB/FTP.
The application can not create the file so it will dump the data to a database staging table.
IIB has to read the data from the staging table and create a file. This file will be transfered using FTP protocol to the Mainframe system.
So there are two tasks at runtime for IIB
i) reading the data from database
ii) translating the database record to a file.
As this a batch processing the file size can be upto 1 GB.
Is this a recommended way of using IIB/ESB? |
|
| Back to top |
|
 |
| fjb_saper |
| Posted: Wed Jun 01, 2016 1:35 pm Post subject: Re: ESB for Batch processing |
|
|
Grand High Poobah
Joined: 18 Nov 2003
Posts: 20756
Location: LI,NY
|
| Partha.Baidya wrote: |
We are considering a senarion to use IIBv10 for batch processing.
A frontend application will put some huge records to DB2 database. This records has to be transfered as file to a Mainframe system using IIB/FTP.
The application can not create the file so it will dump the data to a database staging table.
IIB has to read the data from the staging table and create a file. This file will be transfered using FTP protocol to the Mainframe system.
So there are two tasks at runtime for IIB
i) reading the data from database
ii) translating the database record to a file.
As this a batch processing the file size can be upto 1 GB.
Is this a recommended way of using IIB/ESB? |
I don't think it would be.
Define Huge Records. So what is the max size of a single record?
_________________
MQ & Broker admin |
|
| Back to top |
|
|
| Partha.Baidya |
| Posted: Wed Jun 01, 2016 2:22 pm Post subject: |
|
|
Voyager
Joined: 05 Nov 2009
Posts: 97
|
Each record is 3kb in size and we could have 276K records for a monthly file.
So the entire data will be around 900MB.
When we read from DB we need to read these data and create file out of it. |
|
| Back to top |
|
|
| mqjeff |
| Posted: Thu Jun 02, 2016 4:11 am Post subject: |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 17447
|
So you would use a DatabaseInput node and a FileOutput node.
_________________
chmod -R ugo-wx / |
|
| Back to top |
|
|
| Vitor |
| Posted: Thu Jun 02, 2016 4:35 am Post subject: |
|
|
Grand High Poobah
Joined: 11 Nov 2005
Posts: 26093
Location: Texas, USA
|
| Partha.Baidya wrote: |
| Each record is 3kb in size |
That's not huge.
| Partha.Baidya wrote: |
| and we could have 276K records for a monthly file. |
How do you know (from a design perspective) you've read all the records needed from the database?
If you're planning to return all 276K records to IIB as a single result set that's a) not a recommended use of IIB and b) you're going to need a lot of memory in the EG.
If you're reading them one at a time (as my most worthy associate proposes) then IIB is more feasible, but personally I'd still investigate other tools.
_________________
Honesty is the best policy.
Insanity is the best defence. |
|
| Back to top |
|
|
| Partha.Baidya |
| Posted: Thu Jun 02, 2016 6:46 am Post subject: |
|
|
Voyager
Joined: 05 Nov 2009
Posts: 97
|
@mqjeff, We will use Database node to read the record from Staging table and a Compute node to transform the data to the file structure. Then a File Output node to write to a FTP location.
| Vitor wrote: |
| How do you know (from a design perspective) you've read all the records needed from the database? |
We will store the end of day data from the front end application to a staging table. The data will be deleted mid night once IIB reads the data from table. We can count no of rows before IIB starts reading the table. So we know that the required data has been read.
| Vitor wrote: |
If you're planning to return all 276K records to IIB as a single result set that's a) not a recommended use of IIB and b) you're going to need a lot of memory in the EG.
If you're reading them one at a time (as my most worthy associate proposes) then IIB is more feasible, but personally I'd still investigate other tools. |
I would prefer to read the record once at a time. But I do not find that option is available in IIB while we are reading from database. Like we have this option for file processing.
As you said the processing will require lot of memory for 900MB data as well. The single thread will read the data from DB and transform to a file using Compute node. |
|
| Back to top |
|
|
| mqjeff |
| Posted: Thu Jun 02, 2016 6:48 am Post subject: |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 17447
|
I didn't say use the Database node. I said the DatabaseInput node.
_________________
chmod -R ugo-wx / |
|
| Back to top |
|
|
| Vitor |
| Posted: Thu Jun 02, 2016 6:57 am Post subject: |
|
|
Grand High Poobah
Joined: 11 Nov 2005
Posts: 26093
Location: Texas, USA
|
| Partha.Baidya wrote: |
| We will store the end of day data from the front end application to a staging table. The data will be deleted mid night once IIB reads the data from table. We can count no of rows before IIB starts reading the table. So we know that the required data has been read. |
So you're using an index to go through the result set. This is going to be really, really slow in IIB.
| Partha.Baidya wrote: |
| I would prefer to read the record once at a time. But I do not find that option is available in IIB while we are reading from database. Like we have this option for file processing. |
Because a database has no concept of "end of file" - there's no "end of table" condition returned by the database software so this would be true for any platform or application solution.
| Partha.Baidya wrote: |
| The single thread will read the data from DB and transform to a file using Compute node. |
So your IIB solution will be memory intensive, programmatically slow and can't be scaled because the design relies on you running single threaded.
| Partha.Baidya wrote: |
| Is this a recommended way of using IIB/ESB? |
No it isn't. Not in any sense.
_________________
Honesty is the best policy.
Insanity is the best defence. |
|
| Back to top |
|
|
| Partha.Baidya |
| Posted: Thu Jun 02, 2016 7:16 am Post subject: |
|
|
Voyager
Joined: 05 Nov 2009
Posts: 97
|
@Vitor
Yes, this will be slow application.
Could you please suggest any other way of implementing this problem in IIB? |
|
| Back to top |
|
|
| mqjeff |
| Posted: Thu Jun 02, 2016 7:20 am Post subject: |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 17447
|
Use the DatabaseInput node to process each row individually.
_________________
chmod -R ugo-wx / |
|
| Back to top |
|
|
| Vitor |
| Posted: Thu Jun 02, 2016 7:22 am Post subject: |
|
|
Grand High Poobah
Joined: 11 Nov 2005
Posts: 26093
Location: Texas, USA
|
| Partha.Baidya wrote: |
| Could you please suggest any other way of implementing this problem in IIB? |
You asked if your design was a recommended use of IIB.
It's not.
You need a different ETL solution.
_________________
Honesty is the best policy.
Insanity is the best defence. |
|
| Back to top |
|
|
| Vitor |
| Posted: Thu Jun 02, 2016 7:22 am Post subject: |
|
|
Grand High Poobah
Joined: 11 Nov 2005
Posts: 26093
Location: Texas, USA
|
| mqjeff wrote: |
| Use the DatabaseInput node to process each row individually. |
The OP is wondering how to detect at what point they finish the file.
_________________
Honesty is the best policy.
Insanity is the best defence. |
|
| Back to top |
|
|
| mqjeff |
| Posted: Thu Jun 02, 2016 7:39 am Post subject: |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 17447
|
| Vitor wrote: |
| mqjeff wrote: |
| Use the DatabaseInput node to process each row individually. |
The OP is wondering how to detect at what point they finish the file. |
Either when the last row is read, or when the business data indicates it's the end of file.
"The last row is read" is a condition discoverable from how the DatabaseInput node works.
_________________
chmod -R ugo-wx / |
|
| Back to top |
|
|
| Partha.Baidya |
| Posted: Thu Jun 02, 2016 7:49 am Post subject: |
|
|
Voyager
Joined: 05 Nov 2009
Posts: 97
|
@mqjef
"The last row is read" - how to configure this option in Database Input node.
So if we are reading the database for each row insert, then this flow will be invoked several times as there will be continuous inserts by the backend application.
And we can write the file in append mode.
Will there be any locking issue by different instances of the flow? |
|
| Back to top |
|
|
| Vitor |
| Posted: Thu Jun 02, 2016 8:01 am Post subject: |
|
|
Grand High Poobah
Joined: 11 Nov 2005
Posts: 26093
Location: Texas, USA
|
| mqjeff wrote: |
| Either when the last row is read, or when the business data indicates it's the end of file. |
The business data doesn't seem to indicate this. Lack of more data seems to be the "delimiter"
| mqjeff wrote: |
| "The last row is read" is a condition discoverable from how the DatabaseInput node works. |
Pseudo - code?
_________________
Honesty is the best policy.
Insanity is the best defence. |
|
| Back to top |
|
|
|
|
