COBOL Logical File Rebuild Necessary?

110 pts.
Tags:
AS/400 logical file
AS/400 physical file
AS400 cobol
I apologize this question was not well written - please see the questions again (added question 2a).
Thx.

Hi, 

I have an AS400 with two physical files on it... 

1) INVOIH Invoice Header File 
2) INVOID Invoice Detail File 

40K records in the header file and 40K in the detail file on 3/22 (yes 1 to 1 don't ask). On 3/25 20K records are missing from the detail file (no joke records 10,001 to 29,999 are just gone). Only two programs in this ERP system access this file and have lines of code that delete from this detail file. Neither of them ran. Doesn't matter (for now) how it happened - need to get the detail file put back together. 3/22 backup of the detail file has been restored with another name. I simply want to get the missing 20K back in the production file. 

NOTE: the detail file has one logical file associated with it INVDCOI (invoice order file). 

I know the index fields of the detail file and I know the index values of the missing records in the restored file. 

So questions and I'm not a 400 expert or a COBOL programmer... 

1) Is there a way to pull the missing records out of the restored file and just concatenate them in to the production file (example in UNIX I could just use "cat test >> test1", etc)? 

2) If from question 1 I can just concatenate the records into the production file will this cause any COBOL program problems? 

2a) And if I can concatenate into the physical file do I need to rebuild the logical file (or do anything to it for that matter)?  

3) Or do I need to have a COBOL program written to read in the records from the restored file and write them to the production file? 

For informational purposes here is the File Section of the COBOL program related to the files mentioned above... 

SELECT INVOICE-HEADER-FILE ASSIGN TO DISK-INVOIH 
ORGANIZATION IS INDEXED 
ACCESS IS DYNAMIC 
RECORD KEY IS IH-RECORD-KEY. 

SELECT INVOICE-DETAIL-FILE ASSIGN TO DISK-INVOID
ORGANIZATION IS INDEXED
ACCESS IS DYNAMIC
RECORD KEY IS ID-RECORD-KEY.

SELECT INVOICE-ORD-FILE
ASSIGN TO DATABASE-INVDCOI
ORGANIZATION IS INDEXED
ACCESS IS DYNAMIC
RECORD KEY IS EXTERNALLY-DESCRIBED-KEY WITH DUPLICATES. 

Thanks in advance for any help!!!
1

Answer Wiki

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

Another option would be to do a CPYF with the add and conditions, like this

CPYF FROMFILE(MYLIB/RESTORED)
TOFILE(PRODLIB/DETAIL) MBROPT(*ADD)
INCREL((*IF MYFIELD *EQ ‘3/25’)) 

the other option, if it’s a block of
continuous missing data is by RRN

CPYF FROMFILE(MYLIB/RESTORED)
TOFILE(PRODLIB/DETAIL) MBROPT(*ADD)
FROMRCD(10001) TORCD(30001)

