Power

I am sure there is a simple answer to this. I have display program using a scrollable cursor to load the subfile.  I verified that I close the cursor when I leave the program.  The problem is that the FILE is still locked to the job when I exit the program.  This make is hard to rebuild the file, which is one of the submitted tasks from the program.  10 free tokens to the first one with an answer. :)    

exec sql declare PreNoteWorkList scroll cursor for
  select Record_ID, OWNER_NUMBER,getVendorLongName( OWNER_NUMBER),
         getVendorBankAccount(Owner_Number,'R'), ' ' Audit_Date,
         INCLUDE_FLAG
    From rvsxprepf
   where (:v1OwnerG = ' ' or OWNER_NUMBER = :v1OwnerG)
     and ((:gIncludeFlag = ' ' or :gIncludeFlag = 'Y' and Include_Flag = 'Y')
       or (:gIncludeFlag = ' ' or :gIncludeFlag = 'N' and Include_Flag = 'N'))
order by Owner_Number
FOR FETCH ONLY;

------------------------------
David Taylor
------------------------------

Dear David

I assume you are using embedded SQL in a HLL program.  In such a case, when you compile the program with CRTSQLxxx command, you should specify the parameter CLOSQLCSR with *ENDPGM (which is the default value for the command) - or use *ENDMOD if you use CRTSQLxxxI command - in order for the cursor and ODP to be deleted when the program ends as described in this IBM Technote :  Using the CLOSQLCSR Parameter with CRTSQLxxx at  https://www.ibm.com/support/pages/using-closqlcsr-parameter-crtsqlxxx.   But if you already did this, then please try running the command RCLRSC after the program ends to see if the lock is release or not.

Is it possible that the lock comes from getVendorLongName and/or getVendorBankAccount ? 

It would be good if you could also check if your SQL encounters any error or not by using SQLCOD and SQLSTATE. 

------------------------------
Chance favors only the prepared mind.
-- Louis Pasteur
------------------------------
Satid S.

Original Message:
Sent: Mon November 06, 2023 04:02 PM
From: David Taylor
Subject: Cursor with Fetch Only Still locks the file.

I am sure there is a simple answer to this. I have display program using a scrollable cursor to load the subfile.  I verified that I close the cursor when I leave the program.  The problem is that the FILE is still locked to the job when I exit the program.  This make is hard to rebuild the file, which is one of the submitted tasks from the program.  10 free tokens to the first one with an answer. :)    

exec sql declare PreNoteWorkList scroll cursor for
  select Record_ID, OWNER_NUMBER,getVendorLongName( OWNER_NUMBER),
         getVendorBankAccount(Owner_Number,'R'), ' ' Audit_Date,
         INCLUDE_FLAG
    From rvsxprepf
   where (:v1OwnerG = ' ' or OWNER_NUMBER = :v1OwnerG)
     and ((:gIncludeFlag = ' ' or :gIncludeFlag = 'Y' and Include_Flag = 'Y')
       or (:gIncludeFlag = ' ' or :gIncludeFlag = 'N' and Include_Flag = 'N'))
order by Owner_Number
FOR FETCH ONLY;

------------------------------
David Taylor
------------------------------