Topic
IC4NOTICE: 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.
2 replies Latest Post - ‏2013-03-28T14:19:09Z by SystemAdmin
SystemAdmin
SystemAdmin
3129 Posts
ACCEPTED ANSWER

Pinned topic *ENDMOD with Trigger

‏2013-03-26T16:17:18Z |
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
    krmilligan
    447 Posts
    ACCEPTED ANSWER

    Re: *ENDMOD with Trigger

    ‏2013-03-27T20:11:16Z  in response to SystemAdmin
    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
      SystemAdmin
      3129 Posts
      ACCEPTED ANSWER

      Re: *ENDMOD with Trigger

      ‏2013-03-28T14:19:09Z  in response to krmilligan
      Kent,
      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?