| Author |
Message
|
| mqhelpless |
 Posted: Thu Oct 20, 2005 12:59 am Post subject: Count number of rows deleted |
 |
|
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 |
|
 |
| Prithvi |
| Posted: Thu Oct 20, 2005 1:08 am Post subject: |
|
|
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 |
|
|
| mqhelpless |
| Posted: Thu Oct 20, 2005 1:15 am Post subject: |
|
|
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 |
|
|
| jefflowrey |
| Posted: Thu Oct 20, 2005 3:30 am Post subject: |
|
|
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 |
|
|
| mqhelpless |
| Posted: Thu Oct 20, 2005 5:28 pm Post subject: |
|
|
Apprentice
Joined: 13 Jul 2005
Posts: 33
|
| Will it work if my Delete statement is enforced using Passthru? |
|
| Back to top |
|
|
| jefflowrey |
| Posted: Fri Oct 21, 2005 3:21 am Post subject: |
|
|
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 |
|
|
| hopsala |
| Posted: Fri Oct 21, 2005 5:52 am Post subject: Re: Count number of rows deleted |
|
|
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 |
|
|
| mqhelpless |
| Posted: Sun Oct 23, 2005 9:26 pm Post subject: |
|
|
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 |
|
|
| elvis_gn |
| Posted: Sun Oct 23, 2005 10:25 pm Post subject: |
|
|
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 |
|
|
| hopsala |
| Posted: Mon Oct 24, 2005 2:44 am Post subject: |
|
|
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 |
|
|
| billybong |
| Posted: Fri Oct 28, 2005 12:00 am Post subject: |
|
|
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 |
|
|
| hopsala |
| Posted: Fri Oct 28, 2005 6:09 am Post subject: |
|
|
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 |
|
|
| billybong |
| Posted: Wed Nov 02, 2005 2:30 am Post subject: |
|
|
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 |
|
|
|
|
