Solved

AS400 Db2 Junk data like +++++

Posted on 2010-08-25
7
1,949 Views
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.

thanks
shailesh mahajan


junk-data.xls
0
Comment
Question by:Bsidmis
7 Comments
 
LVL 34

Assisted Solution

by:Gary Patterson
Gary Patterson earned 100 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

0
 

Author Comment

by:Bsidmis
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.
regards
shailesh
0
 
LVL 34

Assisted Solution

by:Gary Patterson
Gary Patterson earned 100 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
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 5

Assisted Solution

by:stevebowdoin
stevebowdoin earned 50 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
0
 
LVL 2

Assisted Solution

by:dcgrindle
dcgrindle earned 50 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.
0
 
LVL 27

Accepted Solution

by:
tliotta earned 50 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.

Tom
0
 

Author Closing Comment

by:Bsidmis
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
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Many people tend to confuse the function of a virus with the one of adware, this misunderstanding of the basic of what each software is and how it operates causes users and organizations to take the wrong security measures that would protect them ag…
In this article we will discuss all things related to StageFright bug, the most vulnerable bug of android devices.
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

707 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now