MVSFORUMS.com

pzmohanty · Posted: Fri Sep 03, 2004 4:22 am Post subject: rownumber & rowid in DB2

hi all,

just before working in DB2 environment i had a brief stint working with Oracle environment.

In Oracle we have ROWNUMBER & ROWID where ROWNUMBER is generated during query & ROWID is a unique ID given to each record of a table which is persistent through out the existence of the record in the table.

Do we have something equivalent to ROWNUMBER & ROWID in DB2 also.

Thanx in advn
P.R.Mohanty

kolusu · Posted: Fri Sep 03, 2004 6:49 am Post subject:

Pzmohanty,

You have been an active user on this site for almost 4 months now. So you are in a position to check out simple things from the manual. So please do some home work.There is a manual link available on the top of this page as well on the home page.

Any check this link for Rowid

http://publibz.boulder.ibm.com/cgi-bin/bookmgr_OS390/BOOKS/DSNITH10/2.4.2.2.4?SHELF=&DT=20010219152025&CASE=

Hope this helps...

Cheers

Kolusu
_________________
Kolusu
www.linkedin.com/in/kolusu

pzmohanty · Posted: Fri Sep 03, 2004 8:33 am Post subject:

hi kolusu,

really sorry 4 giving u oppurtunity for complain.
but believe me b4 putting the question in the forum we really try to get answer by some searching ........but when realize that its gonna take time , i put it to the forum.since, most of the questions were asked during my working hours & thus can't keep on searching for a query for long.......
but by putting the question to forum , i get assured that i will get the answer without consuming my working hour searching.......

anyway going back to the question :

I had issued the following query in SPUFI :
SELECT APP_ID ,ROWID FROM NATDB2.UW_APPLICATION FETCH FIRST ROW ONLY;

and result i got is :
SQLCODE = -206, ERROR: ROWID IS NOT A COLUMN OF AN INSERTED TABLE,
UPDATED TABLE, OR ANY TABLE IDENTIFIED IN A FROM CLAUSE, OR IS NOT A
COLUMN OF THE TRIGGERING TABLE OF A TRIGGER

making things clearer , we r having DB2 ver 7.0

Thanx in advn.
P.R.Mohanty

SureshKumar · Intermediate Joined: 23 Jan 2003 Posts: 211 Topics: 21

pzmohanty,
ROWID is a data type, you may have to use a column name or try select *. thanks

pzmohanty · Posted: Fri Sep 03, 2004 8:50 am Post subject:

hi suresh ,

thanx 4 reply but what i am looking 4 is , i can not alter the table & their is no column in the table which stores rowid , still can i query the ROWID of a record using SELECT.

I am sure this is possible in Oracle but I don't know how to use it in db2.

Thanks,
P.R.Mohanty

pzmohanty · Posted: Fri Sep 03, 2004 8:58 am Post subject:

hi all,

continuing with the query , in ORACLE ROWID is a psuedocolumn in every table & we can fire a query like :

select emp_name , rowid from emp_tab ;

is there any similar facility in DB2????

Thanx in advn,
P.R.Mohanty

NutCracker · Posted: Fri Sep 03, 2004 9:17 am Post subject:

pzmohanty, please restrain from chat lingo.
It hardly takes an effort to write complete, grammatically correct sentences in English.

SureshKumar · Intermediate Joined: 23 Jan 2003 Posts: 211 Topics: 21

pzmohanty,
DB2 does not support it. Thanks