Re: Fields updated that shouldn't be

Yes, this make sense. Sounds like what I need to do is plug in some dummy
values before I update the record, right?

I'm actually running this in my test environment with 3 years of data and
testing with QUERY...runs in about 30 seconds.

I've never posted code before but here's a link to the code:
*http://code.midrange.com/40ed53e409.html*

Thanks.


Jim




On Sat, Jan 22, 2011 at 11:43 AM, Mark S. Waterbury <
mark.s.waterbury@xxxxxxxxxxxxx> wrote:

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

--
This is the RPG programming on the IBM i / System i (RPG400-L) mailing list
To post a message email: RPG400-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/rpg400-l.