| Author |
Message
|
| shalabh1976 |
 Posted: Wed Sep 17, 2008 3:44 am Post subject: Multiple SQL statements in a single line of ESQL |
 |
|
Partisan
Joined: 18 Jul 2002
Posts: 381
Location: Gurgaon, India
|
Hi,
I have 10-15 SQL statements that are independant of each other. However if I were to run them from ESQL this would involve 10-15 round trips to the database. Is there any way I can combine these statements and fire a single statement so that there is only 1 round trip?
Thanks and Regards,
Shalabh
_________________
Shalabh
IBM Cert. WMB V6.0
IBM Cert. MQ V5.3 App. Prog.
IBM Cert. DB2 9 DB Associate |
|
| Back to top |
|
 |
| mqjeff |
| Posted: Wed Sep 17, 2008 3:50 am Post subject: |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 17447
|
| You can do nested selects. |
|
| Back to top |
|
|
| shalabh1976 |
| Posted: Thu Sep 18, 2008 1:21 am Post subject: |
|
|
Partisan
Joined: 18 Jul 2002
Posts: 381
Location: Gurgaon, India
|
We have many DELETES and I have no idea how to nest them.
I did some search on the net and I found the command SQLExecDirect for SQL Server 2005(which is the underlying database). However I have no idea as to use it from ESQL.
_________________
Shalabh
IBM Cert. WMB V6.0
IBM Cert. MQ V5.3 App. Prog.
IBM Cert. DB2 9 DB Associate |
|
| Back to top |
|
|
| fjb_saper |
| Posted: Thu Sep 18, 2008 2:01 am Post subject: |
|
|
Grand High Poobah
Joined: 18 Nov 2003
Posts: 20756
Location: LI,NY
|
Think stored procedure. 
_________________
MQ & Broker admin |
|
| Back to top |
|
|
| elvis_gn |
| Posted: Thu Sep 18, 2008 7:57 am Post subject: |
|
|
Padawan
Joined: 08 Oct 2004
Posts: 1905
Location: Dubai
|
Hi all,
| mqjeff wrote: |
| You can do nested selects. |
Maybe not possible since
| shalabh1976 wrote: |
| I have 10-15 SQL statements that are independant of each other |
But stored procedure should be the first step as fjp_saper suggests...Make one call, and let the procedure to all the deletes...
Regards. |
|
| Back to top |
|
|
| mqjeff |
| Posted: Thu Sep 18, 2008 8:39 am Post subject: |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 17447
|
The independance of the statements does not interfere with the ability to select the total set of all results from each statement.
I am not sure if the fact that the statements are DELETEs will interfere with the ability to select the total set of all results from each statement.
A stored procedure is good. A passthru full of ";" may be sufficient. |
|
| Back to top |
|
|
| elvis_gn |
| Posted: Thu Sep 18, 2008 9:55 am Post subject: |
|
|
Padawan
Joined: 08 Oct 2004
Posts: 1905
Location: Dubai
|
Hi mqjeff,
There is a big difference in what "can be done" and what "should be done"...
In this example...
1. You CAN put all the SQL statements into one PASSTHRU and delete the database records.
2. You CAN nest (or better join the queries) as something common in the input must be the parameter for all deletion.
But you should not because...
1. Who does the database administator question if every developer of every possible team is firing deletes on his database...it becomes impossible to track and maintain data consistency, that is why stored procedures...doing selects or even inserts/updates is ok, but deleting data is a little risky when done all over the place.
2. 15 selects(forget even deletes) in one passthru  What kind of error handling will you provide for such a statement, just throw it as a heap to the MQ admin ? Tracing errors is going to be tougher.
3. What performance do you gurantee ? 15 calls is a little too much even if called from 1 passthru...all 15 in 1 passthru does not mean it takes the time of 1 query, does it ? A stored procedure will atleast do better...
4. Long running SELECT queries sometimes make the flow look inconsitent and the JVM to crash, then just imagine if the DELETE is waiting for other previous DELETES to be committed ? (ignoring that the database handles it to a good degree)...and then you have 15 of them too together...you could find the JVM to shoot and no response being returned from the database call...and as of v6.0, i have not heard of database call timeouts(not sure if its in v6.1)...
For these reasons, don't do something just coz it can be done that way.
Regards. |
|
| Back to top |
|
|
| mqjeff |
| Posted: Thu Sep 18, 2008 10:03 am Post subject: |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 17447
|
| Oh sure. I never said any of these were *good* ideas. |
|
| Back to top |
|
|
|
|
