Is there a way to close Stored Prod cursor from Connect-SQL rule?
Is there a way to close Stored Prod cursor from Connect-SQL rule?We are calling a partner system stored proc which is passing us values via cursor parameter.
We need to close this cursor,so that open cursor count does not reach threshold
Virtusa IT Consulting
AE
Hi Shyam,
If you know the name of cursor, you can try closing the cursor as CLOSE <cursor_name> in 'save' tab of Connect-SQL rule and call it in the activity with 'RDB-Save' method.
You can define a block as below in 'Save' tab of Connect-SQL rule and call it in the activity with 'RDB-Save' method, the block should fetch the cursor and close it.
DECLARE
mycursor SYS_REFCURSOR;
BEGIN
mycursor := <YOUR_PROCEDURE> -- Getting the cursor from the procedure
CLOSE mycursor; -- Closing the global cursor returned from the precedure
END;
Thanks,
Habeeb Baig
Pega should be closing the connection and thereby close the cursor once it is done executing the Connect-SQL rule. Are you sure the number of open cursors is increasing over time because Pega is not closing the connections?
If that is the case, the problem needs to be investigated by Pega support team.
JP Morgan Chase
US
Hi Vipin,
We are sure,it does not closes cursors as we were in parallel monitoring cursor count in database.raised an SR with pega but as usual some irrelevant details they asked for which is no way related to my question.
Response from Pega:-
Hello Shyam,
There's no command to close the cursor because Pega should do so automatically. As it is not doing so, something is wrong. I have some ideas on how to find the root cause. For now, if possible, you should try increasing the number of cursors that can be open to help the issue while we continue the root cause analysis. It is better to have slowdown than to have the application crash.
Please provide the following or I cannot continue investigation on this case:
1) Settings for maximum open cursors in the database
2) prconfig.xml
3) The documents you have showing that the cursors are not being closed.
4) PegaRULES log (there might be a stack trace that shows the issue).
5) ulimit settings (OS level)
6) How often is this stored procedure run?
Thanks!
- Shyam