| |
|
RSS Feed - WebSphere MQ Support
|
RSS Feed - Message Broker Support
|
  |
|
| Open cursor issue. |
« View previous topic :: View next topic » |
| Author |
Message
|
| Vargheese thomas |
 Posted: Wed Jul 14, 2004 8:38 am Post subject: Open cursor issue. |
 |
|
Novice
Joined: 16 May 2003
Posts: 13
|
Hi Guys,
I have an issue. Iam using a custom node that queries (all select queries) the database.
Iam executing the query as follows:
=======================================
MbSQLStatement stmt = node.createSQLStatement(....)
stmt.execute(assembly);
stmt.clearStatement();
=======================================
Java documentation says that the clearStatement() will release all the databse resources but it does not seem to do so. The cursors created by the select queries remain open even after the query execution is complete.
The application executes numerous queries. Since these cursors are not closed iam getting a "maximum open cursor reached exception"
Can someone suggest a way to close all the open cursors?
Thanks
Thomas. |
|
| Back to top |
|
 |
| kirani |
| Posted: Wed Jul 14, 2004 3:10 pm Post subject: |
|
|
Jedi Knight
Joined: 05 Sep 2001
Posts: 3779
Location: Torrance, CA, USA
|
What is the exact error message you are getting? Can you print the complete ExceptionList? Were you able to capture data into SQLCODE, SQLNATIVEERROR, SQLSTATE, SQLERRORTEXT variables?
_________________
Kiran
IBM Cert. Solution Designer & System Administrator - WBIMB V5
IBM Cert. Solutions Expert - WMQI
IBM Cert. Specialist - WMQI, MQSeries
IBM Cert. Developer - MQSeries
|
|
| Back to top |
|
|
| AgentSmith |
| Posted: Thu Jul 15, 2004 5:26 am Post subject: |
|
|
Novice
Joined: 19 Nov 2003
Posts: 19
|
Hi,
I am also part of Varghese Thomas's team.
The exception we got was:
(
(0x01000000):RecoverableException = (
(0x03000000):File = 'F:\build\S000_P\src\DataFlowEngine\ImbComputeNode.cpp'
(0x03000000):Line = 471
(0x03000000):Function = 'ImbComputeNode::evaluate'
(0x03000000):Type = 'ComIbmComputeNode'
(0x03000000):Name = 'RDD_MSGFLOW_BECE#FCMComposite_1_23'
(0x03000000):Label = 'RDD_MSGFLOW_BECE.PropagateSubscriberEvents'
(0x03000000):Text = 'Caught exception and rethrowing'
(0x03000000):Catalog = 'BIPv500'
(0x03000000):Severity = 3
(0x03000000):Number = 2230
(0x01000000):RecoverableException = (
(0x03000000):File = 'F:\build\S000_P\src\DataFlowEngine\ImbRdl\ImbRdlStatementGroup.cpp'
(0x03000000):Line = 153
(0x03000000):Function = 'SqlStatementGroup::execute'
(0x03000000):Type = 'ComIbmComputeNode'
(0x03000000):Name = 'RDD_MSGFLOW_BECE#FCMComposite_1_23'
(0x03000000):Label = 'RDD_MSGFLOW_BECE.PropagateSubscriberEvents'
(0x03000000):Text = 'Error detected, rethrowing'
(0x03000000):Catalog = 'BIPv500'
(0x03000000):Severity = 3
(0x03000000):Number = 2488
(0x01000000):Insert = (
(0x03000000):Type = 5
(0x03000000):Text = '.RDD_MSGFLOW_BECE_PropagateSubscriberEvents.Main'
)
(0x01000000):Insert = (
(0x03000000):Type = 5
(0x03000000):Text = '46.4'
)
(0x01000000):Insert = (
(0x03000000):Type = 5
(0x03000000):Text = 'PROPAGATE;'
)
(0x01000000):RecoverableException = (
(0x03000000):File = 'F:\build\S000_P\src\DataFlowEngine\PluginInterface\ImbJniNode.cpp'
(0x03000000):Line = 875
(0x03000000):Function = 'ImbJniNode::evaluate'
(0x03000000):Type = 'ComIbmJniNode'
(0x03000000):Name = 'RDD_MSGFLOW_BECE#FCMComposite_1_19'
(0x03000000):Label = 'RDD_MSGFLOW_BECE.BECE.PluginNode.DeltaDataGenerator'
(0x03000000):Text = 'Caught exception and rethrowing'
(0x03000000):Catalog = 'BIPv500'
(0x03000000):Severity = 3
(0x03000000):Number = 2230
(0x01000000):RecoverableException = (
(0x03000000):File = 'F:\build\S000_P\src\DataFlowEngine\ImbRdl\ImbRdlStatementGroup.cpp'
(0x03000000):Line = 153
(0x03000000):Function = 'SqlStatementGroup::execute'
(0x03000000):Type = 'ComIbmJniNode'
(0x03000000):Name = 'RDD_MSGFLOW_BECE#FCMComposite_1_19'
(0x03000000):Label = 'RDD_MSGFLOW_BECE.BECE.PluginNode.DeltaDataGenerator'
(0x03000000):Text = 'Error detected, rethrowing'
(0x03000000):Catalog = 'BIPv500'
(0x03000000):Severity = 3
(0x03000000):Number = 2488
(0x01000000):Insert = (
(0x03000000):Type = 5
(0x03000000):Text = ''
)
(0x01000000):Insert = (
(0x03000000):Type = 5
(0x03000000):Text = '1.1'
)
(0x01000000):Insert = (
(0x03000000):Type = 5
(0x03000000):Text = 'SET Environment.WorkArea.ROOT_PRIMARYKEY[ ] = PASSTHRU('SELECT APPL_SYSTEM.OID AS C6973,ADDRESS.OID AS C6653,APPL_SYS_CONTEXT.OID AS C6961,CLIENT.OID AS C6868,CLIENT_ALIAS.OID AS C7000,CLIENT_ADDR.OID AS C6856 FROM ADDRESS,APPL_SYSTEM,APPL_SYS_CONTEXT,TEAM_MEMBER,CLIENT,CLIENT_ADDR,CLIENT_ALIAS,CLIENT_TEAM WHERE CLIENT.OID=1431972 AND ((APPL_SYSTEM.APN_CD = 'GESC' OR APPL_SYSTEM.APN_CD = 'JPM' OR APPL_SYSTEM.APN_CD = 'EC1' )) AND ((TEAM_MEMBER.TMR_CD = 'PMA' OR TEAM_MEMBER.TMR_CD = 'C1' OR TEAM_MEMBER.TMR_CD = 'C3' )) AND (CLIENT_ALIAS.ASC_OID = APPL_SYS_CONTEXT.OID AND APPL_SYS_CONTEXT.APP_OID = APPL_SYSTEM.OID ) AND (CLIENT_ADDR.CLT_OID = CLIENT.OID AND ADDRESS.OID = CLIENT_ADDR.ADD_OID ) AND (CLIENT_TEAM.CLT_OID = CLIENT.OID AND TEAM_MEMBER.TEM_OID = CLIENT_TEAM.TEM_OID )');'
)
(0x01000000):RecoverableException = (
(0x03000000):File = 'F:\build\S000_P\src\DataFlowEngine\ImbRdl\ImbRdlExternalDb.cpp'
(0x03000000):Line = 276
(0x03000000):Function = 'SqlExternalDbStmt::executeStmt'
(0x03000000):Type = 'ComIbmJniNode'
(0x03000000):Name = 'RDD_MSGFLOW_BECE#FCMComposite_1_19'
(0x03000000):Label = 'RDD_MSGFLOW_BECE.BECE.PluginNode.DeltaDataGenerator'
(0x03000000):Text = 'The following error occurred during execution of an SQL statement'
(0x03000000):Catalog = 'BIPv500'
(0x03000000):Severity = 3
(0x03000000):Number = 2519
(0x01000000):Insert = (
(0x03000000):Type = 5
(0x03000000):Text = ''
)
(0x01000000):Insert = (
(0x03000000):Type = 5
(0x03000000):Text = '1.44'
)
(0x01000000):Insert = (
(0x03000000):Type = 5
(0x03000000):Text = 'DS20'
)
(0x01000000):Insert = (
(0x03000000):Type = 5
(0x03000000):Text = 'SELECT APPL_SYSTEM.OID AS C6973,ADDRESS.OID AS C6653,APPL_SYS_CONTEXT.OID AS C6961,CLIENT.OID AS C6868,CLIENT_ALIAS.OID AS C7000,CLIENT_ADDR.OID AS C6856 FROM ADDRESS,APPL_SYSTEM,APPL_SYS_CONTEXT,TEAM_MEMBER,CLIENT,CLIENT_ADDR,CLIENT_ALIAS,CLIENT_TEAM WHERE CLIENT.OID=1431972 AND ((APPL_SYSTEM.APN_CD = 'GESC' OR APPL_SYSTEM.APN_CD = 'JPM' OR APPL_SYSTEM.APN_CD = 'EC1' )) AND ((TEAM_MEMBER.TMR_CD = 'PMA' OR TEAM_MEMBER.TMR_CD = 'C1' OR TEAM_MEMBER.TMR_CD = 'C3' )) AND (CLIENT_ALIAS.ASC_OID = APPL_SYS_CONTEXT.OID AND APPL_SYS_CONTEXT.APP_OID = APPL_SYSTEM.OID ) AND (CLIENT_ADDR.CLT_OID = CLIENT.OID AND ADDRESS.OID = CLIENT_ADDR.ADD_OID ) AND (CLIENT_TEAM.CLT_OID = CLIENT.OID AND TEAM_MEMBER.TEM_OID = CLIENT_TEAM.TEM_OID )'
)
(0x01000000):Insert = (
(0x03000000):Type = 5
(0x03000000):Text = ''
)
(0x01000000):DatabaseException = (
(0x03000000):File = 'F:\build\S000_P\src\DataFlowEngine\ImbOdbc.cpp'
(0x03000000):Line = 167
(0x03000000):Function = 'ImbOdbcHandle::checkRcInner'
(0x03000000):Type = ''
(0x03000000):Name = ''
(0x03000000):Label = ''
(0x03000000):Text = 'Root SQL exception'
(0x03000000):Catalog = 'BIPv500'
(0x03000000):Severity = 3
(0x03000000):Number = 2321
(0x01000000):Insert = (
(0x03000000):Type = 2
(0x03000000):Text = '-1'
)
(0x01000000):DatabaseException = (
(0x03000000):File = 'F:\build\S000_P\src\DataFlowEngine\ImbOdbc.cpp'
(0x03000000):Line = 268
(0x03000000):Function = 'ImbOdbcHandle::checkRcInner'
(0x03000000):Type = ''
(0x03000000):Name = ''
(0x03000000):Label = ''
(0x03000000):Text = 'Child SQL exception'
(0x03000000):Catalog = 'BIPv500'
(0x03000000):Severity = 3
(0x03000000):Number = 2322
(0x01000000):Insert = (
(0x03000000):Type = 5
(0x03000000):Text = 'HY000'
)
(0x01000000):Insert = (
(0x03000000):Type = 2
(0x03000000):Text = '604'
)
(0x01000000):Insert = (
(0x03000000):Type = 5
(0x03000000):Text = '[DataDirect][ODBC Oracle driver][Oracle]ORA-00604: error occurred at recursive SQL level 1
ORA-01000: maximum open cursors exceeded
'
)
)
)
)
)
)
)
)
) ---- |
|
| Back to top |
|
|
| JT |
| Posted: Thu Jul 15, 2004 8:31 am Post subject: |
|
|
Padawan
Joined: 27 Mar 2003
Posts: 1564
Location: Hartford, CT.
|
Just a suggestion, try issuing a commit:
| Code: |
=======================================
int transactionType = MbSQLStatement.SQL_TRANSACTION_COMMIT;
MbSQLStatement stmt = node.createSQLStatement(dataSourceName, statement, transactionType);
stmt.execute(assembly);
stmt.clearStatement();
======================================= |
What version of Oracle are you accessing? How many selects are you performing within the transaction? Can you compensate by increasing the number of OPEN_CURSORS (INITSID.ORA)? |
|
| Back to top |
|
|
| kirani |
| Posted: Thu Jul 15, 2004 10:34 am Post subject: |
|
|
Jedi Knight
Joined: 05 Sep 2001
Posts: 3779
Location: Torrance, CA, USA
|
Try putting the same SELECT statements in a Compute (or DB) node and see if you get the same behavior.
_________________
Kiran
IBM Cert. Solution Designer & System Administrator - WBIMB V5
IBM Cert. Solutions Expert - WMQI
IBM Cert. Specialist - WMQI, MQSeries
IBM Cert. Developer - MQSeries
|
|
| Back to top |
|
|
| Vargheese thomas |
| Posted: Fri Jul 16, 2004 6:04 am Post subject: |
|
|
Novice
Joined: 16 May 2003
Posts: 13
|
Hi,
Thanks for your valuable suggestions.
1. The transaction type is commit. Even then we are having this problem  .
2. ORACLE Version is 9i.
3. Yes. As a temporary fix, we increased the OPEN_CURSORS in the INIT.ORA file. But the problem is, the number of queries keeps increasing as the number of events increases. So this cannot be a permanent fix.
4. In our case, we cannot use compute node/DB node. B'cos this data is cached and is used for further processing of other events.
Is there any other way to tackle this situation? Pls suggest.
Thanks
Thomas |
|
| Back to top |
|
|
| kirani |
| Posted: Fri Jul 16, 2004 3:54 pm Post subject: |
|
|
Jedi Knight
Joined: 05 Sep 2001
Posts: 3779
Location: Torrance, CA, USA
|
| Vargheese thomas wrote: |
4. In our case, we cannot use compute node/DB node. B'cos this data is cached and is used for further processing of other events.
|
Event within the same message or event within different message? If same message then you can store data into global Environment Tree and that can be refered in subsequent nodes.
_________________
Kiran
IBM Cert. Solution Designer & System Administrator - WBIMB V5
IBM Cert. Solutions Expert - WMQI
IBM Cert. Specialist - WMQI, MQSeries
IBM Cert. Developer - MQSeries
|
|
| Back to top |
|
|
| Vargheese thomas |
| Posted: Mon Jul 19, 2004 8:18 am Post subject: |
|
|
Novice
Joined: 16 May 2003
Posts: 13
|
The cache is used by different messages. So i think i cannot cache the values in the global environment tree.
Thanks
Thomas.
_________________
Thanks and regards
Vargheese Thomas |
|
| 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
|
|
|
|
