Results 1 to 4 of 4

Thread: rowid in db2

  1. #1
    Join Date
    Sep 2003

    Unanswered: rowid in db2

    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.

  2. #2
    Join Date
    Sep 2004
    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 use
    update 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 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting

  3. #3
    Join Date
    Sep 2003
    well said. I was wondering about option2 myself so let me go ahead and explore that option.

    thanks for your response.

  4. #4
    Join Date
    Sep 2003
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts