ASG
IBM
Zystems
Cressida
Icon
Netflexity
 
  MQSeries.net
Search  Search       Tech Exchange      Education      Certifications      Library      Info Center      SupportPacs      LinkedIn  Search  Search                                                                   FAQ  FAQ   Usergroups  Usergroups
 
Register  ::  Log in Log in to check your private messages
 
RSS Feed - WebSphere MQ Support RSS Feed - Message Broker Support

MQSeries.net Forum Index » WebSphere Message Broker (ACE) Support » Count number of rows deleted

Post new topic  Reply to topic
 Count number of rows deleted « View previous topic :: View next topic » 
Author Message
mqhelpless
PostPosted: Thu Oct 20, 2005 12:59 am    Post subject: Count number of rows deleted Reply with quote

Apprentice

Joined: 13 Jul 2005
Posts: 33

Hi, I am trying to delete some records in a Message Flow. At the same time, I want to return how many rows were deleted in the single Delete statement. Does anyone knows how to accompolish this?
Back to top
View user's profile Send private message
Prithvi
PostPosted: Thu Oct 20, 2005 1:08 am    Post subject: Reply with quote

Apprentice

Joined: 20 Dec 2004
Posts: 34

1. Use a SELECT statement and place the records you are interested in into an temp variable(environment variables perhaps).
2. Then use the CARDINALITY to check the number of records returned....this will give you the nuber of records to be deleted.
3. Then use a DELETE FROM statement and delete from the database.

Rgds
_________________
Prithvi
Back to top
View user's profile Send private message Yahoo Messenger
mqhelpless
PostPosted: Thu Oct 20, 2005 1:15 am    Post subject: Reply with quote

Apprentice

Joined: 13 Jul 2005
Posts: 33

Thanks for the input. However, my problem lies in a program that is outside of the system. Meaning there is possibility of a parallel access to database. Thus, say I have 2 programs to delete a record from the same database table. Both checks (using a Select statement) for the existence of the record before carrying out a delete.
If the following time sequence occurs :
<----P1 Selects-----P2 Selects-----P1 Deletes-----P2 Deletes------>
Because of the timing issue, both programs P1 and P2 will assume that the record is there and proceeds to delete (there is no record-level locking implemented). Thus, the result will be incorrect as rightfully only one of them should be carrying out the delete of the record.
Back to top
View user's profile Send private message
jefflowrey
PostPosted: Thu Oct 20, 2005 3:30 am    Post subject: Reply with quote

Grand Poobah

Joined: 16 Oct 2002
Posts: 19981

Doesn't the SQLCODE or one of the other SQL state functions return the number of rows deleted after the statement is executed?
_________________
I am *not* the model of the modern major general.
Back to top
View user's profile Send private message
mqhelpless
PostPosted: Thu Oct 20, 2005 5:28 pm    Post subject: Reply with quote

Apprentice

Joined: 13 Jul 2005
Posts: 33

Will it work if my Delete statement is enforced using Passthru?
Back to top
View user's profile Send private message
jefflowrey
PostPosted: Fri Oct 21, 2005 3:21 am    Post subject: Reply with quote

Grand Poobah

Joined: 16 Oct 2002
Posts: 19981

mqhelpless wrote:
Will it work if my Delete statement is enforced using Passthru?


What happens when you try it?
_________________
I am *not* the model of the modern major general.
Back to top
View user's profile Send private message
hopsala
PostPosted: Fri Oct 21, 2005 5:52 am    Post subject: Re: Count number of rows deleted Reply with quote

Guardian

Joined: 24 Sep 2004
Posts: 960

mqhelpless wrote:
Hi, I am trying to delete some records in a Message Flow. At the same time, I want to return how many rows were deleted in the single Delete statement.

Aside from jeff's apt question, this sounds to me like highly dubious design - why (on earth) do you need to know how many rows were deleted?
Back to top
View user's profile Send private message
mqhelpless
PostPosted: Sun Oct 23, 2005 9:26 pm    Post subject: Reply with quote

Apprentice

Joined: 13 Jul 2005
Posts: 33

I need to know if the rows were deleted because there is the possibility of another program accessing the same database record. As time is a constraint, record-level locking implementation is not something feasible at the moment.

