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 120000MDWP450 Posts
Re: *ENDMOD with Trigger2013-03-27T20:11:16ZThis is the accepted answer. This is the accepted answer.Closing 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 Posts
Re: *ENDMOD with Trigger2013-03-28T14:19:09ZThis is the accepted answer. This is the accepted answer.
- krmilligan 120000MDWP
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?