RE: [EXT] Re: Should I be using cursors?

Danny,

I have seen this issue also, where ODPs are left open when embedded in an ILERPG program and that program ends. Here is what we found reduces the ODPs upon close, but did not entirely eliminate them:

1. Use the CLOSQLCSR = *ENDMOD clause in your first EXEC SQL line where you have the SET OPTION list, also COMMIT = *NONE if you are not using Commitment Control.

2. If you have the case of one program ["A"] calling another ["B"] and the B program has the embedded SQL, then make program B have ACTGRP(*CALLER) in its CTL-OPT statement. Make program A have ACTGRP(*NEW) in its CTL-OPT statement. Using this method, all of the ODPs created by A and B are cleaned up when A ends. You also would need to use ACTGRP(*CALLER) in all the programs that A calls so their ODPs get cleaned up too, when A ends.

This reduces the open ODPs upon close. But the ODPs that seem to stay open are ones where we are using SQL to calculate a sum of a field and store it in a variable, i.e.:


Select sum(taxes) from payroll_detail where department = 5;


Hope this helps.

Doug