Midrange News for the IBM i Community


Posted by: Chris Proctor
Programmer Analyst
Columbia Sports Company
Portland, OR
Can I check whether my cursor is open in an RPG program?
has no ratings.
Published: 02 May 2013
Revised: 03 May 2013 - 1511 days ago
Last viewed on: 22 Jun 2017 (5349 views) 

Using IBM i? Need to create Excel, CSV, HTML, JSON, PDF, SPOOL reports? Learn more about the fastest and least expensive tool for the job: SQL iQuery.

Can I check whether my cursor is open in an RPG program? Published by: Chris Proctor on 02 May 2013 view comments(5)

Good morning. Is there a way of checking whether or not my cursor is open in an RPG program? I'm thinking of something like you would use for a file. Like this:

if %open(cursorname);

  exec sql close cursorname;

endif;

Any help would be greatly appreciated.

Return to midrangenews.com home page.
Sort Ascend | Descend

COMMENTS

(Sign in to Post a Comment)
Posted by: Ringer
Premium member *
Comment on: Can I check whether my cursor is open in an RPG program?
Posted: 4 years 1 months 19 days 22 hours 22 minutes ago
Edited: Thu, 02 May, 2013 at 12:01:36 (1512 days ago)

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

Posted by: chrisp
Premium member *
Portland, OR
Comment on: Can I check whether my cursor is open in an RPG program?
Posted: 4 years 1 months 19 days 21 hours 44 minutes ago

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

Posted by: DaleB
Premium member *
Reading, PA
Comment on: Can I check whether my cursor is open in an RPG program?
Posted: 4 years 1 months 19 days 20 hours 29 minutes ago

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.

Posted by: Ringer
Premium member *
Comment on: Can I check whether my cursor is open in an RPG program?
Posted: 4 years 1 months 18 days 23 hours 37 minutes ago

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

Posted by: chrisp
Premium member *
Portland, OR
Comment on: Can I check whether my cursor is open in an RPG program?
Posted: 4 years 1 months 18 days 23 hours 31 minutes ago

Great tips, Chris. Thanks!