I have the SQLRPGLE code below showing my access and close - yet after the program runs the files (MONEWAS3 and WELFARE) stay open as verified by looking at "Display Open Files" for the session. If I sign off and sign back on they obviously close, but running the program again will open and leave them open.
When I compile the code (CRTSQLRPGI) I am changing CLOSQLCSR to *ENDMOD.
Any ideas as to what I am doing wrong that is causing the files to remain open?
ctl-opt dftactgrp(*no);
dcl-f MOCL0700DP printer oflind(PageOverflow) ;
dcl-s PageOverflow ind;
dcl-s count uns(10);
dcl-s inFund zoned(4);
dcl-s inDateRec zoned(8);
dcl-s inFedId packed(9:0);
dcl-s inPayPer zoned(8);
dcl-s inLname char(20);
dcl-s inFinit char(1);
dcl-s inMinit char(1);
dcl-s inSocSec packed (9:0);
dcl-s inAreaW zoned(4);
dcl-s inHomeW zoned(4);
nextPage();
exec sql create alias qtemp.monewas3a FOR MONEWAS3(AS30700);
exec sql declare C0 cursor for
SELECT ALL T01.FUND, T01.DATREC, T01.FEDID, T01.PAYPER, T01.LNAME,
T01.FINIT, T01.MINIT, T01.SOCSEC, T02.AREAW, T02.HOMEW
FROM monewas3a T01
INNER JOIN WELFARE T02 ON T01.SOCSEC = T02.SOCSEC
WHERE FUND = AREAW AND HOMEW <> 0322
ORDER BY T01.DATREC ASC, T01.FEDID ASC, T01.PAYPER ASC,
T01.LNAME ASC;
exec sql open C0;
dow (1=1);
exec sql fetch next from C0 into :inFund, :inDatRec, :inFedId,
:inPayPer, :inLname, :inFinit, :inMinit, :inSocSec, inAreaW,
:inHomeW;
if sqlcod <> 0;
leave;
endif;
if Pageoverflow;
nextPage();
endif;
write DETAIL;
count += 1;
enddo;
exec sql close C0 ;
exec sql drop alias qtemp.monewas3a;
if count = 0;
write ERR01;
endif;
*inlr = *on;
close *all;
dcl-proc nextPage;
pageoverflow = *off;
write HDG01;
end-proc;
I suspect that since you're creating a cursor over an alias, the SQL operation is treated as dynamic and thus you're running into a pseudo closed cursor
Pseudo closed cursors are a key part of a performance optimization feature of IBM DB2 for i SQL. When an application closes a cursor, Db2 for i normally closes the cursor and closes the file, deleting the ODP (Open Data Path). If the application runs the same statement multiple times, each new execution requires a full open of the target file. The idea behind pseudo closed cursors is to not fully close the cursor and file but rather to cache the cursor for potential future use.
After running the program, try issuing
ALCOBJ OBJ((MONEWAS3 *FILE *EXCL)) CONFLICT(*RQSRLS)
If the file is now closed, then you've confirmed that the issue is that the cursor was pseudo closed by the OS.
If that is the case, then it's "Working as designed"
The behavior shouldn't be a problem, if it is a problem...it's usually a sign of some other part of your processing doing something that's not compatible with modern applications.
Edit
CLRPFM CL command for instance, for a long time (and maybe still) had a problem with pseudo closed cursors. But an SQL DELETE or TRUNCATE work just fine.
Edit2
Do a DSPPGM and look at what activation group the program is running in. I suspect that it is *CALLER. Given that you are calling it from the command line, that means you are running a true ILE program in the poorly (IMHO) named "Default Activation Group". Doing so is one of the well known The Seven Deadly Sins of ILE. One side effect of this particular sin is that stuff doesn't get cleaned up correctly.
Anytime your building a true ILE program, ctl-opt dftactgrp(*no); you need to consider what activation group it should run in. For a utility program called from the command line, I'd use *NEW.
ctl-opt dftactgrp(*no) actgrp(*NEW);