| Author |
Message
|
| rajmq |
 Posted: Mon Jun 09, 2008 12:38 am Post subject: Oracle Store procedure |
 |
|
Partisan
Joined: 29 Sep 2002
Posts: 331
Location: USA
|
All,
Just I want to know some of details for calling the store procedure from broker esql !!
Basically what is the difference between inserting the data using normal insert statement & calling store procedure from flow for inserting the data...?
if I use store procedure, do i get more or less performance ?
_________________
IBM Certified System Administrator - WebSphere MQ V6.0
IBM Certified System Administrator - WebSphere Business Integration Message Broker V6.0 |
|
| Back to top |
|
 |
| Gaya3 |
| Posted: Mon Jun 09, 2008 1:06 am Post subject: Re: Oracle Store procedure |
|
|
Jedi
Joined: 12 Sep 2006
Posts: 2493
Location: Boston, US
|
| rajmq wrote: |
Basically what is the difference between inserting the data using normal insert statement & calling store procedure from flow for inserting the data...?
if I use store procedure, do i get more or less performance ?
|
One handing over the business logic of inserting or updating the database to Back end.
other one, handling all those from Broker side.
frequent Database handling is always degrade performance.
it depends upon the logic how you are performing with external tools.
if its having sync transform then need to be optimized.
Regards
Gayathri
_________________
Regards
Gayathri
-----------------------------------------------
Do Something Before you Die |
|
| Back to top |
|
|
| sri_csee1983 |
| Posted: Mon Jun 09, 2008 1:16 am Post subject: |
|
|
Centurion
Joined: 25 Mar 2008
Posts: 125
Location: Chennai,India
|
AFAIK, it is common for all database, that if your DML query is larger in size and commonly and frequently used one, it is always better to go with stored procedures instead of SQL Statement. Reason being it is very common that SP are set of precompiled statements, rather ur sql statements need to be compiled each and every time they run. This is up to my knowledge. 
_________________
With Cheers,
Sri |
|
| Back to top |
|
|
| srinivas.ganapathi |
| Posted: Mon Jun 09, 2008 1:22 am Post subject: |
|
|
Newbie
Joined: 09 Jun 2008
Posts: 7
|
Executing an sql statement from MB and calling a stored procedure are entirely different . If you are excecuting an sql statement then each time the message flows needs to be restarted . If we are calling a stored procedure there is no need to restart the message flows .. we are not directly listening to the database here .
Here is how we call a stored procedure from MB ...
CALL storedProc(resourceId ,msoCheck,errCode,errText,Environment.Variables.Result1.ResultSet1[],Environment.Variables.Result3.ResultSet3[]) IN Database.{DBschema_User};
Declaring a stored procedure 
CREATE PROCEDURE storedProc (IN P1 FLOAT ,OUT P2 CHARACTER,OUT P4 INTEGER,OUT P5 CHARACTER)
LANGUAGE DATABASE
DYNAMIC RESULT SETS 2
EXTERNAL NAME "scott.CHECK_DN_FOR_MSO_proc";
This storedproc returns 2 result sets that are captured in the environment variables ...
scott is the userId for the database . This can be overridden by using a UDP as well ..... |
|
| Back to top |
|
|
| Gaya3 |
| Posted: Mon Jun 09, 2008 1:27 am Post subject: |
|
|
Jedi
Joined: 12 Sep 2006
Posts: 2493
Location: Boston, US
|
| srinivas.ganapathi wrote: |
If you are excecuting an sql statement then each time the message flows needs to be restarted . If we are calling a stored procedure there is no need to restart the message flows .. we are not directly listening to the database here .
|
Please clarify what do you meant here, by restarting the messages flows.
as start and stop of message flows got a different context.
When ever any messages comes, the ESQL will be called and perform the operations.
There is no need to start or stop the message flow for doing the same.
Regards
Gayathri
_________________
Regards
Gayathri
-----------------------------------------------
Do Something Before you Die |
|
| Back to top |
|
|
| srinivas.ganapathi |
| Posted: Mon Jun 09, 2008 1:37 am Post subject: |
|
|
Newbie
Joined: 09 Jun 2008
Posts: 7
|
| Consider a case in which we are using sql stmts to retrieve the data from a DB . Let us suppose that an external application is constantly updating the Database . If we are using the sql stmts directly in MB then we cannot get the latest data that is updated in the DB . We need to stop and start the message flows so that we cache the new data . If we use a stored proc for the same req .. the stored procedure constantly updates the data from the DB and there is no need to stop and start the flows to update the cache .... |
|
| Back to top |
|
|
| rajmq |
| Posted: Mon Jun 09, 2008 1:51 am Post subject: |
|
|
Partisan
Joined: 29 Sep 2002
Posts: 331
Location: USA
|
Thanks for all..
Q1. if you have mutiple records in the xml, it is has be inserted in to two different records but how it is possible using store procedure ?
Q2. if you call the stroeprocedure from esql, how the xml tag values are exactly getting pouplated in the table ?
_________________
IBM Certified System Administrator - WebSphere MQ V6.0
IBM Certified System Administrator - WebSphere Business Integration Message Broker V6.0 |
|
| Back to top |
|
|
| Vitor |
| Posted: Mon Jun 09, 2008 2:10 am Post subject: |
|
|
Grand High Poobah
Joined: 11 Nov 2005
Posts: 26093
Location: Texas, USA
|
| srinivas.ganapathi wrote: |
| Consider a case in which we are using sql stmts to retrieve the data from a DB . Let us suppose that an external application is constantly updating the Database . If we are using the sql stmts directly in MB then we cannot get the latest data that is updated in the DB . We need to stop and start the message flows so that we cache the new data . If we use a stored proc for the same req .. the stored procedure constantly updates the data from the DB and there is no need to stop and start the flows to update the cache .... |
Consider a case where Application A constantly updates a database with rows for Application B to read by means of a SELECT statement. Would you expect that you'd need to restart Application B every time to ensure you got the latest data? I think not.
The only difference between SQL and a stored proc is that the stored proc runs inside the server engine and is often precompiled to save parsing time. I can think of no legistimate situation where a piece of SQL run by an external process and the same SQL run by a stored proc would produce different results.
If you're issuing SELECT from inside MB (which doesn't cache anything) and you're not getting the latest data or you're getting different results from the stored proc there are a number of possibilities:
- The stored proc doesn't do what you think it does
- The SQL doesn't do what you think it does
- you have a weird and badly thought out locking strategy on your database
- you have a weird and badly thought out caching strategy on your database
- you need to raise a PMR with IBM regarding the broker's SQL parser
- you need to raise the equivalent wth the database vendor regarding their result set handling
- you need to pull out whatever home-brew cache you've built into the message flow
_________________
Honesty is the best policy.
Insanity is the best defence. |
|
| Back to top |
|
|
|
|
