Help!! Physical file accidentally deleted, logicals still there. Is there hope?

35 pts.
Logical Files
Physical File
I made a HUGE mistake and realized it way too late!! I needed to do a copy of a physical file with *NOMAX on it for the errors because there were 10 corrupted files. Anyway, things went wrong, we restored from backup. Things were back to normal, or so I thought. I got back on the system a couple weeks later to discover that file I restored from backup, never did restore. I restored the entire library, but since that file was corrupt, it wasn't backing up and thus, not on the system. I realized this a couple weeks later. Mind you, this file is used multiple times daily and they didn't have problems. I do know that their system is mirrored. I can see the logical files and see new data in them. Did a temporary file get created or is it somewhere I'm not looking. I'm desperate!! Please help!!

Software/Hardware used:
as/400 iseries

Answer Wiki

Thanks. We'll let you know when a new response is added.

If you see data when viewing a LF but not when view the PF, it means that the LF is attached to a different file.
Do a DSPFD of the LF and it will show you what PF it is attached too.
You should also check the PF to see in there are any LF attached to it.

You may be able to just delete the PF and move the PF that is attached to the LF back into the library where it belongs.

Or the file is really there but you cannot see it — don’t have authority to it.

Do dspfd on the logical that will indicate the physical file and the library.
If the logicals are native logicals..created with CRTLF (not SQL create view) .. then you cannot delete the physical without deleting the logicals first.


Generally, interactive SQL is good at sniffing these out — the fields in error will show ++++ instead of a value but you’ve got to do a manual scan of the records — hope it’s not a long file.
Select * from myfile



Another option to find the bad data is to do a CPYF to a new file.
It should crash when it hits the bad data..
Then you can find out how many records it copied and go back the the original file and look at the records that come after that point.
You should be able to see the ones with bad data. Generally they will be all together.
Now you can try to update the the bad values with SQL.
If that does not work, you can use multiple CPYF statements to build a good file omitting the bad records.
First copy from rcde #1 to the Rcd number fo the last good record.
Next do CPYF *Add selecting from Rcd# (1st one after the last bad record) to *END
Now either buld new LF over the new file, of do another CPYF from youy new file to repalce the corrupt one.
If you do this porcess be sure no updates are being done while you are fixing as you may lose those records.
Try CMPPFM to compare the member of the file with the bad records to the one without the bad records.


This has worked well for us in the past:
To recover as many undamaged records as possible, do the following:

1 On the IBM® OS/400® or IBM® i5/OS? command line, type the following:


Press the Enter key. The OVRDBF command ensures that the file to be
copied is processed sequentially, one record at a time.

2 On the OS/400 or i5/OS command line, type the following:


Press the Enter key.

a Save QTEMP/x to a SAVF or tape as a backup in case QTEMP is lost.
b If there are problems with the copied from file, consider
manually creating the file that you are going to copy to. For
example, the CPYF command would use CRTFILE(*NO) rather than
CRTFILE(*YES). In addition, the SQL statement CREATE TABLE could
also be used.

3 On the OS/400 or i5/OS command line, type the following:

DLTF liba/x

If the file cannot be deleted because of logical files, rename the
original file to another name in the same library. Press the Enter

4 On the OS/400 or i5/OS command line, type the following:


Press the Enter key. If the original had logical files, rebuild the
same logicals over the new copy of the file.

5 Did you RENAME the original file in step 3? If NO, then STOP. You
do not need to do step 5. If you did rename the file during step 3,
on the OS/400 or i5/OS command line type the following:

DLTF liba/all_logicals_over_renamed_physical and DLTF

The CPYF command creates a new copy of the file that contains all of
the records that could be accessed. The damaged records are noted in
the job log. The value indicated for the ERRLVL parameter should be
considered a threshold value to be set by the user to indicate how
many damaged records of data are to be tolerated before replacement
from a backup copy is deemed necessary. Exceeding this threshold
limit terminates the CPYF command.

After the CPYF command has completed, the user should compare the
total number of records found in the new version of the file with
that of the old. If this value is the same for both files, there
appears to have been no loss of data. If this value is found to
differ between the two files, it might be necessary to recover those
records missing from the new version of the file through data entry,
backups, and so on.

If it is necessary that the file be processed as a direct file, the
deleted records must also be copied to the new file. This is
accomplished by changing the value of the COMPRESS parameter to *NO.

The CPYF command uses the parameter FROMRCD(1) to avoid possible damage in
the access path.

Note: There may be other things that need to be added back to physical
file, such as triggers and constraints.


Discuss This Question: 6  Replies

There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.
  • Kristy1129
    Shew!!!!!!!!! (Breathing a BIG sigh of relief!!!!) Thank you SOOOOOOOOOOO Much!! You just saved my job!! WOW!!! Thank you!! Thank you!!! Thank you!!!
    35 pointsBadges:
  • CharlieBrowne
    So, I'm curious. Was it an authorization problem or was the a problem with the linking of the PF & LF?
    62,340 pointsBadges:
  • Kristy1129
    Well, there was informatiopn in the LF from today, so I new information was getting there somehow. duh, right? lol Anyway, we had a problem when a LF of another file got recompiled and I had to restore from backup. I renamed the library on the system and restored from tape. Well, the problem all started when the PF wouldn't save to the tape because it had errors, so when I restored, it wasn't there and I didn't realize it until much later. I used that command (DSPFD of the LF) and saw that the LF was still attached to the file in old library (the one that I renamed. I still don't know how to figure out which 10 records are corrupt, but at least they haven't lost an entire month of data! SHEW!! Do you know how to figure out which records are corrupt? Thanks again!!!
    35 pointsBadges:
  • Kristy1129
    The file is extremely large, over 100,000 records. We don't have SQL either unfortunately. I was going to try and do the copy until it stops to figure out where the corrupt record is. Thank you! I don't know if corrupt data will show up in a query if I figure out which records they are. I'm pretty sure it would puke if I tried to go to that record in the dfu. How I was doing the copy to get the corrupt data out of it was to do a CPYF and then put *NOMAX on the errors allowed field. That did get rid of the 10 corrupt records, but I don't know what data I lost doing it that way.
    35 pointsBadges:
  • Voodoovw Voodoo
    1,735 pointsBadges:
  • TomLiotta
    Essentially every AS/400 (and iSeries and System i) has SQL. What you probably don't have is the SQL Dev Kit product, which allows you to use the SQL preprocessors of the compilers plus some nice options like green-screen STRSQL interactive SQL and Query Manager interactive interfaces. Also, all AS/400s (etc.) include REXX which handles SQL without the SQL Dev Kit. And AS/400s at recent releases with Qshell should have the db2 utility that can execute SQL statements. Also, iSeries Navigator should allow SQL entry. And RUNSQLSTM and STRQMQRY as well as SQL CLI are there. IOW, SQL is almost guaranteed to be available to you. Just some handy interfaces would be missing. Tom
    125,585 pointsBadges:

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

Thanks! We'll email you when relevant content is added and updated.


Share this item with your network: