On 05 Dec 2012 14:01, John Yeung wrote:
I still don't see why a program shouldn't have the ability to tell
the DB: "Hey, I know you're trying to optimize things for me, but I
am explicitly done with this, and I really want others to be able to
use it (or whatever resources are associated with it) without hassle.
If it turns out I need it again later, I'll ask for it explicitly
and accept the performance penalty."
For the sake of non-SQL work, I tend to agree that a force close
feature would be desirable. I had always desired having available,
either a clause on the CLOSE that would absolutely positively effect a
full close [or even having a separate platform-specific SQL statement or
PROCEDURE call to effect the full close]. The latter, a SP routine, can
be implemented [but only against a TABLE or a MAXMBRS(1) database
physical file] using the somewhat undesirable CL request:
QSYS/CHGPFCST FILE(LibrName/FileName) CST(*all) STATE(*SAME)
Other requests such as an ALTER [e.g. to SET DATA TYPE to the same,
thus effecting no actual change] will also force the close. This is IMO
also very undesirable in most cases; too obscure.
I seem to recall reading that DISCONNECT ALL and RESET CONNECTION can
also close _all_ pseudo-closed cursors in the job, but I do not see that
effect in a test [on v5r3]. Found a reference [from a v4r5 FAQ]:
_i Document Title: Pseudo Closed Cursor FAQ i_
http://www.iprodeveloper.com/forums/aft/118731
One additional means to effect the close is to perform the statement
again while an override is in effect to a non-existent TABLE; or for a
*LIBL name resolution, repeat the statement after removing the library
with the TABLE [and no other libraries in the library list have that
TABLE]. I do not like the override option except in calling programs
because the SQL does not have an OVRDBF procedure for which *CALLLVL
scoped overrides can be implemented to the current call level.... thus a
DLTOVR must be paired and performed on abnormal-end in order to avoid
potential problems for an unwanted override being left active.
Controlling the library list instead, can be just as problematic; and is
similarly undesirable IMO.
<<SNIP>> The DB obligingly releases resources in a reasonable amount
of time if the program ends;
According to the SET OPTION or parameter CLOSSQLCSR specification;
e.g. *ENDMOD, *ENDACTGRP
why doesn't the DB release resources in
a reasonable amount of time from when the program says it's done with
those resources?
Because SQL does [at least should] not care. Can anyone suggest any
SQL that is negatively affected? That would be a concern. That the
non-SQL [aka native] work can be affected is considered to be a
consequence of the SQL; an unfortunate nuisance, for having combined
OS\native work against the "objects" being used by the SQL. The bypass
for the OS\native work is the SQL database close event which effects the
full-close of pseudo-closed cursors; available using the ALCOBJ command
with the CONFLICT(*RQSRLS) "Lock conflict action" parameter
specification when requesting a conflicting lock.
Many database commands which require exclusive access to a file or
member with a pseudo-closed cursor held in another job already signal
this event. Thus even scenarios doing OS\native work should rarely
encounter an issue preventing their work.
The OP never did clarify what the "other work" was, nor how or if it
was failing.... or even if possibly there was merely a false inference
about the impact to the work. I have seen many times that someone
/sees/ the file is open and allocated to a job, and thus presumes the
work they want to do in another job will not work, but they did not
actually try it. To be clear, the OP only suggested the other jobs
"need to access that file"; no indication of for what operation, nor if
the operation was tried, or if the operation was tried but failed.
midrange.com
