*** FETCH working Strange... Please Help ***

[stillin400]

Experts,

We are in V7R2M0.

I Declare open Fetch and close on an embedded SQL in RPGLE and I put all inside a For Loop.. I fetch to a data structure.

dcl-ds TestDS dim(1000) Qualified inz;

T_Flld1 char(9);
T_Fld2 pid char(1);
etc

For Loop for each employee
declare / open / Fetch to a Data Structure array / Close / Process .................... ( I should have kept the declare outside the loop )
End for

We have 2 different system and both area V7R2M0.

NOTE: I am not exclusively clearing the data structure for each for loop.........

System 1 : Employee 1 on first loop fetch .... and
2nd loop.......... System clears the Data Structure and loads the 2nd employee


System 2 : Employee 1 on first loop fetch .... and
2nd loop.......... System moves the 1st employee down the array and loads the 2nd employee info from 1st element.

If I put the exclusive CLEAR TESTDS, it will work on both system.

But, question is
1. why there is a difference on how this same code executed
2. How the system 2 is pulling the 1st employee down the array element and load the 1st employee from the 1st element..

Any body had this problem.

Please help.

Thank You
Stillin400

[TedHolt]

It might help if you would post some code, especially the cursor declaration & the fetch.

[tomliotta]

Also, how are the programs created on the systems? Are two programs compiled, one on each system? Or do you compile on one, then save/restore to the other system?

[stillin400]

Thanks Ted and Tom for taking time to look at this.

Both system has its own source and both are compiled on its own system. Source code is same.
Note : No exclusive clear on the data structure on each FETCH.... when I do this, System 2 works fine, and problem solved.

But Question I have is.. why system 1 and 2 works different for V7R2M0
The only difference I can see, is system 1 .. fetch 1 has less records than fetch 2.. where as system 2..fetch 1 has more records than fetch 2..but still...?.

System 1 : ( Works good )
Fetch 1 : Employee = 1......... 5 Rows 5 Records in the data structure ....... ( Postion 1,2,3,4,5 on the data structure)
Fetch 2 : Employee = 2......... 7 Rows 7 Records in the data structure ....... ( Postion 1,2,3,4,5,6,7 on the data structure)

System 2 : ( Works not good )
Fetch 1 : Employee = 3 ......... 8 Rows 8 Records in the data structure ....... ( Postion 1,2,3,4,5,6,7,8 on the data structure)
Fetch 2 : Employee = 4.......... 5 Rows ..... ( Postion 1,2,3,4,5 for employee 4 records and 6,7,8 for employee 3 on the data structure)

Long story short, system 2, 2nd fetch overlays 1st fetch records

Below is part of the code.. I have altered the code.

For Lctr to%Elem(P_summaDS);

If P_SummaDS(Lctr).EmpNumber =0;
Leave;
ENDIF;

CurrEmpumber = P_SummaDSLctr).EmpNumber;

?@

execsqlsetoptionnaming = *SYS, commit = *none;

execsqldeclare xCursor Cursorfor

Select t1, t2, t3, t4, t5

From file1

where t1 = :x1 and t2 = :x2 and

t3 = :CurrEmpNumber;

?@

execsqlopen xCursor;

dou sqlstt >= sqlEof;
execsql
fetchnextfrom xCursor
for :ArraySize rows........................ ( ArraySize = 1000 )
into :TestDS;
enddo;

execsqlclose xCursor;

Exsr ProcessEmpSR;

ENDFOR;

[Brian Rusch]

The data structure does not get cleared automatically. I'm sure if you would test the same scenario on system 2 that was on system 1 (where the second fetch has less records than the first), you would see the same results. Here's an article about this topic: http://www.rpgpgm.com/2015/04/sql-bl...more-than.html

[stillin400]

Thank You Brian for your response. Let me take a look into the article. So far, when we look at the data on system 1, we see the values are all correct. But, I will test and debug more with different scenario and if I find something more, I will post it here.

[tomliotta]

Thanks Ted and Tom for taking time to look at this.

Both system has its own source and both are compiled on its own system. Source code is same.

You're welcome; but it's almost guaranteed that if we can't at least see both sets of code, we can't tell why one works differently.

I'd say that ideally you'd save the version that works correctly, restore it to the other system, then see if its behavior changes. That should narrow the possibilities significantly. If it works correctly, then copy the working source member and compile it. See if the behavior changes. That would either eliminate almost all other possibilities or highlight the source of the problem.

There are too many possibilities. There might be an unknown difference in source. The source might not match the running program. There might be differences in compile-time defaults. There might be data differences. Maybe the environment (library list, authorities, etc.) has differences. Maybe a PTF is different. There's a long list of things that could be different and that can affect results. No possibility for us to know without actual, sufficient details.

If you could create minimal examples by cutting all unimportant code from one source member and test that on both systems, it might make it obvious why it runs differently. And if it doesn't, we could review minimal code easily.

[JonBoy]

It doesn't seem to be the cause of the problem in your situation but for the sake of completeness ...

If you have different results from the _same_code_ and _same_data_ on two different systems then the most likely cause is different PTF levels. i.e. the fix for a problem is on one system and not the other.