Clearing a PF record on AS/400

320 pts.
Tags:
CHAIN
PF
Physical File
RPG
I'm chaining a field to a unique keyed PF. I'm getting a not found indicator so I'm trying to write the record but I receive a duplicate key error. How this can be I do not understand. A colleague suggested that a field (or fields) within the record might be a key of other LF's over the file and I should clear the record before trying to write a new one. Problem is; the file has 80 or so fields. How do I clear all these fields easily or is there some way to clear the record entirely in one op?

Answer Wiki

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

You can do it using a SQL (command STRSQL) like this :

Update FILE set FIELD = ‘ ‘ where SOMEFIELD = keyvalue

FILE = your file
FIELD = your field or fields in the unique key.
SOMEFIELD = the field you want to clear.
keyvalue = the new value (zeros or blanks, it depends of the type of your field).

The not found indicator maybe is because you don’t have the K(eyed) value in the file declaration row. This is an example>
FSOMEFILE if E K DISK

If you don’t put the K letter, the keyed file return a not found indicator when you do a chain.

Hope this help you!

Discuss This Question: 5  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.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • Gilly400
    Hi, You should be able to use the CLEAR opcode to clear all the fields in a record format. Regards, Martin Gilbert.
    23,730 pointsBadges:
    report
  • philpl1jb
    Odd problem - clear will clear all fields. But you need to determine which logical/physical files declare this file as unique, the other logicals don't matter for this analysis. Leaving some fields blank or zero might cause your error if those fields are key fields for a unique key! The command DSPDBR will show all the logicals linked to a physical file so you can search for the file(s) with unique key rules.
    50,565 pointsBadges:
    report
  • Floyd02
    I'm using RPG3 and although I know some SQL, I do not know how to add the statement to my program, or if it can be added, so I tried the CLEAR option as suggested but I still get the "Duplicate key on access path" error. After I wrote the initial post last night, I tried copying the file to my lib, clearing it, then tried to write a new record to it and I received the same error message! There is another message in the joglog that states "Duplicate record key in member F4101LC." I'm going to assume that this is the unique LF that's causing the problem. So I will try to determine which field it is. Thanks for your help.
    320 pointsBadges:
    report
  • philpl1jb
    The copy that you made to your library won't have any logicals on it. But You've got the answer "Duplicate record key in member F4101LC" this is a multi-member file. Members are record containers within the file. If you do DSPFD you will discover the list of members. When you do a chain you probably are attempting to get a record in the first member only. But when you do a write the DBMS is enforcing the UNIQUE across all members. You want to chain into this file by an access path that has the OVRDBF command set to *ALL members. I think that will find the record. But you want to write to this file using a specific member. Perhaps a second access path. I don't remember ever encountering a UNIQUE constraint placed on a multi-member file.
    50,565 pointsBadges:
    report
  • Floyd02
    Yes, that was my answer. I found that a field I was clearing was a unique key field for that LF
    320 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.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

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

Following