> 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

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?

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.

Thanks for the help! 

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?

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

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

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).

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.

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

;-)

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.