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
 

 

how to sequence the rows of a select?

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
jctgf
Currently Banned

Active User


Joined: 04 Nov 2006
Posts: 109

PostPosted: Sun Dec 16, 2007 7:50 pm    Post subject: how to sequence the rows of a select?
Reply with quote

hi,

is there any function in db2 that provides a sequential number for each row retrieved in a select statment?

example:

1) select without the function i want:

select name, cityname from table where zipcode = 123

bill miami
mark nyc
ster omaha


2) select with the function i want:

select ???, name, cityname from table where zipcode = 123

001 bill miami
002 mark nyc
003 ster omaha



thanks,
jc
Back to top
View user's profile Send private message

stodolas

Active Member


Joined: 13 Jun 2007
Posts: 632
Location: Wisconsin

PostPosted: Sun Dec 16, 2007 10:40 pm    Post subject:
Reply with quote

Your row number could change for bill miami each time since you don't have an order by. Even if you had an order by on the name, if you add Albert chicago bill would have a different number the next time you run your query
Back to top
View user's profile Send private message
jctgf
Currently Banned

Active User


Joined: 04 Nov 2006
Posts: 109

PostPosted: Sun Dec 16, 2007 10:59 pm    Post subject:
Reply with quote

hi,

thanks, but is there any function in DB2 that provides this row number?

i don't mean the rowid number, but a row number generated by the select command.

it should be ascendent and start from 1 with a increment of 1.

thanks again,
jc
Back to top
View user's profile Send private message
stodolas

Active Member


Joined: 13 Jun 2007
Posts: 632
Location: Wisconsin

PostPosted: Mon Dec 17, 2007 8:44 am    Post subject:
Reply with quote

I can't think of a way to do it and about 10 minutes searching on Google seemed to confirmed that. It is probably possible if you write your own function or stored procedure and have a DBA put it into the database for you.
I may be missing something, but I can't see a generated row number serving any purpose in processing the data. The table should already have a primary key that can uniquely identify any row in the table without adding these generated numbers you desire.

A number made up on the fly on each query doesn't have any carry over meaning from one query to the next because it is almost assured to change.

Is there a business requirement behind this? If you are running the query through Cobol, then you can keep your own counter. If you are doing ad hoc queries, what does the a row number generated by the select do for you, other than to give a count and for that you could do a SELECT COUNT(*) FROM table WHERE same conditions as your original query.
Back to top
View user's profile Send private message
jctgf
Currently Banned

Active User


Joined: 04 Nov 2006
Posts: 109

PostPosted: Mon Dec 17, 2007 3:19 pm    Post subject:
Reply with quote

hi,

it's a little bit complicated to explain ... icon_sad.gif

we have a problem with the 3 layer architerture in our company.

we have a pseudo-conversational enviroment and the caller program usually is a NATURAL one. the called program is coded in COBOL but every time it returns control to the caller, the cursor is closed.

at the next call, the cursor must be opened again and it's a bad solution in terms of performance.

i am trying to find the "less worst" solution.

yes, i can use a counter in the COBOL ws but i just wondered if db2 could provide the counter to me.

thanks a lot,
jc
Back to top
View user's profile Send private message
Ritesh Chopra

New User


Joined: 15 Oct 2007
Posts: 13
Location: India

PostPosted: Mon Dec 17, 2007 4:22 pm    Post subject: Reply to: how to sequence the rows of a select?
Reply with quote

Do you want to count the rows, so that when the program gets the control back u can fetch the rows after the row you had already fetched in previous fetch ?
Please elaborate more oon problem.
Back to top
View user's profile Send private message
stodolas

Active Member


Joined: 13 Jun 2007
Posts: 632
Location: Wisconsin

PostPosted: Mon Dec 17, 2007 5:32 pm    Post subject:
Reply with quote

Let me guess, there are about 50 rows and program a calls program B and program B can only return 10 rows at a time to program A?

Order your cursor by primary key. On subsequent calls to B, pass in the highest primary key from the last call. Make sure program B has a WHERE that allows you to pass in the highest primary key that you retrieved on the last call.

Either this is homework or 2 people are working on exactly the same problem at the same time. People were just replying to an identical topic using a natural program and a conversational enironment and cursor that gets closed.
Back to top
View user's profile Send private message
stodolas

Active Member


Joined: 13 Jun 2007
Posts: 632
Location: Wisconsin

PostPosted: Mon Dec 17, 2007 5:38 pm    Post subject:
Reply with quote

Oh wait you are double posting on your same topic:

http://ibmmainframes.com/viewtopic.php?t=26787&highlight=
Back to top
View user's profile Send private message
TG Murphy

Active User


Joined: 23 Mar 2007
Posts: 149
Location: Ottawa Canada

PostPosted: Mon Dec 17, 2007 9:03 pm    Post subject:
Reply with quote

You say "at the next call the cursor must be opened again."

Question: Does this next call happen within the same task? Or does it happen after the pseudo return (ie. after the user presses F8 to advance to the next page?)

If it happens with the same task - and your cursor is being closed, is it possible that Natural is issing a SYNCPOINT? Here is an long shot experiment to try: Use the WITH HOLD on your cursor declaration and see if that avoids the need to re-open the cursor.
Back to top
View user's profile Send private message
enrico-sorichetti

Global Moderator


Joined: 14 Mar 2007
Posts: 10203
Location: italy

PostPosted: Mon Dec 17, 2007 10:15 pm    Post subject: Reply to: how to sequence the rows of a select?
Reply with quote

I would say that the probability might be low, but ...
what if some other task inserted something in between...
relying on a simple sequence expectation is not wise..

what about storing the full result of the query in temporary storage
an do the terminal paging from there
( obviously if the number of the retrieved rows is not unreasonably high )
Back to top
View user's profile Send private message
jctgf
Currently Banned

