Bad data in as/400 database.. cant display record

134495 pts.
Tags:
AS/400
AS/400 database
DFU
Hex
We have a user that insists on using cut and paste to enter data into our system. The problem is bad hex/ascii chars are causing errors trying to retrieve the data.

If you try to edit the data using STRDFU it gives the message "The retrieved record contains invalid data." To make things worse you cannot F-23 the record to delete it.

The only way we have found to fix our file is copy from RRN 1 to just before the bad record to a temporary file. Then we copy to just after the bad recoed to the end of file to the temp with the *add option. We then copy the temp file to our production file *replace. To complicate things this has to be done off hours and the file is used constantly. This means that customer is dead in the water until the next day.

Is there a way to edit the database file in a hex format?

Some of the bad chars they are using are elipse( a single char size ...), tab and carriage return characters to name a few.

Any help is greatly appreciated.

 

Asked: Last updated:
Related Questions
1

Answer Wiki

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

You can write a program to go through the file and update the records.
Use %XLATE to covert the bad characters to blanks.

If you kinow the record number or some other key, you can have your program receive that as a parm and just do the update to the one record.
——————
Check this out:
http://www.rpgiv.com/rpgivforum/view?SID=20090603062122245792&NRL=12&KW=hex
Phil

ALSO …
FIRE THE USER … OR BILL HIM / HER OR THEIR DEPARTMENT FOR THE
TIME TAKEN TO FIX THE RECORDS …
I’M KIDDING, OF COURSE, ABOUT THE FIRING …BUT THERE IS A REAL USER ISSUE HERE …
AND THE FACT THAT IT’S A BUSY FILE COULD CAUSE A SERIOUS PROBLEM …
ALSO SEE THE NEXT ANSWER …

BOTTOM LINE … ONE WAY OR ANOTHER THIS FAULTY DATA ENTRY HAS TO STOP …
Craig Baxter

—————–

If input is occurring in RPG you will want to use the XLATE on the incoming data.
The page referenced above gives the hex codes in and hex codes out to correct for cut and paste
values that don’t work on the 400. If you’re in cobol you will have to make something similar.
If the data is coming in directly from web applications then you will have to fix data fields after creation
or use a before trigger function to correct the incoming data.

Phil

Discuss This Question: 9  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.
  • Vatchy
    If you have SQL on your system and you know the record key you can use the SQL "UPDATE" command to change that particular record.
    1,415 pointsBadges:
    report
  • Yorkshireman
    how are they getting the data into the system? If it is a character screen input to DB2 land, then add some validation. If they are pasting into files in other file systems in the IFS, then you may need a validatiuon program to check and edit those files. What sanctions do you have against the user? such a manual recovery to a production system just cannot be tolerated.
    6,085 pointsBadges:
    report
  • NullFields
    I agree with Yorkshireman. You need to stop the bad data from getting into the table. Applying validation to the data entry is the best way. The closer that validation is to the database, the better. You could apply check constraints to the columns or a DB trigger that would catch bad data no matter what mechanism is used to enter the data.
    880 pointsBadges:
    report
  • Splat
    It's been some time since I've done this and things may have changed but I think that you can use UPDDTA to remove the record in question. When you receive the The retrieved record contains invalid data message, do another F23 - if memory serves that will lock the record for update & a final F23 should remove it. Or that's how it worked for me in the past. If that doesn't work, see if you can F23 the user.
    12,915 pointsBadges:
    report
  • Koohiisan
    I'm with Vatchy, SQL saved us many a time. We used to get invalid data (hex stuff) from scanners every now and then, and we were able to do a simple update. Our barcode field was about 15 chars and was stored in a 20 char field. So, we did an SQL 'UPDATE file SET field=LEFT(field,15)'. A few minutes later all was well! So, even if you can't identify the exact record to do an SQL update against it, maybe you can just try to 'normalize' that field in the whole file. Just my $0.02.
    5,045 pointsBadges:
    report
  • TomLiotta
    This isn't a user issue -- it's an application issue. Users can't be expected to know that a carriage-return, nor any other special/control character, is going to make it into a database record. The interface shouldn't allow it to happen. Fix the interface. And maybe even thank the user for exposing a potentially serious flaw in the design/implementation. If there is no validation, how can any of the data be trusted? Users can simply put whatever they want in? Usually, the problem isn't that the record can't be deleted or updated. It's that your record can't be displayed. Okay, then don't display them when fixing them. Simply issue a SQL DELETE or UPDATE against the rows with bad data. Stop allowing those characters to get into the records and the problem shouldn't happen. Tom
    125,585 pointsBadges:
    report
  • JM1015jm
    If you know the key and the field with the invalid data... create a DFU over the file and omit the field that has the invalid characters from the DFU. The system will allow you to access the record so you can delete it with F23.
    10 pointsBadges:
    report
  • ToddN2000
    @jm1015jm: I know it's an old post but we wrote a fix program to handle it. We could not just delete the data as we did not know what was in it using the I-Series. Here SQL did the trick to get past it. We now have a program that scrubs the data being entered and eliminates any data issues caused by cutting and pasting from any outside source...I just wish our software package did it for us.
    134,495 pointsBadges:
    report
  • GregManzo
    Sadly, most of those software packages were written before cut-and-paste were available. :-(
    2,960 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: