That's a good question and I don't know the answer. I keep track myself with a boolean flag.

If you compiled with CLOSQLCSR *ENDMOD (which I don't recommend for performance reasons, I typically use *ENDACTGRP so ODP's can be reused by the query engine) when they will close automatically when the program exists (regardless of the *INLR value).

Chris Ringer

Hi Chris. I added the CLOSQLCSR and that seems to have taken care of the problem. BTW, this is an RPG stored procedure that is called by a job that goes into DLYW, then calls it again and times out after a certain amount of time with no activity.

Also, I wanted to thank you again for turning me on to RPG sprocs. They are awesome! I use them almost exclusively now where the sproc has to retrieve data from more than 4 files. It's the only way to go. Thanks again!

Chris

I always keep track, too. But you might be able to get it from SQLSTATE. You try to open, and if you don't get '00000', open failed. '24502', for example, means cursor identified in an OPEN statement is already open.  I don't know what OPENing an already open cursor does the file pointer (first open positions at the start of the result set, I think). Of course, if it's scroll cursor and you're going to reposition anyway, that may not matter.

Just FYI, FWIW, I normally use CLOSQLCSR(*ENDACTGRP) so the SQL query optimizer can reuse ODPs (open data paths - temporary indexes) so subsequent calls to the program (module) will be fast. Typically you don't want the temporary indexes being rebuilt over and over on every call.

Here are some general rules I've learned/googled over time.

1. For embedded SQL, DB2 Universal Database for iSeries only reuses ODPs opened by the same RPG *statement number*. So don't duplicate the same SQL statement in the program, put it in one subroutine.
2. Use CLOSQLCSR = *ENDACTGRP or *ENDJOB to keep ODP alive.
3. Try to keep the prepared SQL string a constant length. Use ? parameter markers in SQL string.
4. If eligible to be reused you will see this info in the job log in debug: 
   1st time SQL is run: ODP Created, ODP Deleted.             
   2nd time SQL is run: ODP Created, ODP Not Deleted.         
   3rd time SQL is run: ODP Reused.  <-- GOOD!                         
5. The current SQL statement must be very similar to last ODP.
6. If number of rows to fetch changes "a lot", the ODP is rebuilt. 

Chris Ringer

Great tips, Chris. Thanks!