Gen EDGE

What combination of options do I need to set to keep a CURSOR WITH HOLD on a READ EACH in a Gen batch program?  I have set the CURSOR WITH HOLD option of the READ EACH, and I have tried different Select Clause Isolation Levels - Cursor Stable and Do Not Specify - but when I call a commit within the READ EACH and then try to read the next row, I keep getting -501.  I can see in Detector that the program does 1 open, a number of fetches, and then 1 close, but it should not be executing the CLOSE!!

Please help if you can.....

Thanks!

Cathy

Are you using batch with DB2 on MVS?  We found that CURSOR with HOLD did not generate for ORACLE and other DBMS targets.

If yes, are you doing a ROLLBACK?  That will cause a held CURSOR to be released. 

Otherwise, we use CURSOR with HOLD option in our batch jobs with DB2 and it holds just fine after a COMMIT.

Yes, DB2 on MVS, and I am only calling an EAB to do a COMMIT - no ROLLBACK.  I have used it myself before, but for some reason this one does not work no matter what I try.

Dumb question I know but can you confirm the WITH HOLD clause has actually been generated in the COBOL and on the cursor you are expecting? 

:-)  Yes, I checked.  Here is the statement, and it is on the correct cursor.  I have tried different isolation levels, but none work.  It is super weird, as I have done this before and it has worked, so I have no idea what is different with this one.  We have thought about putting displays in the generated COBOL, but if I find something I can't fix within Gen, that is not going to help.  If anything comes to mind, please let me know.  I am calling the commit dynamically from within the READ EACH, but have done that before and not had a problem.

EXEC SQL DECLARE CUR_1779434173_1 CURSOR WITH HOLD FOR
SELECT                                                
        SHM_SHIPMENT01."SHP_INST_ID",                 
        SHM_SHIPMENT01."PRO_NBR_TXT",                 
        SHM_SHIPMENT01."PKUP_DT",                     
        SHM_SHIPMENT01."ESTIMATED_DLVR_DT",           
        SHM_SHIPMENT01."LST_UPDT_TMST",               
        SHM_SHIPMENT01."CALC_SVC_TMST",               
        SHM_SHIPMENT01."LST_UPDT_UID",                
        SHM_SHIPMENT01."BILL_CLASS_CD",               
        SHM_SHIPMENT01."BILL_STAT_CD",                
        SHM_SHIPMENT01."CHRG_TO_CD",                  
        SHM_SHIPMENT01."ORIG_TRMNL_SIC_CD",           
        SHM_SHIPMENT01."DEST_TRMNL_SIC_CD",           
        SHM_SHIPMENT01."SPLIT_IND",                   
        SHM_SHIPMENT01."HAZMAT_IND",                  
        SHM_SHIPMENT01."FRZBLE_IND",                  
        SHM_SHIPMENT01."GARNTD_IND",                  
        SHM_SHIPMENT01."COD_IND",                     
        SHM_SHIPMENT01."DISC_CD",                     
        SHM_SHIPMENT01."LATE_TENDER_CD",              
        SHM_SHIPMENT01."SPOT_QUOTE_ID"                
FROM                                                  
    "SHM_SHIPMENT"                      SHM_SHIPMENT01
WHERE                                                 
(                                                     
     SHM_SHIPMENT01."LST_UPDT_UID" = 'REMATCH'        
)                                                     
WITH CS                                               
END-EXEC 

Thanks!

Cathy                                          

Hi Cathy,

Yes definitely seems strange!

If you have not done so already I would suggest raising a support case so we can investigate in more detail.

Thanks

Lynn