AS/400 - Steps to finding what the problem is?

Posted on 2014-11-18
Medium Priority
Last Modified: 2015-04-21
Hi Experts,

I am using an AS400
On the AS400 there is a problem
A client's data is doubling the amounts

How do I go about finding what is wrong?

The client has only provided snap shots of the screens.

Thanks for any help
Question by:Amour22015
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
  • 8
  • 4
  • 2
  • +4
LVL 46

Expert Comment

by:Kent Olsen
ID: 40449964
Hi Armour,

That could literally be anything.  From a user error that's entering the wrong value or clicking the wrong button to an SQL error that's storing the result twice or miscalculating the result.

Anything else that you can tell us would be a great help is steering the discussion.

LVL 35

Expert Comment

by:Gary Patterson
ID: 40450033
Since this is a data corruption issue, a good place to start is to determine when it is happening and what application is involved.

You haven't given us many specifics.  Is this a commercial software package?  Is it something the user created?  What do you have in the way of technical documentation for the application?  Do you have source code?  Does the client have vendor suport for this application?

In general, the process is as follows:

1) If this is a commercial application, contact the vendor and open a problem report.

Assuming that is not the case, or perhaps it is an out-of-support commercial application:

2) Identify the database file (table) containing the problem data.  You may be able to do that you having the client access a function that displays the data and using the DSPJOB command (option 14) to display the list of open files.  Or you may be able to inspect the source code or technical documentation for the application if the client has that.  Or you may need to engage the services of a consultant who is familiar with the system.

3) Once you've identified the file, determine if journaling is enabled for that file using the DSPFD command).  If journaling is enabled, you should be able to inspect the journal receivers, find the problem updates to this table, and determine the user, date, time, and program that caused the problem.

4) If journaling is not enabled, contact the system administrator and request that they start journaling this table, and then attempt to reproduce the problem.  If a development or test environment exists, ti is always best to do this in a non-production environment if possible.

In my experience, utilizing jorualing will always enable you to isolate the problem program / user, etc.  You'd then just follow normal process and program debugging procedures to isolate the cause and fix it.
LVL 35

Expert Comment

by:Gary Patterson
ID: 40450035
I'll note that this is the kind of problem that often requires the assistance of an experienced programmer to troubleshoot.  Do you have access to a programmer familiar with this system?
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks


Author Comment

ID: 40450072

Here is a snap shot of the problem, don't know if I am doing this correct to bring up the snap shot I copy to word and then get the word document?

But if you notice that the claims are doubling the amounts?

If you look at the top left there is:

I am taking a guess that they are table names?

I would need help on finding the table(s), and how to sql the claim number thanks.
LVL 46

Expert Comment

by:Kent Olsen
ID: 40450106

It looks like each line item is displaying twice.  That's probably different than just having an amount doubled.

It looks like each of those items are recorded twice.  As Gary asked above, is this a home-grown application and what access to source do you have?

Author Comment

ID: 40450107
The programmer familiar with this system is busy and I am just trying to find what the problem is without the programmer's help.

This would be a good way to learn what this claim process is about?

But I will need some guidance, so that I can at least have some knowledge about what I would be going after?


Author Comment

ID: 40450135

This is a In-house application.
I think I have all the access to the table(s) so that I can look into this.

I can say that the data comes from the SQL Server and is transferred to the AS400
On the SQL Server (DataStore) all the data looked good and was not doubled.

Also I had someone else (not the programmer) help me and it was mentioned that there is a:
since i cant see the code...change you verify that the provider is choosing the PRVDR_TYP_CD = 'SERVICING' and then setting PAYEE info in Trilogy based on the PRVDR_TYP_CD = 'BILLING'

So this maybe the cause, but I would need help looking at the table that contains:

LVL 18

Expert Comment

by:Dave Ford
ID: 40450153
Without more details, there's really very little that we can do. But, as Gary commented, that's why you have programmers.

Ultimately, you'll need to determine what tables the application is reading, what fields are being used, and what logic the program is using to display the data. All that information comes from ... you guessed it ... a programmer. It doesn't necessarily have to be "the programmer familiar with this system", but he or she would be MUCH faster at narrowing down the problem.


Author Comment

ID: 40450173
But guess what?

I am the programmer, otherwise I would not even be on Experts looking for help.

I just have not worked with the AS400 for over 20 years and need help getting around.

