Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

Multi ROW fetch question

 
Post new topic   This topic is locked: you cannot edit posts or make replies.    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
Rohit Umarjikar

Senior Member


Joined: 21 Sep 2010
Posts: 1610
Location: NY,USA

PostPosted: Thu Aug 30, 2012 7:37 pm    Post subject: Multi ROW fetch question
Reply with quote

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
View user's profile Send private message

Anuj Dhawan

Senior Member


Joined: 22 Apr 2006
Posts: 6258
Location: Mumbai, India

PostPosted: Thu Aug 30, 2012 8:31 pm    Post subject:
Reply with quote

Something perhaps, you don't want to listen - but why not run a test your own?
Back to top
View user's profile Send private message
Rohit Umarjikar

Senior Member


Joined: 21 Sep 2010
Posts: 1610
Location: NY,USA

PostPosted: Thu Aug 30, 2012 8:40 pm    Post subject:
Reply with quote

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
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Thu Aug 30, 2012 8:51 pm    Post subject:
Reply with quote

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
View user's profile Send private message
Rohit Umarjikar

Senior Member


Joined: 21 Sep 2010
Posts: 1610
Location: NY,USA

PostPosted: Thu Aug 30, 2012 9:18 pm    Post subject:
Reply with quote

Yes Dick, true
Back to top
View user's profile Send private message
chandan.inst

Active User


Joined: 03 Nov 2005
Posts: 269
Location: Mumbai

PostPosted: Fri Aug 31, 2012 9:22 am    Post subject:
Reply with quote

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
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne

PostPosted: Fri Aug 31, 2012 9:43 am    Post subject:
Reply with quote

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
View user's profile Send private message
chandan.inst

Active User


Joined: 03 Nov 2005
Posts: 269
Location: Mumbai

PostPosted: Fri Aug 31, 2012 10:02 am    Post subject:
Reply with quote

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
View user's profile Send private message
Rohit Umarjikar

Senior Member


Joined: 21 Sep 2010
Posts: 1610
Location: NY,USA

PostPosted: Fri Aug 31, 2012 2:52 pm    Post subject:
Reply with quote

@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
View user's profile Send private message
Rohit Umarjikar

Senior Member


Joined: 21 Sep 2010
Posts: 1610
Location: NY,USA

PostPosted: Fri Aug 31, 2012 2:59 pm    Post subject:
Reply with quote

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
View user's profile Send private message
Anuj Dhawan

Senior Member


Joined: 22 Apr 2006
Posts: 6258
Location: Mumbai, India

PostPosted: Fri Aug 31, 2012 4:02 pm    Post subject:
Reply with quote

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
View user's profile Send private message
chandan.inst

Active User


Joined: 03 Nov 2005
Posts: 269
Location: Mumbai

PostPosted: Fri Aug 31, 2012 4:03 pm    Post subject:
Reply with quote

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
View user's profile Send private message
Anuj Dhawan

Senior Member


Joined: 22 Apr 2006
Posts: 6258
Location: Mumbai, India

PostPosted: Fri Aug 31, 2012 4:07 pm    Post subject:
Reply with quote

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
View user's profile Send private message
chandan.inst

Active User


Joined: 03 Nov 2005
Posts: 269
Location: Mumbai

PostPosted: Fri Aug 31, 2012 4:28 pm    Post subject:
Reply with quote

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
View user's profile Send private message
chandan.inst

Active User


Joined: 03 Nov 2005
Posts: 269
Location: Mumbai

PostPosted: Fri Aug 31, 2012 4:45 pm    Post subject:
Reply with quote

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
View user's profile Send private message
enrico-sorichetti

Global Moderator


Joined: 14 Mar 2007
Posts: 10203
Location: italy

PostPosted: Fri Aug 31, 2012 4:47 pm    Post subject: Reply to: Multi ROW fetch question
Reply with quote

the topic is getting nowhere, wasting time on pure speculation
Back to top
View user's profile Send private message
chandan.inst

Active User


Joined: 03 Nov 2005
Posts: 269
Location: Mumbai

PostPosted: Fri Aug 31, 2012 5:02 pm    Post subject:
Reply with quote

Totally agree with you Enrico
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   This topic is locked: you cannot edit posts or make replies.    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Multi row fetch - "for read only... Nileshkul DB2 3 Sun Aug 14, 2016 12:52 am
No new posts Fault Analyzer - listings question. egrove IBM Tools 4 Thu Aug 11, 2016 5:31 pm
No new posts dataset copy question - REPRO or some... atulxp TSO/ISPF 2 Wed Aug 03, 2016 10:56 pm
No new posts Reading selected volumes of a multi-v... RickBig JCL & VSAM 6 Wed Jul 13, 2016 7:26 pm
No new posts JCL to fetch schedule status from act... parasmalik20 CA Products 1 Thu Jun 02, 2016 7:11 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us