Re: Fields updated that shouldn't be

Hi, James:

Knowing very little about your actual application or programs, here is a preliminary "guess"...

**You mentioned using SQL to "read" records, but you are using "native I/O" (CHAIN and WRITE or UPDATE) to "write" records. This could be the source of the problem, due to the differences between SQL and "native I/O" ...

When you use SQL to FETCH data, you get only those columns (fields) you specify on the SELECT, but when you use "native I/O" (READ, READP, READE, CHAIN, etc.) you get *ALL* the fields in the record, whether you wanted them or not, and similarly, when you use WRITE or UPDATE, it writes out *ALL* the fields in the record, whether you wanted that or not...

So, the problem could be that the CHAIN used to see if the record exists fetches a value for FLDA ... so then, the local variable for FLDA now contains a value from some previous record that was found, and so, when the program subsequently WRITEs a new record, if the program has not explicitly cleared the local variable for FLDA, then that value will be written out in the new record... *:-o* (_Do you understand why this is so?_)

This is fundamental to RPG... RPG is a "scatter-gather" language. On input (READ, CHAIN...) the RPG generated code copies each field from the record buffer to like-named local variables in the program (the "scatter" part), and then on output (WRITE or UPDATE) the RPG generated code "gathers" the data again from all of the like-named variables into each field's position in the record buffer, and then does the WRITE or UPDATE. This is _very different_ from how SQL works, processing only those columns named on each SQL statement.

*USE THE DEBUGGER*
Can you run this interactively? Perhaps you could run "Program2" in debug and step through the code to see exactly when it updates File2?

You could create "cut-down" versions of your programs, with "dummy" File1 and File2, and with field names like FLDA and FLDB etc., and "dummy" programs that use the "dummy" files and fields... and a small set of "sample data" just enough to reproduce the problem. The dummy files need contain only a few fields needed to reproduce the problem. Then, if all else fails, perhaps you can post the source code for these "dummy" programs and files and the "sample data" at http://code.midrange.com and then reply to the list with a link to these samples, so someone can try to reproduce your problem, to further understand the problem and perhaps offer assistance or advice.

HTH,
Mark S. Waterbury

> On 1/22/2011 11:48 AM, James Newman, CDP wrote:

I've got a program bug that I cannot figure out and would appreciate some
help. This is written in RPG using SQL on V5R2. Let's assume for sake of
simplicity that I'm using data for one calendar year - 2008.

Program1 reads records from FILE1 delivered by SQL and writes to FLDA in
FILE2. Since it's calculating a 60 period average it can't begin until it
has 60 records. So we don't see any records written to FILE2 until
03/28/08.

Program2 reads records from FILE1 delivered by SQL and writes to FLDB. It's
performing a different calculation that requires 15 days of data. So we see
it begin writing recors on 01/15/08.

Here's where the problem starts. I was looking over the data and noticed
that FLDA had data in it for the records from 01/15/08 to 03/27/08. It is
filled with the exact number in FLDA from 12/31/08. So I think the problem
must be something in Program2 but can't find anything. Even though it is
updating the same records I can't find any way it would update FLDA.

Then I decided to try an experiment. I reset my test data, ran Program1
again and then ran Program3 (they aren't necessarily sequential), which
reads records from FILE1 delivered by SQL and writes to FLDC in FILE2.
Guess what? It does exactly the same thing - updates FLDA!!

My only guess at this point is that I've got something wrong in the way I
update FILE2. Here's how I do it:

1. Build key with KLIST
2. CHAIN to FILE2
3. If indicator is off, UPDATE
4. If indicator is on, WRITE

Any suggestions would be greatly appreciated. TIA.


Jim Newman