• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2553
  • Last Modified:

AS400 Db2 Junk data like +++++

Dear Support Team,
I have observed that some times in a PF, the data corrputed or shown like junk data ( like ++++++) , which indicate that may be the length of data is more than the defined variable length.  It is not happneing in entire database only in a particular file this thing is happening.
I am enclosing herewith an file in which the data has been corrupted. i want to understand why it happens and how we can prevent in future.

shailesh mahajan

5 Solutions
Gary PattersonVP Technology / Senior Consultant Commented:
This looks like the output from an STRSQL or QM/400 query.  What utility is being used?

Display the job log and see if there are message logged about the cause of the replacement character mapping.  This can happen when a field contains invalid data, or when a user-defined field is too short to hold the result of a query.  In valid data is almost always the result of data either being improperly imported into the file, or an application corrupting data and writing it out without performing proper data validation.

If multiple applications write to this or update this file, you may find it useful to review the journal receivers associated with the file (or enable journaling for the file if it is not currently enabled).  That will allow you to pinpoint he specific application that is causing the corruption (if that is really the problem - this might just be a problem with the query that you are running).

It would be useful to see the following:

1) The layout of the problem table (DDL, DDS, or DSPFFD output).
2) The messages, if any, on the job log after running this query, including second-level text
3) A hex dump of one or more of the the problem records (DSPPFM command, F10 is one easy to way to do this).
4) The SQL statement that is generating this result.

- Gary Patterson

BsidmisAuthor Commented:

Dear Gary,
This is the output from WRKQRY.
The data not imported by any means. It enters by an user via DDS Screen in which proper cheks and validations are applied. We are not using journaling rightnow. This file is writing(data appending) by only one program and only one user at a time.
Let me analyse this situation once again and would confirm you.
Gary PattersonVP Technology / Senior Consultant Commented:
In that case, please print out the full WRKQRY query definition and post it back here, too.  Perhaps you just have some fields declared too small in the query definition, or are performing some invalid conversions (converting numeric fields containing zeros to dates, for example).

- Gary
Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

Looks to me as if the data does not match its definition.  For instance you have character data where numeric should be.

Take that "trouble in minutes".  Use DSPFFD to get the starting position and data type.
I bet the type is “Packed”.  But it could be "Zoned"

Then use DSPPFM to display the file. Use F10 to display in Hex.  In "Control" put "W" and the starting position of the field. It will be something like W100.

If I am correct and it is packed then you will find character data and not packed data.  Most likely is should be a series of digits followed by an F. "12345F"
If it is zoned it will look like 1F2F3F4F5F for 12345.  If it is not a digit followed by an F then there’s your problem.

If I am correct, your programs have been compiled to ignore decimal data errors.

This occurs a lot with migrated System/36 code.  36 did not care, 400 cares a lot.

Steve Bowdoin
Query/400 uses the ++++ to replace any data in the list that is invalid for that column.  

Since you are only getting the first three columns and not the last two we can see, my guess is there are blank records picked up by the query.  That can mean a missing join or actual blank records.

If your query only uses one table, the blank records are in that table.  

If your query uses multiple tables, though, the problem is in the join.  The +++++ means that a join to the primary table didn't get a hit on a secondary table.  There is no valid data to display from the secondary table.  Your problem would be there are no matching records instead of blank records.
The spreadsheet shows unusual values in the TROUBLE DESC column for those last five rows.  It shows an especially odd value in the TR.CATEGORY column for the next-to-last row.

The "bld" value looks as if it actually belongs as part of the TROUBLE DESC column. This is strongly suggested by the TROUBLE DESC value five rows above which is practically the same value otherwise.

An obvious guess is that all of those rows were added through an incorrect record format, e.g., one that allowed LVLCHK(*NO). It looks as if all columns beginning with Trouble in Minutes in the last five rows have been shifted to the right four bytes.

BsidmisAuthor Commented:
Dear All,

I have gone through the reply of all experts, and it is really useful information. One thing is that there is not any problem with WRKQRY. Basically the data is storing via a program and this program is calculating some value and updating this PF.

Let me re-check this program and even though if i would face any problem i would contact to you.

thanks a lot

shailesh mahajan
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now