IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

How to generate a Seq/Row counter for each row in a cursor?


IBM Mainframe Forums -> DB2
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
Ramanan-R

New User


Joined: 21 Mar 2007
Posts: 66
Location: Chennai, Tamilnadu, India

PostPosted: Sun Aug 21, 2011 9:29 pm
Reply with quote

Hi All,

The requirement is to generate a unique Sequence/Row counter from 1 auto incremented by 1 for each row retrieved from a cursor.
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


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

PostPosted: Sun Aug 21, 2011 10:11 pm
Reply with quote

why make db2 do the work?
when you open the CURSOR intialize a working/storage counter.
after each successful fetch ADD 1 to the counter.

if your db2 supports row_number, look up the syntax in the manual.

sounds like a class project, instead of a requirement.

you should have posted this question in our companion website for
beginners and people who can not read manuals.
Back to top
View user's profile Send private message
shivaganeshaa

New User


Joined: 11 Aug 2005
Posts: 14
Location: chennai

PostPosted: Sun Aug 21, 2011 10:56 pm
Reply with quote

Define sequence object in DB2 , and that will give you the increment directly every time you access that variable.
Back to top
View user's profile Send private message
Ramanan-R

New User


Joined: 21 Mar 2007
Posts: 66
Location: Chennai, Tamilnadu, India

PostPosted: Mon Aug 22, 2011 7:51 am
Reply with quote

Actually i need to code this in a stored procedure which would be called by Java through DB2 connect and hence would be just using Open cursor without fetch statement...
Back to top
View user's profile Send private message
dick scherrer

Moderator Emeritus


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

PostPosted: Mon Aug 22, 2011 8:43 am
Reply with quote

Hello,

Then get the row_number in the other code, not the stored procedure?
Back to top
View user's profile Send private message
shivaganeshaa

New User


Joined: 11 Aug 2005
Posts: 14
Location: chennai

PostPosted: Mon Aug 22, 2011 8:57 am
Reply with quote

At any rate you need to get in touch with DBA to define Data Base object which is type SEQUENCE OBJECT.

Something as below

CREATE SEQUENCE orders_seq AS INT
START WITH 1
INCREMENT BY 1
MINVALUE 1
NO MAXVALUE
NO CYCLE
NO CACHE
ORDER



If possible give more info, on what do you mean you say OPEN cursor and not fetch it and by Java thru DB2 Connect.
Back to top
View user's profile Send private message
dick scherrer

Moderator Emeritus


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

PostPosted: Mon Aug 22, 2011 9:46 am
Reply with quote

Hello,

I believe this will not do what Ramanan-R wants. . .

I believe the goal is to get the relative row number of the rows as each is returned. This might not be the same as the "sequence" number.

Possibly i misunderstand.
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DB2

 


Similar Topics
Topic Forum Replies
No new posts Generate random number from range of ... COBOL Programming 3
No new posts Two input files & writing counter... DFSORT/ICETOOL 12
No new posts Generate output lines (SYSIN card for... DFSORT/ICETOOL 4
No new posts Sort counter to show records combination JCL & VSAM 2
No new posts JSON Generate COBOL Programming 2
Search our Forums:

Back to Top