Re: Stupid question of the century; code app to close & drop locks

Dan Kimmel wrote:

Closing the cursor on the last iteration would release the
implicit allocation, wouldn't it?

Effectively, and why I noted the "timely close is still the goal." I was emphasizing that use of "SQL SELECT statements which don't hold a lock" and "SQL CURSOR for read only" does not in any way negate the requirement to close, nor by itself assist to enable an ALTER. First because there is no SELECT which would not obtain the implicit lock. Second because a cursor for read-only is no better than one for-update since each holds the implicit locks [for all opens] which prevent the exclusive allocation required by the ALTER.

There are essentially two forms of allocation at work for use of the SQL; the one by the database for the open which implicitly holds the lock, plus that the database open may remain associated with a SQL pseudo-closed cursor for which those implicit locks will remain even after /closing the cursor/. If all SQL cursor activity was relegated to a *NEW activation groups then I suppose [with the exception of job scoped SQL cursors,] pseudo-closed is not so much an issue. The ALTER would initiate an asynchronous close event [same as ALCOBJ CONFLICT(*RQSRLS)], but that effects close only for locks held by pseudo-closed opens. Thus all RLA or non-cursor held locks will still prevent the ALTER.

The user would have to anticipate that the ALTER might have to wait to acquire the exclusive lock. The application would need
to release all such explicit or implicit allocations any time
it went in to a wait state. The ALTER would work in these gaps.
I agree that there would probably need to be some notification
method to the application that would induce it to enter a wait
state and drop all allocations of the file.

Agreed. And this would be similar to an approach for designing the application for HA switchover. If the goal is to minimize impact and all the work should continue [i.e. neither held awaiting ALTER completion, nor being ended] then the switch to an actively maintained copy of the database is ideal. Preventing jobs active in the application from starting any new OPEN activity just prior to ALTER [as signaled by coded event] or since ALTER [signaled by database close event, and likely some additional coding with its exit program] is not the easiest, but if the application can do that, then it is not far from being able to also switch over to an alternate copy of the database if one were being maintained.

However from the conversation so far [as continued in another thread, talk of program described data] it seems the OP may be using RLA, if not solely then in part, and so the SQL is just a subset of locking issues. And since the RLA does not have any event for close, the application would need to code the wait on semaphore itself for the same reasons; i.e. even if full closes could be effected by an event signaled to each opener across the system, the same asynchronous nature means that each notified job must also be prevented from progressing to its next open.

I believe rather than an approach of having a goal of trying to run mostly free of locks by closing often, the better goal is to define points in processing where the application would explicitly look for an outstanding request to /close up shop/. This is because full close and full open are expensive; that is of course the reason for the pseudo-closed cursors, to improve performance by eliminating unnecessary open\close activity. A notification signaled to each process running the application could effect turning on a /shutdown/ indicator, and the application having been coded to look there, would respond by shutting down and not allowing restart until the maintenance had completed. Again with having gone that far, it is just a small just to redirect the application to startup on an alternate copy of the database.

Regards, Chuck

CRPence wrote:

Dan Kimmel wrote:

The way to do this is straightforward.

One. Redesign your application so it doesn't hold a lock on the
file. The key verb is "hold". Your application acquires a lock
when it needs one and releases it as soon as it can.

Two. Redesign your application so it is insensitive to changes in the file.

With this done, an ALTER table or CHGPF can be done and your application will wait while the change takes place (the change
will have an exclusive lock) and then resume.

Several ways to keep from holding a lock: CLOSE the file within
your program after every use and OPEN it only when needed. Use
SQL SELECT statements which don't hold a lock. Use SQL CURSOR
for read only.

To make your application insensitive to changes: Specify LVLCHK
*NO on compile and be careful not to change existing fields
when adding a field. Use embedded SQL with naming the
individual fields (no *) in the SELECT statements.

A read-only cursor is of no assistance because an /open file/ is
an implicitly allocated file, preventing an exclusive allocation
of the file by an ALTER request. There is no SQL SELECT that
will avoid that implicit lock; the alluded timely close is still
the goal. Besides, a database application is unlikely to be
limited to inquiry-only, except in a DW\BI setting; unlikely to
be such a limited scope\concern of the OP.

The application would effectively need to be able to effect full-close for every data access which likely would be
prohibitively expensive. It would be better to code the
application to receive a signaled event to effect close; await a
semaphore of /OK to open/ as established by the changer, which in
theory, could be the open-wait timer set at the *FILE level in
the *FILE object or an override. The pseudo-close activated by
the database SQL would be acceptable, if not best, to implement
such an event. An event would be effected by the ALTER attempt
due to its effective ALCOBJ CONFLICT(*RQSRLS).

However due to timing of new opens by the application, that would
probably require coding to the QDB_... SQL close event exit
program to effect some delay or other discouragement of the
application to perform any new opens, before all pseudo-closed
across the system are completely full-closed; i.e. coding to the
exit program establishes the semaphore. Of course the exit
program is a feature of SQL access only; i.e. RLA does not, can
not, close its file in response to the SQL database close event.