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

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.


Kent

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.

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?

Ok,

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:
op0012R1
TKNOOP

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.

Ahh...

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?

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?

Thanks.

Yes,

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:
PRVDR_TYP_CD

Thanks

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.

HTH,
DaveSlash

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.

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!

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

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

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.  

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?

Thanks

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 #.

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:

WRKOBJ OBJ(*ALLUSR/OP00012*)

Select allOpen 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:

WRKUSRJOB USER(TKNOOP) STATUS(*ACTIVE) JOBTYPE(*INTERACT)

Select allOpen 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 )

Select allOpen 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 ))
  or
RUNQRY QRYFILE(( librarynAME/FILEnAME ))

Select allOpen in new window

That gives you a quick view of any records in the named file.

DSPFD FILE( fileName )
  or
DSPFD FILE( libraryName/fileName )

Select allOpen 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 )
  or
DSPPGM PGM( libraryName/programName )

Select allOpen in new window

That can show locations of related source.

WRKOBJPDM LIB( libraryName ) OBJTYPE(*FILE) OBJATR( *SRC )

Select allOpen 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.

Tom

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.

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

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.

ok great.