RE: SQL select seems to be creating a lock on a file -- MIDRANGE-L

Thanks Rob
But when I change the embedded SQL to
exec SQL Select count(*) Into :Reccount
from FILEA for read only;
the editor replies
FOR FETCH ONLY clause not allowed.
Then taking an F1
Additional Message Information

Message ID . . . . . . : SQL0109 Severity . . . . . . . : 30
Message type . . . . . : Diagnostic

Message . . . . : FOR FETCH ONLY clause not allowed.
Cause . . . . . : One of the following conditions was not allowed:
-- Embedded SELECT statements cannot include the FOR UPDATE clause, the
FOR READ ONLY clause, the FOR FETCH ONLY clause, the OPTIMIZE clause, or the
UNION, EXCEPT or INTERSECT operator.
-- SELECT statement used in cursor declarations or subselects cannot have
an INTO clause.
-- CREATE VIEW statements may not have an INTO, ORDER BY, FOR UPDATE, FOR
READ ONLY, FOR FETCH ONLY, or OPTIMIZE clause.
-- INSERT statements may not have an INTO clause in a subselect, a FOR
UPDATE, FOR READ ONLY, FOR FETCH ONLY, or an OPTIMIZE clause.
-- WHERE CURRENT OF cursor clause is not allowed in statements processed
in interactive SQL or statements processed by the RUNSQLSTM command.
More...

Alan Shore
E-mail : ASHORE@xxxxxxxx
Phone [O] : (631) 200-5019
Phone [C] : (631) 880-8640

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of rob@xxxxxxxxx
Sent: Tuesday, July 23, 2013 8:53 AM
To: Midrange Systems Technical Discussion
Subject: RE: SQL select seems to be creating a lock on a file

Then upgrade.

Or you could try just
exec SQL Select count(*) Into :Reccount
from FILEA
for read only
with NC;

The "for read only" may do the trick.

The "with NC" you probably already have covered with your exec sql Set Option Commit = *None;

Here's the difference:
"for read only" doesn't lock the data on the select. Thus allowing the other program to update data.
"skip locked data" says if the other program has data locked while it is updating it then ignore that row.
One handles your updates. The other deals with the other job's updates.

Rob Berendt
--
IBM Certified System Administrator - IBM i 6.1 Group Dekko Dept 1600 Mail to: 2505 Dekko Drive
Garrett, IN 46738
Ship to: Dock 108
6928N 400E
Kendallville, IN 46755
http://www.dekko.com

From: Alan Shore <ashore@xxxxxxxx>
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>,
Date: 07/23/2013 08:29 AM
Subject: RE: SQL select seems to be creating a lock on a file
Sent by: midrange-l-bounces@xxxxxxxxxxxx

Thanks for the reply Rob
Skip locked data seems to be available from V6r1 - which we are not on

Alan Shore
E-mail : ASHORE@xxxxxxxx
Phone [O] : (631) 200-5019
Phone [C] : (631) 880-8640

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx [
mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of rob@xxxxxxxxx
Sent: Tuesday, July 23, 2013 8:14 AM
To: Midrange Systems Technical Discussion
Subject: Re: SQL select seems to be creating a lock on a file

Skip locked data
may not be an option on V5R4. It's rather new.

End of program support for V5R4 is only 69 days away. Less than the
expiration date for a software license key.
http://www-947.ibm.com/systems/support/i/planning/upgrade/suptschedule.html

Rob Berendt