Active User


Joined: 04 Nov 2006
Posts: 109

PostPosted: Tue Dec 18, 2007 3:39 am    Post subject:
Reply with quote

stodolas wrote:
Oh wait you are double posting on your same topic:

http://ibmmainframes.com/viewtopic.php?t=26787&highlight=


hi,

my intention wasn't to double post.

i wasn't receiving any reply for the 1st post.

i thought the reason was that it was to confusing.

thanks,
jc
Back to top
View user's profile Send private message
Ritesh Chopra

New User


Joined: 15 Oct 2007
Posts: 13
Location: India

PostPosted: Tue Dec 18, 2007 2:48 pm    Post subject: Reply to: how to sequence the rows of a select?
Reply with quote

Hi jctgf,
What stodolas wrote is correct.
I will explain it this way
The best way to do it is:
suppose you have a program 'A' which retrives and sends data to 'B'.
Now A can send only 10 rows at a time.
What can be done is pass back from 'A' data that indicates which was the last data item.
Then in the sql query you can compare,
your table data > last data.
as the data in the table is in sorted order.

or if u have more than one field to compare the
field1 concat field2 concat f3........ > last data.

This way you can manage to fetch the data starting from the next row.
Experts please correct me.
Back to top
View user's profile Send private message
Ritesh Chopra

New User


Joined: 15 Oct 2007
Posts: 13
Location: India

PostPosted: Tue Dec 18, 2007 2:52 pm    Post subject: Reply to: how to sequence the rows of a select?
Reply with quote

Hi enrico-sorichetti,
Please explain in detail what u want to say.
Is it that what if some new rows are inserted between two fetches ?
Any ways if you buffer the data, the new inserted rows won't be fetched as the buffer does not have them.
Back to top
View user's profile Send private message
enrico-sorichetti

Global Moderator


Joined: 14 Mar 2007
Posts: 10203
Location: italy

PostPosted: Tue Dec 18, 2007 3:23 pm    Post subject: Reply to: how to sequence the rows of a select?
Reply with quote

the approach of buffering/saving the result of the query has two objectives

Guarantee the consistency of the results ( totals for example )

Give the end user the possibility of scrolling back and forth thru the data
( without repeating the query )
much more simple than all the cursor/fetch management implied
by the O/P approach

if rows get inserted delete in between all the considerations made on the
relative sequence number WILL be wrong
( the probability is low.. but no reason to risk )
Back to top
View user's profile Send private message
jctgf
Currently Banned

Active User


Joined: 04 Nov 2006
Posts: 109

PostPosted: Wed Dec 19, 2007 3:47 am    Post subject: Re: Reply to: how to sequence the rows of a select?
Reply with quote

Ritesh Chopra wrote:
Hi jctgf,
What stodolas wrote is correct.
I will explain it this way
The best way to do it is:
suppose you have a program 'A' which retrives and sends data to 'B'.
Now A can send only 10 rows at a time.
What can be done is pass back from 'A' data that indicates which was the last data item.
Then in the sql query you can compare,
your table data > last data.
as the data in the table is in sorted order.

or if u have more than one field to compare the
field1 concat field2 concat f3........ > last data.

This way you can manage to fetch the data starting from the next row.
Experts please correct me.


Hi Ristech,

I couldn’t be happier with your suggestion. This is exactly what we have been doing here for a while.

We have been wondering if there is best way to do this, though. The digits/concat command seems to be very “expensive”.

Today we did an experiment here. We tested 2 programs:

The first one (program B1) is coded just like you said, but also with the “fetch first n rows” statement.

The second one (program B2) doesn’t use digits/concat but simply fetches and discards the all rows already fetched in a previous call.

Surprisingly the second program was faster than the first one. We repeated the comparison many times and the second one was always faster, despite fetching all the rows of the cursor (a 5000 rows cursor) several times.

In an online transaction it is impossible to say which option is faster, but in a batch job that calls program B thousands of times it surely may make a difference.

Thanks,
jc
Back to top
View user's profile Send private message
jctgf
Currently Banned

Active User


Joined: 04 Nov 2006
Posts: 109

PostPosted: Wed Dec 19, 2007 5:14 am    Post subject: Re: Reply to: how to sequence the rows of a select?
Reply with quote

Ritesh Chopra wrote:
Hi jctgf,
What stodolas wrote is correct.
I will explain it this way
The best way to do it is:
suppose you have a program 'A' which retrives and sends data to 'B'.
Now A can send only 10 rows at a time.
What can be done is pass back from 'A' data that indicates which was the last data item.
Then in the sql query you can compare,
your table data > last data.
as the data in the table is in sorted order.

or if u have more than one field to compare the
field1 concat field2 concat f3........ > last data.

This way you can manage to fetch the data starting from the next row.
Experts please correct me.


hi again,

is there a way to concat the fields without using the "digits" command?

i am assuming here that i would have some advantage if i do not execute the "digits" command...

thanks,
jc
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    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
This topic is locked: you cannot edit posts or make replies. Db2 SQL query to convert rows into co... subratarec DB2 5 Thu Nov 17, 2016 4:51 pm
No new posts Two Selects in a single select query Rohit Umarjikar DB2 1 Fri Nov 04, 2016 8:46 pm
No new posts Row-Numbers of distinct rows? Auryn DB2 1 Thu Oct 20, 2016 4:38 pm
No new posts Sequence number add in SORT pshongal SYNCSORT 3 Fri Sep 02, 2016 3:32 pm
No new posts Can we use OPTIMIZE FOR 1 ROWS for fe... cvijay784 DB2 1 Fri Aug 05, 2016 11:56 am


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