|
View previous topic :: View next topic
|
| Author |
Message |
Rohit Umarjikar
Global Moderator
Joined: 21 Sep 2010
Posts: 3053
Location: NYC,USA
|
|
|
|
| Code: |
Perform until end-process
exec sql
fetch next rowset
from xyz for 2000 rows
into :ab
end-exec
Evaluate SQLCODE
when 0
when +100
perform 100-main-process
Error
End-evaluate
100-main-process
exec sql
get diagnostics :row-count=row_count
end-exec
if row-count > 0
process
else
Set end-process to true
end-if
|
Will this be going in loop? |
|
| Back to top |
|
 |
Anuj Dhawan
Superior Member
Joined: 22 Apr 2006
Posts: 6250
Location: Mumbai, India
|
|
|
|
| Something perhaps, you don't want to listen - but why not run a test your own? |
|
| Back to top |
|
|
Rohit Umarjikar
Global Moderator
Joined: 21 Sep 2010
Posts: 3053
Location: NYC,USA
|
|
|
|
Anuj,
Yes I am trying to test parallely but you know it needs lot of data set up as per program conditions. |
|
| Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix
|
|
|
|
Hello,
So, set up some data and test. . .
Add a little at a time - it is not all needed the very first time thru . . . |
|
| Back to top |
|
|
Rohit Umarjikar
Global Moderator
Joined: 21 Sep 2010
Posts: 3053
Location: NYC,USA
|
|
|
|
| Yes Dick, true |
|
| Back to top |
|
|
chandan.inst
Active User
Joined: 03 Nov 2005
Posts: 275
Location: Mumbai
|
|
|
|
Hi Rohit,
For me logic looks bit confusing..
You need to set end-process to true in case of Sqlcode 100.
Because for multi-fetch in case of sqlcode 100 your row-count may or may not come greater than zero.
I am not sure what will be value of row count when you issue next fetch even after sqlcode 100 and this next fetch will definitely happen when your first sqlcode 100 return 1 or more row and that might be the reason your logic going in loop
Let me know if I am missing anything or any further information required
Regards,
Chandan |
|
| Back to top |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne
|
|
|
|
Chandan,
good catch!
point is that when you have FETCH'd and received an sqlcode of +100,
the next FETCH will not give you an SQLCODE of 0 or +100,
it will be negative something.
Rohit,
according to the advice in the manuals, a 2000 row rowset fetch
is not suggested.
100, 200 maybe 500; but more is not suggested. |
|
| Back to top |
|
|
chandan.inst
Active User
Joined: 03 Nov 2005
Posts: 275
Location: Mumbai
|
|
|
|
Hi Rohit,
Just a suggestion you can use SQLERRD(3) to get ROW-COUNT after fetch like below
| Code: |
| MOVE SQLERRD(3) TO ROW-COUNT |
This will reduce on DB2 I-O operation of get diagnostics
Regards,
Chandan |
|
| Back to top |
|
|
Rohit Umarjikar
Global Moderator
Joined: 21 Sep 2010
Posts: 3053
Location: NYC,USA
|
|
|
|
| @Chandan: Yes your are right, but you know the stored procedure is already in production for a pretty long time.So I can cannot change any code unless it has some performance issue. |
|
| Back to top |
|
|
Rohit Umarjikar
Global Moderator
Joined: 21 Sep 2010
Posts: 3053
Location: NYC,USA
|
|
|
|
| Quote: |
point is that when you have FETCH'd and received an sqlcode of +100,
the next FETCH will not give you an SQLCODE of 0 or +100,
it will be negative something. |
But if it is other than 0 or 100 then it should throw some error as per the logic which is not happening.[/quote] |
|
| Back to top |
|
|
Anuj Dhawan
Superior Member
Joined: 22 Apr 2006
Posts: 6250
Location: Mumbai, India
|
|
|
|
With first glance at your code, Rohit : If SQLCODE is 0 or 100, control goes to 100-main-process where program checks for the row-count. In case SQLCODE=100, row-count is 0 which is NOT greater than 0 so the flag 'end-process' is set to true; and this acts as a 'cotrol break' for PERFORM UNTIL and the loop ends.
That's why the condition of "a next fetch after SQLCODE=+100" should not come and the program works. |
|
| Back to top |
|
|
chandan.inst
Active User
Joined: 03 Nov 2005
Posts: 275
Location: Mumbai
|
|
|
|
Hi Rohit,
But as per your earlier post this logic is going into loop then how its working fine in production?
About sqlcode after next fetch,it needs to be checked whether it gives 100 or some other sqlcode. I am not sure about it
its better to check with some test data
Regards,
Chandan |
|
| Back to top |
|
|
Anuj Dhawan
Superior Member
Joined: 22 Apr 2006
Posts: 6250
Location: Mumbai, India
|
|
|
|
And why do you question:
| Quote: |
| Will this be going in loop? |
- have you faced some problem or you just speculated this while analyzing the code? |
|
| Back to top |
|
|
chandan.inst
Active User
Joined: 03 Nov 2005
Posts: 275
Location: Mumbai
|
|
|
|
I just ran a quick test code for similar scenario but not with muti fetch
it's giving sqlcode 100 not single fetch when its going for fetch even after sqlcode 100
Regards,
Chandan |
|
| Back to top |
|
|
chandan.inst
Active User
Joined: 03 Nov 2005
Posts: 275
Location: Mumbai
|
|
|
|
Just a correction in typo
I meant to say it gives sqlcode 100 for next fetch
Few addition to Anuj's reply..
When this query returns sqlcode 100 for first time it may return few rows but less that your row set limit in this case ROW-COUNT > 0 and
end-process will not be set.
But it should for the next fetch when it will return 100
Looks like its your speculation as Anuj mentioned
Regards,
Chandan |
|
| Back to top |
|
|
enrico-sorichetti
Superior Member
Joined: 14 Mar 2007
Posts: 10873
Location: italy
|
|
|
|
| the topic is getting nowhere, wasting time on pure speculation |
|
| Back to top |
|
|
chandan.inst
Active User
Joined: 03 Nov 2005
Posts: 275
Location: Mumbai
|
|
|
|
| Totally agree with you Enrico |
|
| Back to top |
|
|
|
|