Anyway thanks Jeff.
Back to top
View user's profile Send private message
elvis_gn
PostPosted: Sun Oct 23, 2005 10:25 pm    Post subject: Reply with quote

Padawan

Joined: 08 Oct 2004
Posts: 1905
Location: Dubai

Hi mqhelpless,

When u say that P1 and P2 are the two applications deleting the rows, do u mean that P1 is your flow and P2 is some external Java/C application ??

If so then cant you alter design to make one common flow to do the deletion for both the appliactions...??

Regards.
Back to top
View user's profile Send private message Send e-mail
hopsala
PostPosted: Mon Oct 24, 2005 2:44 am    Post subject: Reply with quote

Guardian

Joined: 24 Sep 2004
Posts: 960

mqhelpless wrote:
I need to know if the rows were deleted because there is the possibility of another program accessing the same database record. As time is a constraint, record-level locking implementation is not something feasible at the moment.

What precisely do you mean by "time is a constraint"? Are you sure you have such throughputs that implementing locking will be impossible?

Aside from the fact that I have never encountered such throughputs that disabled the option of using locks, I don't think you really have a choice, except what elvis suggested - to take the delete logic out and put it in a seperate flow; but that flow, of course, can only run in one instance, so you will find yourself stranded on the low-performance shore once more.

Bottom line, if you want higher throughput, implement locks and simply run several flow/appl instances; this, coupled with a bit of WMQ performance tweaks should do the trick.
Back to top
View user's profile Send private message
billybong
PostPosted: Fri Oct 28, 2005 12:00 am    Post subject: Reply with quote

Disciple

Joined: 22 Jul 2005
Posts: 150
Location: Stockholm, Sweden

Another quite ugly way of doing it is to mark the data in the db using a reserved column.
So, it goes someting like this:

* app/flow 1 marks each row through an update with some specific code ie "purgeInit"
* filtering on "purgeInit", selects these rows to the environment
* checks cardinality and finally..
* deletes every row containing "purgeInit".

It's then up to app 2 to leave all rows containing "purgeInit" alone if it wants to delete something.

Anyway, thats 3 db calls instead of using one, so the best way to go is probably to lock the db.
Back to top
View user's profile Send private message Visit poster's website
hopsala
PostPosted: Fri Oct 28, 2005 6:09 am    Post subject: Reply with quote

Guardian

Joined: 24 Sep 2004
Posts: 960

billybong wrote:
Another quite ugly way of doing it is to mark the data in the db using a reserved column.
So, it goes someting like this:

* app/flow 1 marks each row through an update with some specific code ie "purgeInit"
* filtering on "purgeInit", selects these rows to the environment
* checks cardinality and finally..
* deletes every row containing "purgeInit".

It's then up to app 2 to leave all rows containing "purgeInit" alone if it wants to delete something.

I'm afraid what you suggested will not work: appl1 paused / appl2 does SELECT and finds a row with no "purgeinit", appl2 paused / appl1 marks the same row as "purgeinit", appl1 selects and checks cardinality, appl1 paused / appl2 processes row (does not know that purgeinit is marked since the select has finished beforehand) and deletes it, appl2 paused / appl1 goes up and is amazed to see row not there...
You will not be able to write your own lock mechanism (that's what you're trying to do here, really) without having at least 2 reserved columns and about 6 calls - try it and see. Modern DBs have already implemented this for you, and more efficiently than you or I will ever do, so no use in coding this yourself.
Back to top
View user's profile Send private message
billybong
PostPosted: Wed Nov 02, 2005 2:30 am    Post subject: Reply with quote

Disciple

Joined: 22 Jul 2005
Posts: 150
Location: Stockholm, Sweden

you're absolutely right hopsala. I forgot to add that app 2 should reserve the rows as well by using its own column or key phrase.
Anyway, its still a clumsy way of doing it for sure but since db locking wasn't an option I thought I still should suggest it.
Back to top
View user's profile Send private message Visit poster's website
Display posts from previous:   
Post new topic  Reply to topic Page 1 of 1

MQSeries.net Forum Index » WebSphere Message Broker (ACE) Support » Count number of rows deleted
Jump to:  



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
Protected by Anti-Spam ACP
 
 


Theme by Dustin Baccetti
Powered by phpBB © 2001, 2002 phpBB Group

Copyright © MQSeries.net. All rights reserved.