Results 1 to 8 of 8

Thread: ROWID in DB2

  1. #1
    Join Date
    Jan 2003
    Location
    Bangalore
    Posts
    36

    Unanswered: ROWID in DB2

    I want to have a absolute row identifier for each row in my table, without creating any unque identity column in my table.
    e.g something like ROWID in ORACLE.

    IS there any way to get it?

  2. #2
    Join Date
    Jan 2003
    Posts
    1,605

    Re: ROWID in DB2

    select row_number() over() as row_count, column_name from table_name

    Is this what you want?

    Originally posted by sandips
    I want to have a absolute row identifier for each row in my table, without creating any unque identity column in my table.
    e.g something like ROWID in ORACLE.

    IS there any way to get it?

  3. #3
    Join Date
    Jan 2003
    Location
    Bangalore
    Posts
    36
    Not exactly, because row_number() is query specific, I want to store the rowids in a temporary table and want to use them in another query.
    So it is imperative that the rowid should reflect the absoulte position in the table like ROWID does in ORACLE.

  4. #4
    Join Date
    Jun 2002
    Location
    UK
    Posts
    525
    The rowid in Oracle is only static as long as the table is not restructured, so using this as a unique key might not be considered best practice.

    Is there any reason that you can't include an identity column?

  5. #5
    Join Date
    Jan 2003
    Location
    Bangalore
    Posts
    36

    getting unique identity of a row

    I am talking about a situation where there is no question of restructuring the table in production environment.

    I am not sure whether I can include one identity key in my table.
    Actually I thought of adding the identity column as the last resort to my problem.
    any other solution?
    thanx and regards
    Sandip

  6. #6
    Join Date
    Jun 2002
    Location
    UK
    Posts
    525
    Does your production table not have a primary key? Can you expand a little on the exact circumstances?

    Thanks, Damian.

  7. #7
    Join Date
    Jan 2003
    Location
    Bangalore
    Posts
    36

    Identity field problem

    Sorry for the delay in reply,
    The problem is my production table has a composite primary key and I have some 2 crores of records in the table. so generating the additional identity key means
    4 bytes per int field multiplied by 2 crores record = 80 MB of additional memory requirement for an additional column which is at best can be averted by using some other means of SQL.

  8. #8
    Join Date
    Oct 2001
    Location
    Bangalore
    Posts
    186
    Hi,

    As per your circumstances you can only use the first solution and store it in a temp table.
    Otherwise what my experience says with DB2 is that there is no way out, at the cost of Memory.

    Cheers,
    Prashant

Posting Permissions

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