| Author |
Message
|
| Bichu |
 Posted: Mon Aug 01, 2016 7:16 am Post subject: Event based database integration |
 |
|
Centurion
Joined: 16 Oct 2011
Posts: 124
Location: London
|
Hello,
It will be great if you guys can shred some thoughts on this.
I need to fetch the modified data from a table(INSERT,UPDATE & DELETE) on a daily basis using IIB. But, as per the Infocenter, a new table needs to be created which stores the events information. But my source database doesn't allow a new table to be created.
Any alternate option for this? |
|
| Back to top |
|
 |
| timber |
| Posted: Mon Aug 01, 2016 7:29 am Post subject: |
|
|
Grand Master
Joined: 25 Aug 2015
Posts: 1292
|
| Quote: |
| I need to fetch the modified data from a table(INSERT,UPDATE & DELETE) on a daily basis using IIB |
Several questions spring to mind immediately:
1. Why 'on a daily basis'. Why not process each event in near-real-time when it happens?
2. Picking up INSERTs and UPDATEs from the last 24 hours would be simple, using a SELECT statement (no need for a DatabaseInput node, so no need for an event table either). But if you want to detect DELETEs as well, that sounds like a case for a database trigger that populates a table containing all of the changes...which is starting to sound like an event table.
I think you need to explain the requirements in a bit more detail. And tell us what solutions you have considered. |
|
| Back to top |
|
|
| Bichu |
| Posted: Mon Aug 01, 2016 7:43 am Post subject: |
|
|
Centurion
Joined: 16 Oct 2011
Posts: 124
Location: London
|
Thanks timber
| timber wrote: |
1. Why 'on a daily basis'. Why not process each event in near-real-time when it happens? |
The DB is a highly protected DB with sensitive data. Client wants the interactions to be less and events to be detected once in a day. They don't want to invoke this service on an event basis but on a daily basis.
I have considered writing a SP, but still the same is not allowed on the DB. Then I was left with Event Table which is also not permitted |
|
| Back to top |
|
|
| Vitor |
| Posted: Mon Aug 01, 2016 8:09 am Post subject: |
|
|
Grand High Poobah
Joined: 11 Nov 2005
Posts: 26093
Location: Texas, USA
|
| Bichu wrote: |
| Client wants the interactions to be less and events to be detected once in a day. They don't want to invoke this service on an event basis but on a daily basis. |
This is a false premise on the part of your client. If this database is properly secured, it doesn't matter if it's accessed once a day or once a minute. Likewise, if it's not properly secured then telling you that you can only access it once a day will not prevent an intruder accessing the data at will.
The only way that requirement makes sense on a technical level is if they're going to open the firewalls once a day to allow your job access, and that's so doomed to fail it's not even worth considering.
One option would be a daily batch job that picks up all the changes in the previous period, and have IIB process that. But if the data is that sensitive then your client will probably not be happy with it being sFTP'd all over the place.
I'll let @mqjeff speak on why sFTP is such a poor choice (aside from the security concerns) as he does that so much better than I do.
_________________
Honesty is the best policy.
Insanity is the best defence. |
|
| Back to top |
|
|
| Bichu |
| Posted: Mon Aug 01, 2016 8:14 am Post subject: |
|
|
Centurion
Joined: 16 Oct 2011
Posts: 124
Location: London
|
|
| Back to top |
|
|
| mqjeff |
| Posted: Tue Aug 02, 2016 6:32 am Post subject: |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 17447
|
Any kind of file reading process is a perfect live example of the Halting Problem.
You can only know that the file is ready to read when you know that the program writing the file has halted.
Which you generally can't do.
SFTP is, I believe?, better than FTPS. But it's still moving files around, and that's bad for above reason.
Also it makes data much harder to process in general, since every process that's working with the file has to be prepared to handle the largest file size possible (usually at least 2x the "normal" size).
_________________
chmod -R ugo-wx / |
|
| Back to top |
|
|
| Bichu |
| Posted: Tue Aug 02, 2016 6:47 am Post subject: |
|
|
Centurion
Joined: 16 Oct 2011
Posts: 124
Location: London
|
|
| Back to top |
|
|
|
|