Now I am more into the SQL Server and have already narrowed it down to knowing that the problem is on the AS400.
LVL 35

Expert Comment

by:Gary Patterson
ID: 40450186
OP00012R1 looks like the name of a program, the name of a display file, or the name of a screen withing a display file.  But it -could- be a table name.

TKNOOP looks like a user name to me, but it could be a table name.

This is not an operating system screen.  It is an application screen, so the application programmer can put anything on it they want.

My guess is that these claims were imported twice, somehow.  If so, you're going to need a programmer to help you sort it out - most likely data needs to be fixed, and this may not be the only place - even if you can identify the file.

Depending on how the program was written you may be able to drill down into the user's job from WRKACTJOB or WRKUSRJOB (or DSPJOB of you know the full job name) and use option 14 to see a list of open files while the user is in this screen.  Just depends on how the program was written.  This kind of problem requires some advanced troubleshooting skills that it just isn't possible to teach in a forum like this - specially without access to the client system.  An experienced programmer can track down, possibly in minutes, what it would take us hours to teach you to do.

The best way to learn is to start with any technical documentation that exists.  Unless you're an experienced programmer yourself, the best way to do that is to talk to a programmer who knows this system and get some guidance about the standards used in these programs - where source code is stored.  Screen naming conventions.  Location of technical documentation.  Location of database files.  File naming conventions.

I've been programming on this platform for over 25 years and it might take me half a day or more to figure out what is going on in a system I'm not familiar with.  Just isn't practical to teach you all the things you'd need to know to solve this on your own if you aren't an experienced programmer.  My first step would be to talk to a developer familiar with this particular program and get some basic guidance.

For example, let's say that this does turn out to be the result of a program bug.  Assuming the program is written in  RPG (most likely from looking at that screen), do you know that programming language?

One last note: if I posted a screen like that out of some of my client systems, I'd be in violation of my non-disclosure agreement and could get myself fired or my company sued or fired.  Are you sure it is OK to be posting that information?  You have disclosed the name of a workstation on the network, the internal IP address of a printer, what I suspect is a valid IBM i user name, and several valid claim numbers.  

Please be careful - I think it s great that you're taking initiative, but I'd hate to see you get in trouble!
LVL 35

Expert Comment

by:Gary Patterson
ID: 40450211
You need to find out the name of the table.  You've got some columns names.  Can that person help you figure out the table name and the library name?  Assuming you get all of that, what else do you need to know?  

How does the interface from SQL server work?  There is a good chance that there is a linked table in the SQL server database that you can use to query this table remotely from SQL server - sounds like that is more familiar territory for you anyway.  My guess is that the data transfer process get executed twice.  Take a look at whatever run logs it produces and that may answer the question for you.  Then you just need to unravel the mess.  Maybe it is just one table and no other processing has happened.  No way for me to know from here, though.

There is a green screen interface to DB2 SQL using the STRSQL command.  You can enter interactive SQL commands there.  Sounds like you know SQL.  Just need to know that on this system you need to knwo the library name and table name, and that you typically use a slash as the delimiter 9STRSQL defaults to *SYS naming convention):

SELECT * from library/tableName

Author Comment

ID: 40450233
There are other screens that I could not post because of client disclosure.

But what I have posted so far would not cause a problem

name of a workstation on the network, the internal IP address of a printer, what I suspect is a valid IBM i user name, and several valid claim numbers.  

