rowid

Our env: DB2 v8.1 on AIX5.3
My question is, I have a SQL statement which works fine in oracle...
update mytab set c1 = -c1 where rowid = v_rowid;
I need an equivalent in db2 (I am aware rowid equivalent is row_number() over() but I am wondering how to assign the left hand part of rowid because mytab table does not contain the column rowid!!).
Any ideas? thanks.
dr_suresh20 View Public Profile Send a private message to dr_suresh20 Send email to dr_suresh20 Find all posts by dr_suresh20
#2 08-07-06, 11:44 Peter.Vanroose Registered User Join Date: Sep 2004Location: BelgiumPosts: 602 Essentially there is no equivalent in DB2.The logic behind this being that there is no "natural" rowid attached to a certain row: a certain row can be the 4th result row from a certain query, but the same row could as well be the 6th row from the SAME query on the SAME table when the optimizer chose to implement the query differently! (E.g., with or without the use of an index.)
Typically you probably wanted to change a value in the row with rowid=4 because some (independent) program logic found out that this row had a certain property, i.e., "where" condition.In that case you could do one of three things:1. Add that condition to your UPDATE statement:update mytab set c1 = -c1 where CONDITION;2. Suppose you are running through a result table with a cursor, and at a certain iteration the condition applies for the current row; in that case useupdate mytab set c1 = -c1 where CURRENT OF cursorname;3. Use a subquery to produce the primary key values of mytab that satisfy the condition:update mytab set c1 = -c1 where mytab.pkey IN ( SELECT pkey FROM mytab WHERE CONDITION );__________________--_Peter Vanroose,__IBM Certified Database Administrator, DB2 V8 z/OS__IBM Certified Application Developer, DB2 V8__ABIS Training and Consulting__http://www.abis.be/

Every record stored has a ROWID; which is a combination of page(block)#,slot #(within a page) and file#(and optionally object#). ROWID is the fastest way of getting to a record; is used by indexes. Oracle provides it as a pseudocolumn; db2 does NOT provide rowid. Rowid is fixed,constant, immutable till you do a REORG. If you analyze some records and you want to modify or select those records again, you should store their rowids so that you can get to them most efficiently. ROWNUM is different; IN db2 rowid is 4 bytes; 3 for page# and one for slot#; one byte, 8 bits can go up to 255; that's why you have 255 record limit per page; in VIPER you have 4byte page and 2 byte slot#;so you have bigger limits on table size and # of records per page.