Bad data in as/400 database.. cant display record
Home > IT Answers > AS/400 > Bad data in as/400 database.. cant display re...
ToddN2000
220 pts.
0
Q:
Bad data in as/400 database.. cant display record
AS/400, Hex, DFU, Editors, AS/400 database
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: Sep 4 2009  5:58 PM GMT
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
RELATED QUESTIONS
0
Philpl1jb
24520 pts.
0
A:
 RATE THIS ANSWER
0
Click to Vote:
  •   0
  •  0
  • AddThis Social Bookmark Button
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
Last Answered: Sep 8 2009  3:36 PM GMT by Philpl1jb   24520 pts.
Latest Contributors: ASWDEVELOPER   205 pts., CharlieBrowne   6410 pts.
  •   
0
0
RSS - Discussions Help
Discuss This Answer:
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _



_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _

Vatchy   670 pts.  |   Sep 8 2009  2:01AM GMT

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.

 

Yorkshireman   3200 pts.  |   Sep 8 2009  11:15AM GMT

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.

 

NullFields   395 pts.  |   Sep 8 2009  2:26PM GMT

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.

 

Splat   1050 pts.  |   Sep 9 2009  3:58PM GMT

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.

 

Koohiisan   995 pts.  |   Sep 9 2009  5:02PM GMT

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.

 

TomLiotta   7355 pts.  |   Oct 11 2009  11:19AM GMT

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

 
0