RE: Run SQL Scripts and File Locks

Hi Charles
Your CLRPFMSQL command intrigues me
Are you able to publish it?
It sounds like something I have contemplated doing for a while

Alan Shore
Programmer/Analyst, Direct Response
E:AShore@xxxxxxxx
P:(631) 200-5019
C:(631) 880-8640
"If you're going through Hell, keep going" - Winston Churchill

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Charles Wilt
Sent: Thursday, May 26, 2011 9:03 AM
To: Midrange Systems Technical Discussion
Subject: Re: Run SQL Scripts and File Locks

Make sure you select "Clear Messages" and "Close all results" form the edit menu.

However, it's possible there would still be a problem with locks due to the DB's "pseudo-close" cursor functionality. This is a common issue with any SQL interface...actually I'm surprised that STRSQL doesn't seem to have it.

There's been lots of discussion and links pasted about regarding the locks from "pseudo-close"...

But basically, even though your app, Run SQL Scripts in the case, tells the DB to close the cursor, the DB may reply affirmative yet not actually completely close the cursor. This way if it gets the same of a similar enough statement again, it can more quickly satisfy the request.

There are three solutions
1) Quit using CLRPFM - it's designed only to be used when you can guarantee exclusive access to a file. That's hard to do now-a-days and if you're allowing the file to be queried at any time you simply can't make the guarantee CLRPFM requires. I created a CLRPFMSQL command that uses SQL DELETE to delete all records in a file. At
v5r2(?) and higher the SQL DELETE usually performs just as fast as CLRPFM as it will do a clear if possible, or a change file, or as a last resort individually delete the rows.

2) Do an ALCOBJ WAIT(xx) CONFLICT(*RQSRLS) where xx is non-zero before attempting the CLRPFM.

3) The system will automatically hard close a pseudo-closed cursor if an operation, such as CLRPFM, that requires exclusive locks is requested. However, your job must be willing to wait. Check the DFTWAIT of the job along with the the WAITFILE ( and WAITRCD?) parms of the file.

Note that option 3 may not be a "solution" per say for CLRPFM, but it might help with other operations..per this thread.
http://archive.midrange.com/midrange-l/200902/msg00534.html

HTH,
CHarles


On Wed, May 25, 2011 at 6:17 PM, Kurt Anderson <kurt.anderson@xxxxxxxxxxxxxx> wrote:

When I bring up a file in STRSQL via Select, the moment I return to the statement the lock is gone.

In Run SQL Scripts, once a statement is ran, I don't see how to get out of that file short of performing another SQL Statement or closing the program.  (There isn't a way to close out a statement.)   In addition to that, if I open up a statement that was run into its own window b/c I want to view the result set for that statement later, that seems to keep the file locked (open, as you say) regardless if I actually run another statement or not.

Why is Run SQL Scripts keeping the open?  Yes, thank you, a file that is opened can't be cleared - that's obvious.  What's not obvious is that Run SQL Scripts is keeping the file open.  How should I know that?  How can I prevent that?

STRSQL's exit message in 7.1 that suggests that we should be using Run SQL Scripts.  We saw that once before, and now SEU is being stabilized.  Maybe that's the future for STRSQL, so I'd like to get a good understanding of Run SQL Scripts, and quite honestly I've been meaning to play with it for quite some time.

Rob - turns out FOR FETCH ONLY doesn't change the situation.  The job still keeps the file open.

Thanks,

-Kurt

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of CRPence
Sent: Wednesday, May 25, 2011 4:28 PM
To: midrange-l@xxxxxxxxxxxx
Subject: Re: Run SQL Scripts and File Locks

On 25-May-2011 13:31 , Kurt Anderson wrote:

Last weekend we upgraded to a Power 720 from a 520.
After using and exiting STRSQL for the first time in a session, a
message appears suggesting to give Run SQL Scripts a try, so I did.

The utility has its pros and cons, and I'm trying to use it so I can
get used to it. However today a job ran into an issue clearing a file
because apparently the SQL connection had the file in use. I had only
been doing SELECT statements in the SQL utility, so now I'm concerned.
I showed a couple of our power-users this tool, but if it's going to
end up keeping a file in use, I'm going to have to take it away.

Any thoughts on the matter?

  A file.mbr open in Job-A [except as psuedo-closed by SQL] should prevent that same file.mbr from being cleared [by CLRPFM] in Job-N irrespective of language used to open the file.  For what reason would the Run SQL scripts not be expected to effect the same?

Regards, Chuck
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe,
unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take a moment to review the archives at http://archive.midrange.com/midrange-l.

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe,
unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take
a moment to review the archives at
http://archive.midrange.com/midrange-l.

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take a moment to review the archives at http://archive.midrange.com/midrange-l.