| Author |
Message
|
| mgk |
 Posted: Tue Apr 21, 2009 10:41 am Post subject: |
 |
|
Padawan
Joined: 31 Jul 2003
Posts: 1647
|
But what was wrong with:
| Code: |
| DELETE FROM Database.TESTDB.MySchema.TEST AS A WHERE A.DATETIME < (CURRENT_TIMESTAMP - INTERVAL '1' DAY); |
?
PASSTHRU will work, but it is a good idea to only use it when you find something WMB can't do directly. In this case, it is not needed...
Kind Regards,
MGK
_________________
MGK
The postings I make on this site are my own and don't necessarily represent IBM's positions, strategies or opinions. |
|
| Back to top |
|
 |
| Vgowda |
| Posted: Tue Apr 21, 2009 10:27 pm Post subject: |
|
|
Acolyte
Joined: 11 Dec 2007
Posts: 61
Location: Bengaluru
|
yes Vitor, i decided to use a query in a flow (Compute node). 
Hey MGK, watever the code was there it was working fine but it was not displaying the value in the debug node that was the issue. Without passthru statement also the delete statement works which u have posted.
Anyways thanks for that guys 
_________________
Regards
Vinay |
|
| Back to top |
|
|
| Vitor |
| Posted: Tue Apr 21, 2009 11:09 pm Post subject: |
|
|
Grand High Poobah
Joined: 11 Nov 2005
Posts: 26093
Location: Texas, USA
|
| Vgowda wrote: |
yes Vitor, i decided to use a query in a flow (Compute node).
|
I still fail to see the value you're getting for the overhead, but if you're happy.....
_________________
Honesty is the best policy.
Insanity is the best defence. |
|
| Back to top |
|
|
| WMBDEV1 |
| Posted: Wed Apr 22, 2009 12:43 am Post subject: |
|
|
Sentinel
Joined: 05 Mar 2009
Posts: 888
Location: UK
|
| Vitor wrote: |
| Vgowda wrote: |
yes Vitor, i decided to use a query in a flow (Compute node).
|
I still fail to see the value you're getting for the overhead, but if you're happy..... |
I strongly agree with Vitor here, it sounds to me like you may have built an expensive scheduling tool! |
|
| Back to top |
|
|
| nab054371 |
| Posted: Wed Apr 22, 2009 8:34 am Post subject: |
|
|
Disciple
Joined: 15 Nov 2006
Posts: 173
|
I am not quite sure if it would be expensve in this case,since the code activity needs executing a database delete anyway.Had the delete not been required, then I agree with you guys that PASSTHRU would be redundant.
In fact computing the timestamp in broker and thene executing the delete might be more expensive if anything. |
|
| Back to top |
|
|
| WMBDEV1 |
| Posted: Wed Apr 22, 2009 8:52 am Post subject: |
|
|
Sentinel
Joined: 05 Mar 2009
Posts: 888
Location: UK
|
| I meant using the broker as a scheduler to do such a noddy task was expensive, in terms of $$$$ |
|
| Back to top |
|
|
| nab054371 |
| Posted: Wed Apr 22, 2009 9:29 am Post subject: |
|
|
Disciple
Joined: 15 Nov 2006
Posts: 173
|
My reply was meant for Vitor's post.
Again this might just be something tiny in the grand schema of things he might have going on in WMB. And with this he wont have to pay extra $$$ since WMB licenses (PVU) is almost based off of CPU configuration. |
|
| Back to top |
|
|
| Vitor |
| Posted: Wed Apr 22, 2009 9:44 am Post subject: |
|
|
Grand High Poobah
Joined: 11 Nov 2005
Posts: 26093
Location: Texas, USA
|
| nab054371 wrote: |
| I am not quite sure if it would be expensve in this case |
Not expensive? Aside from the license costs of WMB against cron, think of the amount of processor & memory overhead involved in spinning up a broker & execution group.
_________________
Honesty is the best policy.
Insanity is the best defence. |
|
| Back to top |
|
|
| Vitor |
| Posted: Wed Apr 22, 2009 9:56 am Post subject: |
|
|
Grand High Poobah
Joined: 11 Nov 2005
Posts: 26093
Location: Texas, USA
|
| nab054371 wrote: |
| Again this might just be something tiny in the grand schema of things he might have going on in WMB. |
My point was that this is tiny in the grans scheme of things that anyone hsa going on. Nor have I at any time claimed that it's not a workable solution. Simply that it's a sledgehammer (tied to an anvil) to crack a nut. The nut will still crack.
Also (and this might be related to this grand scheme I live in) housekeeping queries like deleting old rows on a dbase tends to be much easier to implement (because they're easier to QA & pass audit) than a flow with all it's attendent impedimentia.
Consider also, the flow containing the passthru will still need to be initiated in some way: message to a queue, timer node or an mqsistart command. So this doesn't solve all the problems, especially if you end up with a cron job that triggers the flow......
| nab054371 wrote: |
| And with this he wont have to pay extra $$$ since WMB licenses (PVU) is almost based off of CPU configuration. |
There's no extra $$$ involved in any of this. The abilty to run the query comes with the dbase solution, scheduling comes with the OS. So using a query or using WMB involves no extra cost (unless you need extra hardware to run this new flow as well as all the other stuff broker is doing).
But it comes down to individual requirements. If the poster's happy then so be it. All is well.
Unless I'm brought onto the site in question. 
_________________
Honesty is the best policy.
Insanity is the best defence. |
|
| Back to top |
|
|
| nab054371 |
| Posted: Wed Apr 22, 2009 10:30 am Post subject: |
|
|
Disciple
Joined: 15 Nov 2006
Posts: 173
|
Well im almost certain he is taking this route just cause he has real time messages that would trigger DB delete activity,it doesnt make any sense otherwise. And again that would make WMB an ideal candiate to initate the DB delete rather than croning a script or using DB triggers.
As for the amount of CPU and memory,that would come with both DB solution or croning script,so this would not be any different. |
|
| Back to top |
|
|
| Vitor |
| Posted: Wed Apr 22, 2009 10:39 am Post subject: |
|
|
Grand High Poobah
Joined: 11 Nov 2005
Posts: 26093
Location: Texas, USA
|
| nab054371 wrote: |
| As for the amount of CPU and memory,that would come with both DB solution or croning script,so this would not be any different. |
If a cron job running a batch SQL interpreter uses the same amount of CPU & memory as a broker with an execution group, you have a very strange OS with a very strange cron!
_________________
Honesty is the best policy.
Insanity is the best defence. |
|
| Back to top |
|
|
| nab054371 |
| Posted: Wed Apr 22, 2009 10:51 am Post subject: |
|
|
Disciple
Joined: 15 Nov 2006
Posts: 173
|
| Well i dont see myself saying "SAME AMOUNT OF CPU AND MEMORY" do u? and BTW the tradeoff is between leveraging features offered by WMB(when it seems to be the best otion from proposed solutions) and hardware, when hardware comes pretty cheap..hmm i might go with what WMB has to offer! |
|
| Back to top |
|
|
| Vitor |
| Posted: Wed Apr 22, 2009 11:13 am Post subject: |
|
|
Grand High Poobah
Joined: 11 Nov 2005
Posts: 26093
Location: Texas, USA
|
| nab054371 wrote: |
| Well i dont see myself saying "SAME AMOUNT OF CPU AND MEMORY" do u? |
I was confused by your use of the phrase "would not be any different"
| nab054371 wrote: |
| leveraging features offered by WMB |
If the poster was leveraging any of the features offered by WMB (my original question refers) then indeed, which is why I asked. In this instance, the poster is not even leveraging WMB's date manipulation facilities.
| nab054371 wrote: |
| when hardware comes pretty cheap |
You do live in a different world. I wish the site management thought hardware was pretty cheap; I could use a few more environments.
I envy you your budget flexibility.
_________________
Honesty is the best policy.
Insanity is the best defence. |
|
| Back to top |
|
|
| WMBDEV1 |
| Posted: Wed Apr 22, 2009 11:50 am Post subject: |
|
|
Sentinel
Joined: 05 Mar 2009
Posts: 888
Location: UK
|
| nab054371 wrote: |
| Well im almost certain he is taking this route just cause he has real time messages that would trigger DB delete activity |
How are you so certain? What do you know that we dont?
If this is the case then its only because its been specifically designed this way (I cant see any off the shelf products being written this way) and all im trying to say is that it may be overkill for the solution (as also pointed out by Vitor).
Although these noddy tasks seem free as you have the broker already, when you implement lots of noddy tasks you may have to increase scaling in the future increasing your PVUs which aint cheap; but more likely this solution will now need an mqsi admin to support rather than a DBA which will more than likely be a more expensive resource!
If he is using the timeout nodes then he may also lose some flexibility to alter the schedule (without redeploying the flow). |
|
| Back to top |
|
|
| nab054371 |
| Posted: Wed Apr 22, 2009 10:11 pm Post subject: |
|
|
Disciple
Joined: 15 Nov 2006
Posts: 173
|
Vitor-
In this case since the DB delete activity would be triggered by a message driven event, I would say WMB would be the best option.As for the world I live in, RAM comes for couple of hundred dollars and CPU for maybe couple more. I dont see this solution having much of a performance impact as against the cron or DB solution, since DB triggers would mean Oracle resource manager pooling anyway. Not to mention that triggers are not recommened by oracle simply cause they have adverse impact on oracle table inserts or deletes unless done over the high water mark.So with that in mind the value that u get for some extra RAM and CPU is much more from this solution. Again had it not been for message driven event,I would have preferred cron or DB tigger solution.
WMBDEV1-
I have exchanged emails with the guy reporting this problem trying to understand his design and no he is not using timeout nodes. Again I am not sure why would this be a noddy task specially when its a Message driven event.I would rather think WMB would be the best choice for this activity. As for needing an MQSI admin, PASSTHRU and DB deletes wont need an MQSI admin! As for additional PVU's, if u need them to accomplish a task that best fits WMB solution, then so be it. |
|
| Back to top |
|
|
|
|