Discuss This Question: 12  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.
  • Splat
    I've never tried this on a logical file but, assuming your logicals have unique keys, you might be able to copy from the restored version to the production version using the MBROPT(*UPDADD) parameter on the CPYF.
    12,835 pointsBadges:
    report
  • RossHowatson
    This is not a Cobol or an Index problem.  

    If you browse the file(table) via the DSPPFM command and go to record(row) 9999 and then use the +1 command a few times is there a sudden jump in the record(row) number (YES/NO)?  

    YES:  If yes then the records(rows) have been deleted but it may be possible to undelete these records(rows).  You may even have the untility on your system to do this or if not then search for the utility via Google.  I took the free utility command/program and created a REXX program to loop through all records(rows) in a file(table) and created my own new file(table) which containe the deleted records(rows) in question.  I then could merge the with the undelete the records(rows) in a table these records(rows).

    A faster method is to restore a backup of the file in question into a different library.  Then you can use the CPYF command and ADD the records(rows) based upon the record number range in question.  

    The only way to practice the command is to save the file(table) to a save file (or tape) and restore this into another library.  A straight copy file (CPYF) should remove the deleted records(rows) when you create the file(table) in a new library(collection).  

    NO:  If no then the records(rows) are flagged for deletion and are being ignored by the programs in question.  Find the flag for the If flagged for deletion then simply unflag the records(rows) in question.  

    Final question is how did the records(rows) get deleted?  If not by a program then the fastest way of deleting the records(rows) is by using the SQL Delete statement.  The records(rows) could have been deleted by someone making a copy of the file(table) and then coping back only the records(rows) in question.  
    630 pointsBadges:
    report
  • TheRealRaven
    Does the restored file have all of the "deleted" rows? Are the "deleted" rows in the physical row positions 10,001 to 29,999, or are those logical row positions?

    As noted above, this seems unrelated to COBOL or logical files. Depending on answers to previous questions, this may be a trivial problem.

    Is the file that's missing rows journaled?
    32,480 pointsBadges:
    report
  • smithmt052
    That sounds pretty simple.  So if I do this and got the physical file complete would I need to do anything to the logical file?

    Thanks

    110 pointsBadges:
    report
  • smithmt052
    Last post/comment was referring to this solution...

    CPYF FROMFILE(MYLIB/RESTORED)
    TOFILE(PRODLIB/DETAIL) MBROPT(*ADD)
    INCREL((*IF MYFIELD *EQ ‘3/25’)) 

    the other option, if it’s a block of
    continuous missing data is by RRN

    CPYF FROMFILE(MYLIB/RESTORED)
    TOFILE(PRODLIB/DETAIL) MBROPT(*ADD)
    FROMRCD(10001) TORCD(30001)
    110 pointsBadges:
    report
  • smithmt052
    In regard to the reply above starting with "This is not a Cobol or an Index problem."  

    No the records are completely gone - I wish they were soft deletes.  Thanks for the tip on practicing with the CPYF command.  This is the approach I'm going to take unless you tell me this is easier.  You mentioned that the records could have been deleted via an SQL statement.  Would it not be possible to run an SQL INSERT statement to get the deleted data from the restored file back into the production file or do I have to use this CPYF command?

    As for how the records disappeared that remains a mystery.  Unfortunately, this very old ERP system does not log anything that it does.   No operating system level journaling is done on the system either.  Only thought at this point is someone did something accidentally.

    Thanks
    110 pointsBadges:
    report
  • smithmt052
    In regard to this response "Does the restored file have all of the "deleted" rows? Are the "deleted" rows in the physical row positions 10,001 to 29,999, or are those logical row positions?"...

    No unfortunately nothing is journaled - sounds like I can use this CPYF command though or maybe an SQL INSERT statement depending on the response to my SQL INSERT question above.  Based upon the one response it sounds like a DELETE SQL statement can be run against a COBOL flat file.  I'm just not sure if I can do an INSERT SQL statement (I would think so but two people have mentioned CPYF).
    110 pointsBadges:
    report
  • RossHowatson
    Find a library called UNDEL either on your system or via Google and get this on your system.  In this library should be a program called UNDEL2.  

    Save to a save file the detail file and restore this to a library called TESTLIB.

    Use the following REXX program to undelete one row at a time from the parent file.
    630 pointsBadges:
    report
  • RossHowatson
    Two typos on line 1 and 4 put at the end of each line the */

    630 pointsBadges:
    report
  • RossHowatson
    /* Override REXX StdIn to be physical file. */
    "OvrDbF StdIn TESTLIB/INVOID INVOID"

    /* Add to library list UNDEL so command will work. */
    "Addlible UNDEL *FIRST"

    Say "UnDelete Detail Row"
    CALL clearscreen

    MissRecNum = 0
    TotalMissing = 0

    /* Read first record. */
    Parse LineIn CurRecNum

    Do WHILE ( CurRecNum <> ''  )
       MissRecNum = MissRecNum + 1
       DO while CurRecNum > MissRecNum
          "UNDEL2 FILE(TESTLIB/INVOID) MBR(INVOID) RCDNBR("MissRecNum") ACTION(*UPD)"
          MissRecNum  = MissRecNum +1
          TotalMissing = TotalMissing + 1
       End
    /* Read next record. */
       Parse LineIn CurRecNum
    End

    Say "Total Missing Rows is: " TotalMissing


    say "End of UNDELRO2"
    EXIT      /* End of Program */


    clearscreen:
    TRACE N
    Do linefeed = 1 to 23
       Say ""
    End
    Return
    630 pointsBadges:
    report
  • GregManzo
    Just add the records via one of the methods already described. The system will ensure the logical is kept in line. If you *really* wanted to you could re-org the PF (RGZPFM cmd) but I wouldn't bother unless RRN was important to you applications (which implies other potential problems, but lets not go there today).
    2,905 pointsBadges:
    report
  • TheRealRaven
    A CPYF command might be preferable, depending on specific file definition. An older ERP app is likely to use older file definitions, and lack of journaling makes a more modern SQL table definition less likely.

    SQL INSERT... FROM... should also work if the sub-SELECT can be reliably designed.

    Recovery from a journal would be best, but...? A journal would also tell you essentially exactly how the deletions happened.
    32,480 pointsBadges:
    report

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.

Following

Share this item with your network: