Midrange News for the IBM i Community


Posted by: seewiz
file lock
has no ratings.
Published: 11 Jan 2013
Revised: 23 Jan 2013 - 1703 days ago
Last viewed on: 22 Sep 2017 (3153 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)

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 8 months 11 days 11 hours 25 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 8 months 11 days 11 hours 15 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 8 months 11 days 10 hours 26 minutes 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 8 months 11 days 9 hours 48 minutes ago

Thanks for the help! 

Posted by: neilrh
Premium member *
Jackson, MI
Comment on: file lock
Posted: 4 years 8 months 11 days 9 hours 13 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 8 months 11 days 8 hours 26 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 8 months 11 days 8 hours 1 minutes ago
Edited: Fri, 11 Jan, 2013 at 11:22:31 (1715 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 8 months 11 days 7 hours 56 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 8 months 11 days 7 hours 17 minutes ago
Edited: Fri, 11 Jan, 2013 at 12:31:46 (1715 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 8 months 11 days 4 hours 34 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 8 months 8 days 6 hours 8 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.