One could ping that information and as far as the claim number, there is no association to the client (like #1234 = Walmart Company) .  So I think it is safe, but again just about anything posted can be traced down?

LVL 14

Expert Comment

ID: 40450329
Duplicated data that comes from an SQL data source could be caused by an incomplete table join, or by a duplicated row in a table that does not have a unique key defined.

An example of the first problem is if your customer master is joined to a claim master, and your claim #s have suffixes like 04, but the join does not include the suffix field. Your inquiries would look normal for every claim that only had one suffix record, but would get duplicated for any that had two or more suffix records.

An example of the second problem is if your customer master is joined to a claim master, and the claim master is not uniquely keyed on the claim #. Your inquiries would look normal for every claim that only had one record, but would get duplicated if someone created 2 claim records using the same claim # for the same customer #.
LVL 27

Expert Comment

ID: 40451486
First, I echo Gary's concerns about posting such an image. It appears to relate to medical claims and reveals troubling details that can be extrapolated to a surprisingly large map of a business' internal structure.

That aside, the identifying items in the upper left should be noted. They might help or not. Regardless, they are elements that can confirm other items. Gary's guess that OP00012R1 is a display file record name seems very likely. A possibility is that either the display file or the program has a name like "OP00012". A generic search for a similar object name might be useful:


Open in new window

Any resulting listed objects would be entry points into later searches. If a *FILE object that is a display file (DSPF) shows up, make note of the actual name and library. Do the same if any *PGM (program), *MODULE or *SRVPGM (service program) objects are listed. Once a library is known, many related objects might be found.

Also, TKNOOP seems likely to be a *USRPRF (user profile) object name. It might not help right now, but the name that shows up at the time the screen is being displayed can be very helpful. You should have one workstation session that is displaying the problem screen and a second session to use for your troubleshooting. On the second screen:

Open in new window

In place of "TKNOOP", use whatever shows up in that part of the first screen. If the value is a *USRPRF, one of the listed jobs will be the one that is showing the problem. From that job, you can display details such as the currently running program in the call stack or the files that are open in the job.

If you can only find the program name, one possibility:
DSPPGMREF PGM( programName )

Open in new window

The list of references should include names of files that are accessed by the program.

For any database files, you might then use:
RUNQRY QRYFILE(( fileName ))

Open in new window

That gives you a quick view of any records in the named file.
DSPFD FILE( fileName )
DSPFD FILE( libraryName/fileName )

Open in new window

That lets you review the file description to see if a journal is available. As noted above, journal entries can provide just about everything you need to know about transactions to track down the source of any duplications (if they exist). They can potentially also be useful if transactions need to be reversed, but I'd stay away from that possibility if you don't know how it can be done successfully.

Library names can let you learn if programs and files are kept together on the system. They might be in different libraries. Knowing where things are can limit your searches  so you're not overwhelmed with irrelevant objects. You might want to use ADDLIBLE to add libraries to your troubleshooting session to make things a little simpler.

The STRSQL command might be available on the system. If so, you might use it to enter various SQL SELECT statements to look at rows in tables rather than using the RUNSQL command.

Access to object names might let you learn where source code is stored:
DSPPGM PGM( programName )
DSPPGM PGM( libraryName/programName )

Open in new window

That can show locations of related source.

Open in new window

If PDM is available and you know a library name, that can give you access to the source files in the library.

As for what you do with any bits of info you run across, that's hard to say. In any case, some combination of the above commands can possibly give you a good idea where to start.


Author Comment

ID: 40454044
I've requested that this question be deleted for the following reason:

I would like this question deleted, there is information that might be a discloser problem, please delete at once thanks.
LVL 27

Accepted Solution

tliotta earned 2000 total points
ID: 40454045
Only thing that would need deleting would be the attached image in the comment . Otherwise this seems like a useful question thread.


Author Comment

ID: 40454933
Thanks both tliotta and Netminder correct this is a useful question thread.

Expert Comment

by:Douglas Oerly
ID: 40722058
I'm coming in a little late on this, but hopefully my comment could help someone in the future.  The question, as others have stated, is vague but in trouble-shooting an issue like this I would:

Go to the source application where the data is initially being captured and strdbg.  This will allow you to step through the program as it is running, hopefully in an non-production environment.  This will work as long as the Command rmvobs *all has not been run on the app.  The Dspfd and Dsppgmref commands will help in determining what objects are involved in the process and give you other objects to step through in the process.  This could be time consuming and you - knowing your data and environment - would have a better idea of where to set your breakpoints and what to key in on.  Another good tool would be Hawkeye if available.

You can also examine the entire job and keep an eye on what is going on by running the cmd wrkactjob and selecting or specifying the job.  It should go without saying that these issues should have surfaced during testing and caught there.  With that said, I'm assuming that you are using custom in-house applications, and if your using 3rd party software you'll have to find an answer from them.

Author Closing Comment

ID: 40736575
ok great.

Featured Post

Industry Leaders: 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

Recently, Microsoft released a best-practice guide for securing Active Directory. It's a whopping 300+ pages long. Those of us tasked with securing our company’s databases and systems would, ideally, have time to devote to learning the ins and outs…
In today's business world, data is more important than ever for informing marketing campaigns. Accessing and using data, however, may not come naturally to some creative marketing professionals. Here are four tips for adapting to wield data for insi…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

764 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