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 » Oracle Store procedure

Post new topic  Reply to topic
 Oracle Store procedure « View previous topic :: View next topic » 
Author Message
rajmq
PostPosted: Mon Jun 09, 2008 12:38 am    Post subject: Oracle Store procedure Reply with quote

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
View user's profile Send private message
Gaya3
PostPosted: Mon Jun 09, 2008 1:06 am    Post subject: Re: Oracle Store procedure Reply with quote

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
View user's profile Send private message
sri_csee1983
PostPosted: Mon Jun 09, 2008 1:16 am    Post subject: Reply with quote

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
View user's profile Send private message Send e-mail Visit poster's website
srinivas.ganapathi
PostPosted: Mon Jun 09, 2008 1:22 am    Post subject: Reply with quote

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
View user's profile Send private message
Gaya3
PostPosted: Mon Jun 09, 2008 1:27 am    Post subject: Reply with quote

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
View user's profile Send private message
srinivas.ganapathi
PostPosted: Mon Jun 09, 2008 1:37 am    Post subject: Reply with quote

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
View user's profile Send private message
rajmq
PostPosted: Mon Jun 09, 2008 1:51 am    Post subject: Reply with quote

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
View user's profile Send private message
Vitor
PostPosted: Mon Jun 09, 2008 2:10 am    Post subject: Reply with quote

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
View user's profile Send private message
Display posts from previous:   
Post new topic  Reply to topic Page 1 of 1

MQSeries.net Forum Index » WebSphere Message Broker (ACE) Support » Oracle Store procedure
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.