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

how to sequence the rows of a select?


IBM Mainframe Forums -> DB2
Post new topic   Reply to topic
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
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
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
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
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
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
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
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
Reply with quote

Oh wait you are double posting on your same topic:

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: 148
Location: Ottawa Canada

PostPosted: Mon Dec 17, 2007 9:03 pm
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

Superior Member


Joined: 14 Mar 2007
Posts: 10872
Location: italy

PostPosted: Mon Dec 17, 2007 10:15 pm
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
Reply with quote

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

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
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
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

Superior Member


Joined: 14 Mar 2007
Posts: 10872
Location: italy

PostPosted: Tue Dec 18, 2007 3:23 pm
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
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
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 View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DB2

 


Similar Topics
Topic Forum Replies
No new posts To get the count of rows for every 1 ... DB2 3
No new posts Exclude rows with > than x occurre... DFSORT/ICETOOL 6
No new posts Dynamically pass table name to a sele... DB2 2
No new posts Cobol program with sequence number ra... COBOL Programming 5
No new posts Convert single row multi cols to sing... DFSORT/ICETOOL 6
Search our Forums:

Back to Top