| |
|
RSS Feed - WebSphere MQ Support
|
RSS Feed - Message Broker Support
|
  |
|
| Rising number of open cursors - PASSTHRU dynamic SQL |
« View previous topic :: View next topic » |
| Author |
Message
|
| aspre1b |
 Posted: Mon Apr 21, 2008 3:24 am Post subject: Rising number of open cursors - PASSTHRU dynamic SQL |
 |
|
Voyager
Joined: 05 Jul 2007
Posts: 78
Location: Coventry, UK
|
Wondering of anyone has come across a problem with rising number of open cursors? I've been trawling the net for answers and so far none have been forthcoming.
We are performing Updates to an Oracle 9i database using a PASSTHRU statement. Instead of using prepared statements we have coded the value into the statement, which we dynamically build up depending on the request parameters. We have two connections to the database (confirmed by SQL statement) Each connection is slowly increasing the number of open cursors to the database.
I know a coding change would enable us to circumvent this problem but as we have gone through OAT, UAT and Performance Test this would incur great expense. Is there a way to configure the broker to close open cursors without performing an mqsireload? Or is there a way to timeout the open cursors from the broker say if they haven’t been used for over an hour?
Ultimately a code fix will be part of a future maintenance release.
Message Broker 5 CSD 12
Oracle 9i
Regards
Brendon |
|
| Back to top |
|
 |
| mgk |
| Posted: Mon Apr 21, 2008 3:34 am Post subject: |
|
|
Padawan
Joined: 31 Jul 2003
Posts: 1642
|
Hi,
As you seems to have noticed, coding the values directly into a PASSTHRU is a bad idea; you should use parameter markers (?) in the string you give to PASSTHRU so that it does not change each time you use it.
If you cannot do this now, then you should open a PMR as there is an environment variable (which I forget the name of) which will force statements to be removed from the internal cache (so closing the open cursors) once a message is committed or rolled back. This may reduce your performance of course, but it will stop your open cursor problem.
Service will be able to tell you the variable name...
Regards,
_________________
MGK
The postings I make on this site are my own and don't necessarily represent IBM's positions, strategies or opinions. |
|
| Back to top |
|
|
| aspre1b |
| Posted: Mon Apr 21, 2008 3:55 am Post subject: |
|
|
Voyager
Joined: 05 Jul 2007
Posts: 78
Location: Coventry, UK
|
Thanks for the quick reply mgk - I have raised a PMR for this issue.
I just hope I can amend the setting for a single execution group and not broker wide as that would be unacceptable.
 |
|
| Back to top |
|
|
| CHF |
| Posted: Mon May 19, 2008 3:01 pm Post subject: |
|
|
Master
Joined: 16 Dec 2003
Posts: 297
|
I had the same issue but resolved it by passing the reference. But I am still curious to know about this variable. Did anyone know what this variable is?
_________________
CHF  |
|
| Back to top |
|
|
| aspre1b |
| Posted: Tue May 27, 2008 3:12 am Post subject: |
|
|
Voyager
Joined: 05 Jul 2007
Posts: 78
Location: Coventry, UK
|
See below for information regarding the use of MQSI_EMPTY_DB_CACHE. However it was a non starter for us as we have many broker projects in many execution groups running on a single broker and the setting was global, which was unacceptable for us.
I had to produce a coding fix in the end, which I was reluctant to do as we had already gone through UAT and OAT.
-------------------------------------------------------------
http://publib.boulder.ibm.com/infocenter/wbihelp/v6rxmx/index.jsp?topic=/com.ibm.etools.mft.fp8.doc/au16530_.htm
"The DataFlowEngine terminates while processing messages" subsection.
This discusses the use of MQSI_EMPTY_DB_CACHE and how this can be used
to stop the caching of database statements on message flow threads.
In these sections there is no explicit mention of cursors. An open
cursor is maintained for each cached database statement that has been
created by a message flow.
So please stop the Broker and set the environment variable
MQSI_EMPTY_DB_CACHE=Y
and export it for the mqsiserviceuserid of the broker.
Then restart the broker and please check the usage of the open cursors. |
|
| Back to top |
|
|
|
|
|
| |
|
Page 1 of 1 |
|
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
|
|
|
|
