NOTICE: developerWorks Community will be offline May 29-30, 2015 while we upgrade to the latest version of IBM Connections. For more information, read our upgrade FAQ.
This topic has been locked.
2 replies Latest Post - 2013-03-28T14:19:09Z by SystemAdmin
Pinned topic *ENDMOD with Trigger
Answered question This question has been answered.
Unanswered question This question has not been answered yet.
I'm looking at performance issues and am trying to understand the use of *ENDMOD with SQL Triggers. We seem to be using a lot of storage so I used the SQL_DUMP_CURSORS procedure to dump the open cursors and have found that their are several implicit cursors named ' CURSR' associated with trigger programs. The triggers are defined with *ENDMOD for the CLOSSQLCSR option. I'm wondering if we need to do something else to close these cursors when the triggers finish?
Updated on 2013-03-28T14:19:09Z at 2013-03-28T14:19:09Z by SystemAdmin
krmilligan 120000MDWP447 PostsACCEPTED ANSWER
Re: *ENDMOD with Trigger2013-03-27T20:11:16Z in response to SystemAdminClosing SQL cursors usually is not a way to improve performance. All of the SQL Procedural objects(Triggers, Procedures, etc) were changed to use *ENDMOD to be compatible with the behavior of other DB2 products. The *ENDMOD setting in this environment does not effect the reuse of SQL cursors/ODPs.
I would look at other ways to improve performance.
SystemAdmin 110000D4XK3129 PostsACCEPTED ANSWER
Re: *ENDMOD with Trigger2013-03-28T14:19:09Z in response to krmilliganKent,
Thanks for your time, but I have another question.
The performance problem we are dealing with involves increasing memory usage. The job logs shows several PWS0083 messages. I've looked at the usual suspects (leaking statements, queries that use temp storage, etc.) but have not seen anything obvious. One thing I have noted is that the descriptor count as reported in the iseries navigator job->details->SQL page has periods of rapid increase. The queries come from a WAS/JDBC application through JT400 and call several external stored procedures. None of the stored procedures allocate descriptors and all result sets are returned to the client where they are properly closed.
What does the descriptor count displayed in the Navigator screen represent?