Midrange News for the IBM i Community


Posted by: seewiz
file lock
has no ratings.
Published: 11 Jan 2013
Revised: 23 Jan 2013 - 1582 days ago
Last viewed on: 23 May 2017 (2898 views) 

Using IBM i? Need to create Excel, CSV, HTML, JSON, PDF, SPOOL reports? Learn more about the fastest and least expensive tool for the job: SQL iQuery.

file lock Published by: seewiz on 11 Jan 2013 view comments(11)

I have a program that will copy one file (file 1) to another (file 2) when the user is satisfied that the file is correct.  File 2 is being used in many different programs as IF, not UF.  These programs have a few reads/chains to File 2 that do not have the (N) for not locking the record.  The copy program is not able to copy file 1 to file 2 while these programs are running.  I thought if a file was opened as IF it wouldn't lock it.  Some of the programs also use SQL to access the file, which I also thought wouldn't lock the file.

Is my only recourse to use a USROPN on file 2 and thus reducing the window time the file is actually open and is locked?  Would this solution also work for the SQL statements?

Return to midrangenews.com home page.
Sort Ascend | Descend

COMMENTS

(Sign in to Post a Comment)
Posted by: Ringer
Premium member *
Comment on: file lock
Posted: 4 years 4 months 12 days 20 hours 59 minutes ago

> copy file 1 to file 2

What command? CPYF *REPLACE? If yes, that's basically a CLRPFM and the system needs an exclusive lock on the file. An IF won't lock individual records but it will lock the file. Do WRKOBJLCK MYFILE *FILE to see where it's locked. You might instead consider writing an RPG program to copy the records or use SQL.

Chris Ringer

Posted by: seewiz
Premium member *
Comment on: file lock
Posted: 4 years 4 months 12 days 20 hours 49 minutes ago

Will the SQL statement do a clear and then copy-in essence?  It doesn't need an exclusive lock to do this?  This particular file is a multiple member file.  Can SQL handle copying from one member to another member of the same file?

Posted by: DaleB
Premium member *
Reading, PA
Comment on: file lock
Posted: 4 years 4 months 12 days 20 hours ago

SQL statements would be:

DELETE FROM File2;
INSERT INTO File2 SELECT * FROM File1;

First you delete all the rows, then you "copy" all the rows. But it's working on rows (well, row sets), not on members. Other jobs with record locks will interfere, but your job does not need any exclusive locks on the member like a CLRPFM or CPYF would need. You could to this in a RUNSQLSTM, or embedded in RPG with /EXEC SQL.

The native RPG equivalent would be to declare File1 as IF, File2 as UF with A. Then in calcs write two loops. First loop reads and deletes all File2. 2nd loop reads all File1 and writes to File2. This is essentially what the two SQL statements are doing, but a lot more verbose. Again, the delete loop will be affected by record locks from other jobs, but you don't need exclusive member locks.

Posted by: seewiz
Premium member *
Comment on: file lock
Posted: 4 years 4 months 12 days 19 hours 22 minutes ago

Thanks for the help! 

Posted by: neilrh
Premium member *
Jackson, MI
Comment on: file lock
Posted: 4 years 4 months 12 days 18 hours 48 minutes ago

This whole process sounds like it carries a lot of risk. If folks are reading this file, do they expect the data to be there, what happens if it now cannot find a record because the delete step has run, but the insert step hasn't completed yet? What happens if someone is reading through the file and you delete all the data from under them?

Posted by: Ringer
Premium member *
Comment on: file lock
Posted: 4 years 4 months 12 days 18 hours 1 minutes ago

SQL cannot handle members unless you do an OVRDBF file first. Then SQL will use that given member. If you are doing mass deletes, I'd recommend REUSEDLT *YES on the table.

Yeah, I'm with neilster. How will other jobs react when the data suddenly disappears then reappears?

Chris Ringer

Posted by: bobcozzi
Site Admin ****
Chicagoland
Comment on: file lock
Posted: 4 years 4 months 12 days 17 hours 35 minutes ago
Edited: Fri, 11 Jan, 2013 at 11:22:31 (1594 days ago)

Dale's solution is best and if members are an issue, you can use members in SQL but you have to create an Alias first.

 

CREATE ALIAS  MYDATA/BOB  MYDATA/CUSTMAST (REGION6)

 

See the article here on MidrangeNews.com for details on accessing a member with SQL

http://www.midrangenews.com/view?id=1233

Posted by: SteveCCNJ
Premium member *
Egg Harbor Twp., NJ
Comment on: file lock
Posted: 4 years 4 months 12 days 17 hours 31 minutes ago

SQL can handle members as long as you create an ALIAS to the file that specifies the member.  Look in the SQL Ref. Guide.

 

CREATE ALIAS creates a persistent DB2 object so it only needs to be set up once (unless you want to CREATE and DROP it each time).

Posted by: Ringer
Premium member *
Comment on: file lock
Posted: 4 years 4 months 12 days 16 hours 52 minutes ago
Edited: Fri, 11 Jan, 2013 at 12:31:46 (1594 days ago)

Yeah, I knew that actually. I think it's easier to do the OVRDBF first instead of creating all the ALIAS'. From a programming perspective, your SQL statement won't change, just specify the file name and not one of the many possible ALIAS names. Just my 2 cents.

Chris Ringer

PS: In my case, I don't know the member names in advance. I create and use them based on date/time for archiving previous FTP data, SNADS data, etc.

 

Posted by: SteveCCNJ
Premium member *
Egg Harbor Twp., NJ
Comment on: file lock
Posted: 4 years 4 months 12 days 14 hours 8 minutes ago

Personally, I break out in a rash everytime somebody says "multi-member file'.

;-)

Posted by: seewiz
Premium member *
Comment on: file lock
Posted: 4 years 4 months 9 days 15 hours 42 minutes ago

This is the first company I have worked for that has multi member files.  And thankfully, it is only 1 file.  Thanks for the help.