Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win


AS400 Db2 Junk data like +++++

Posted on 2010-08-25
Medium Priority
Last Modified: 2013-12-06
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

Question by:Bsidmis
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
LVL 35

Assisted Solution

by:Gary Patterson
Gary Patterson earned 300 total points
ID: 33523212
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


Author Comment

ID: 33527987

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.
LVL 35

Assisted Solution

by:Gary Patterson
Gary Patterson earned 300 total points
ID: 33531756
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
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.


Assisted Solution

stevebowdoin earned 150 total points
ID: 33531925
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

Assisted Solution

dcgrindle earned 150 total points
ID: 33533990
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.
LVL 27

Accepted Solution

tliotta earned 150 total points
ID: 33538264
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.


Author Closing Comment

ID: 33538559
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

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recursive SQL in UDB/LUW (it really isn't that hard to do) Recursive SQL is most often used to convert columns to rows or rows to columns.  A previous article described the process of converting rows to columns.  This article will build off of th…
Sometimes a user will call me frantically, explaining that something has gone wrong and they have tried everything (read - they have messed it up more and now need someone to clean up) and it still does no good, can I help them?!  Usually the standa…
This is used to tweak the memory usage for your computer, it is used for servers more so than workstations but just be careful editing registry settings as it may cause irreversible results. I hold no responsibility for anything you do to the regist…
Hi friends,  in this video  I'll show you how new windows 10 user can learn the using of windows 10. Thank you.

650 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question