| Author |
Message
|
| hellozen123 |
 Posted: Mon May 09, 2016 4:16 am Post subject: ORA-01000: maximum open cursors; PASSTHRU statement in loop |
 |
|
Novice
Joined: 09 May 2016
Posts: 11
|
Inside ESQL (IIB 10), I am using PASSTHRU statement within a loop to delete/insert/update records into Oracle DB, however getting "ORA-01000: maximum open cursors exceeded" when number of records increases certain limit. This development was done to generate SQL statement dynamically from the input request, as we were receiving Table Name, Column Name, DB Action (Insert/Delete/Update) and number of rows at runtime.
As suggested in similar post, I have tried several options such as
a) Issuing Passthru(Commit) instead of ESQL Commit.
b) Writing the Passthru logic in subflow and calling it inside a loop from mainflow.
c) Changing Transaction property of flow or Compute node.
but nothing worked.
I know its a design flaw and such coding practices are not recommended by IBM and experts, however I don’t have any control over design decisions. Since this issue was observed during UAT, I had provided a temporary fix by hardcoding Table and Column name inside ESQL and using Insert/Delete/Update statements.
It would be great if someone can advise and guide me into the direction to resolve this open cursor issue without changing the original design (without hardcoding table and column names).
Thanks in advance. |
|
| Back to top |
|
 |
| mqjeff |
| Posted: Mon May 09, 2016 6:38 am Post subject: |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 17447
|
Can your DBA create a stored procedure that handles the transaction for you?
_________________
chmod -R ugo-wx / |
|
| Back to top |
|
|
| hellozen123 |
| Posted: Mon May 09, 2016 4:20 pm Post subject: |
|
|
Novice
Joined: 09 May 2016
Posts: 11
|
| As an EAI team we dont own any database, for this particular requirement we were loaned application's tablespace to create few of our staging tables. It is not in scope (contractually) of DBA to create SP for us, more over we are not allowed to create one ourself. This is where it got tricky and left us with no option than to use PASSTHRU. |
|
| Back to top |
|
|
| ruimadaleno |
| Posted: Tue May 10, 2016 1:30 am Post subject: |
|
|
Master
Joined: 08 May 2014
Posts: 274
|
do you understand the meaning of "ORA-01000: maximum open cursors exceeded" ?
Please ask you DBA , he will give you some pointers to avoid this error.
_________________
Best regards
Rui Madaleno |
|
| Back to top |
|
|
| hellozen123 |
| Posted: Tue May 10, 2016 3:21 pm Post subject: |
|
|
Novice
Joined: 09 May 2016
Posts: 11
|
Yes, we did consult our DBA, as a temporary measure they have increased the OPEN_CURSORS parameter to number much higher than the maximum number of records we can expect in Prod.
They also suggested to check our code of this bug (which we are already aware of) as increasing the parameter is not a permanent solution. For rest of our other interfaces we are using the regular SQL statements and they never caused any issues.
In this case we had to use PASSTHRU to dynamically create SQL queries. Is there anyway we can instruct Oracle to close the cursors opened for our session after certain number? Any statement we can issue from ESQL (COMMIT and PASSTHRU(COMMIT) did not close the CURSORS) rather than changing Oracle configuration. |
|
| Back to top |
|
|
| mqjeff |
| Posted: Wed May 11, 2016 4:57 am Post subject: |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 17447
|
There should be methods to control your use of DB Cursors during a Broker flow execution.
A user/service trace and an ODBC trace may help you identify which parts of your flow are causing it (it might not be directly your ESQL nodes). It could be related to the size of the transaction you're running the flow in combined with the number of active instances in the flow.
And you may also be able to install the full Oracle client and configure the ODBC datasource to use that instead of the wire-level protocol driver.
_________________
chmod -R ugo-wx / |
|
| Back to top |
|
|
| hellozen123 |
| Posted: Thu Jul 07, 2016 4:59 pm Post subject: |
|
|
Novice
Joined: 09 May 2016
Posts: 11
|
| Quote: |
| There should be methods to control your use of DB Cursors during a Broker flow execution. |
Option for setting up MQSI_EMPTY_DB_CACHE environment variable (even though it resolves the issue) was ruled out as it can have a negative impact on flows running Java code. This impact was not tested as the risk of changing this parameter just for one flow could not be justified.
| Quote: |
A user/service trace and an ODBC trace may help you identify which parts of your flow are causing it (it might not be directly your ESQL nodes).
|
Nothing specific appeared in the trace which can suggest that the problem is with a specific compute node.
| Quote: |
| It could be related to the size of the transaction you're running the flow in combined with the number of active instances in the flow. |
You are right, it appears to be related to the size of the transaction. I tried executing the simple DB insert (with hardcoded values) inside a loop (x1000+) using PASSTHRU, it never increased the cursor size to more than one.
Presently we are not executing a very complex operation, it just a simple DB insert of a single row with 4-5 columns. However to prepare insert string we are doing a complex processing on the large input message with hundreds of record. We are running only one instance of the message flow.
| Quote: |
| And you may also be able to install the full Oracle client and configure the ODBC data source to use that instead of the wire-level protocol driver. |
Using different driver other than wire-level protocol driver (Datadirect) did not solve the issue.
Code is now live, with OPEN_CURSORS changed to few thousand  . Management and architect (Integration and DB) were happy about changing the parameter (temporaryly) because of the flexibility this code provides. As a developer of this flow, I am not convinced with the decision and still looking to resolve the issue permanently. |
|
| Back to top |
|
|
| eammnsh |
| Posted: Sat Oct 08, 2016 4:01 am Post subject: |
|
|
Apprentice
Joined: 22 Aug 2016
Posts: 29
|
I`ve been through this issue before, and we have to switch to the Oracle JDBC driver and to use JCN.
In java you can use the database batch handling (Batch.add) and (Batch.execute), this solution was very good as we were supposed to work with and handle large number of insert/update/delete queries.
I am not sure of why the ODBC with ESQL cant handle large number of insert/update/delete db queries like a batch!! |
|
| Back to top |
|
|
|
|
