PDA

View Full Version : Record Locking in Embedded SQL



nandas400
February 14th, 2008, 04:20 PM
Hey Buddies

I have opened a file File1 for Update and I use an embedded SQL select to retrieve records from File1.Will the SQL select statement will lock those records which have been retrieved?


If yes how to select records without the lock on the file because I do not update all the records that have been retrieved.

Thanks in advance.

Regards
Nanda.

kitvb1
February 14th, 2008, 04:26 PM
use: for fetch only on your sql statement

nandas400
February 14th, 2008, 04:33 PM
Thanks Kit for the reply.


So I need to use like FETCH ONLY NEXT FROM CURSOR1?


Is there anyway to declare Cursor as read only?

Thank You

Regards
Nanda.

jamief
February 14th, 2008, 04:42 PM
--or--



SELECT EMPNO, LASTNAME, SALARY
FROM EMP
WHERE SALARY > 10000.00
FOR READ ONLY;

nandas400
February 14th, 2008, 04:47 PM
Thanks Jamie for ur help:)

mjhaston
February 17th, 2008, 10:55 AM
--or--



SELECT EMPNO, LASTNAME, SALARY
FROM EMP
WHERE SALARY > 10000.00
FOR READ ONLY;





I've been meaning to ask about this because I have an object locked from a CGI-RPG program and all I am doing is reading from it. I do have "For Read Only" declared and still I lock it.

Mercury
February 18th, 2008, 08:32 AM
I am fairly sure that you don't get the lock if you use a view instead of the table.

mjhaston
February 18th, 2008, 10:56 AM
I switched it to a logical view and now my lock is on the logical!

I'm doing a very basic ...


select fields

from file

where simple condition

for read only

Mercury
February 18th, 2008, 12:14 PM
Is "file" in this example above created by a CREATE VIEW stm ?

mjhaston
February 18th, 2008, 12:30 PM
No, it's a LF created with DDS.

arrow483
February 18th, 2008, 02:17 PM
What kind of lock are you seeing ? A file opened for update WILL lock any record read, until (1) the record is ypdated, (2) another record is read, or (3) UNLOCK commnd is processed. Any "read" open will NOT lock the file. You may have to UNLOCK the record before SQL can process it.

Are you using commitment control or journalling?

mjhaston
February 18th, 2008, 03:13 PM
Opt Job User Lock Status Scope Thread
CGIDEV2 QTMHHTTP *SHRRD HELD *JOB
*SHRRD HELD *JOB
*SHRRD HELD *JOB



Here's what I'm doing in the program:

Suppose to close cursor at the end of program, also ingore case during comparisons.

Exec Sql
Set Option closqlcsr = *endmod,
srtseq = *langidshr;


I do this to get an overall record count.


// build sql string
sqlString = whereString + groupString + havingString + orderString;

Exec Sql
Prepare SQL2 from :sqlString ;
Exec Sql
Declare C2 Cursor for SQL2;
Exec Sql
Open C2;
Exec Sql
Fetch from C2 into :total;
Exec Sql
Close C2;



main loop of program to spit out records:


// build sql string
sqlString = whereString + groupString + havingString + orderString;


Exec Sql
PREPARE SQL FROM :sqlString;

Exec Sql
Declare C1 Asensitive Scroll Cursor for SQL;

Exec Sql
Open C1;

Exec Sql
Fetch Relative :X from C1 INTO :SQL_DS ;



Interesting, the first time thru the program the lock appeared and went away when the program was finished. After that the second run the lock stuck.

I'm not doing anything with commitment control or journalling that I'm aware of.

mjhaston
February 18th, 2008, 08:26 PM
Now I'm frustrated. I thought you pointed out a glitch of mine when you mentioned the lock waiting for another record to be read. I did have a situation where I was jumping out of my DO loop before the EOF because I'm only reading 20 records at a time for the grid. So I put in an extra Close C1.

Same results. I've even made sure both times I access the file it's "For Read Only".

First time into the CGI-RPG program I see the lock appear and disappear. Second time I run it I get the three locks above.

Mercury
February 19th, 2008, 04:10 AM
Build a genuine view (CREATE VIEW) and give it a try.
Anyway, if the underlying table is already open for update, using a view would'nt solve the case.
Additionally try also
Set option = ALWBLK(*ALLREAD)

arrow483
February 19th, 2008, 10:11 AM
The SQL selects, whether reading a view or a LF, are not going to lock any records. The file is going to show a *SHRRD lock anyway, but that is not a record lock. In your post, it looks like locks are held - this is normal. Any record lock must be coming from somewhere else. Where are you seeing a record lock ?



Opt Job User Lock Status Scope Thread
CGIDEV2 QTMHHTTP *SHRRD HELD *JOB
*SHRRD HELD *JOB
*SHRRD HELD *JOB

Mercury
February 19th, 2008, 10:21 AM
Yeah Arrow that's also what I think. This lock should come up from another program in the call stack likely in the CGI program.

nandas400
February 25th, 2008, 04:25 PM
Thank You Guys for your thoughts on this.

Regards
Nanda