Re: ovrdbf SHARE parameter

Scott Klement wrote:

David FOXWELL wrote:

Does this parameter have any effect on a file used in an rpg
by embedded sql? I'm looking at a lot of ovrdbf in a clp that uses this parameter, but without the opnqryf that I'm used to see following it.

I don't think it's possible to share an ODP opened by SQL. (Unfortunately, I can no longer remember where I heard that.) So unless I'm mistaken, SHARE(*YES) has no impact on embedded SQL.

SQL will not open shared regardless of the member attribute or override, nor will a query use an existing shared ODP. Anecdotally one can try either scenario and see by the WRKJOB OPTION(*OPNF) that no sharing is effected.

In CQE an attempt to reference a shared ODP was as I understood, supposed to always be terminated with an error msgCPF4205 "Shared open not allowed for query." An exception was made for a special case, to support a /trick/ some customers had been using for RUNQRY. I think the error is now specific to the OPNQRYF & RUNQRY path of CQE; the condition probably still being overlooked in the special case.

As to why a CLP would use SHARE(*YES) without OPNQRYF, there are two common reasons I've encountered:

a) To improve performance. If you go many years back, when computers had only kilobytes of memory, the cost (in terms of memory and performance) of opening a file multiple times was very
high. So if you had multiple programs using the same file, you'd
share the ODP so they'd all share the same file open, improving
performance and reducing memory footprint. IMHO, this is not a
good practice today.

IMO sharing is still worthwhile both for memory and performance of opens. Plus for every full open there is a full close. Full opens are excessively expensive by comparison to a shared open, and that cost is the reason for so much effort by the SQL over the years to maintain [more often and whenever possible] pseudo-closed cursors; i.e. their non-shared but "do not require full open next time" ODPs. Of course other than with the open itself, the programs that end up sharing the ODP [which includes the first full open], must understand that their data accesses via the shared ODP is effectively stateless. So if an application or another set of programs can be designed to utilize and take advantage of shared opens, then the benefits seen will increase for each additional access which can be changed from exclusive to a sharer.

<<SNIP>>

Sometimes the calls that follow are to SQLRPGLE programs, which
is the reason for my question. Sometimes the calls are to ordinary RPGs and there, I don't understand why the programmer has added share(*yes).

If the file isn't used on an F-spec (by anything that's called within the scope of the OVRDBF) or a similar mechanism to an F-spec... then I doubt the SHARE(*YES) is having any impact at all.

More likely the programmer just didn't understand that SHARE(*YES) was not required, and so kept it when converting from
another technology. Since the share is ignored, it wouldn't have
affected the program's ability to work or not work.

I concur. The idea of someone having left the SHARE(*YES) from "the way it was done", having been carried into the now implemented by\as SQLRPG, reminds of the *many* examples of similar.

I have often seen SQLRPG code where there is an F-spec for each file reference by the SQL; in SELECT statements, even if not also for other statements like UPDATE or DELETE which is bizarre. Now had the file specifications been there solely for some "documentation" purpose and having coded USROPN [and the files never explicitly opened], then maybe not so bad. However making the outcome negative, for lack of USROPN and no explicit opens, the files named would get full opened every time the SQLRPG program runs; i.e. the RPG will implicitly OPEN every file on the F-Spec, in that case for no reason. So starting the program came with the cost of every full open effected and the excessive\useless clutter in the output of WRKJOB OPTION(*OPNF) and OPTION(*JOBLCK), and the end of the program typically with the cost of full close for all of those same opens which never had any RPG I\O opcodes requested against them.

<<SNIP>>