oracle rowid pseudocolumn - anything like that in db2? 1

[ddiamond]

Oracle contains a pseudocolumn called rowid which returns a unique id for every row in a table. In procedual sql, this is often more convenient than a compound primary key when looping through a cursor and attempting to update the current record. Does DB2 have anything equivalent?

 

[BrianTyler]

Look at the Graeme Burchell DB2 cookbook.

You will find the explanation in the OLAP functions.

A few threads down, Tiers also answered this question, when posed by kkitt.

(If you don't have the cookbook then get it. Search for Graeme's name and you will find the link)

 

[MarcLodge]

ddiamond,
Brian is referring to thread178-1166423.

Graeme Birchall's cookbook has reently moved. It can now be found at

http://mysite.verizon.net/Graeme_Birchall/cookbook/DB2V82CK.PDF,

and is a very well written manual. Wish they were all like this!

 

[ddiamond]

The rownumber() function is not what I am looking for. I am not trying to number my results; I am trying to save a unique value of a row for later use in an update statement. In oracle you can use rowid for this purpose instead of a primary key.

Here is an oracle example:

DECLARE
  UpdateCount    NUMBER      := 1;

  CURSOR cr IS
    SELECT policy_generic_key, limit_type, rowid
    FROM policy_xs_underlying
    ORDER BY policy_generic_key, limit_type;

BEGIN
  FOR rec IN cr
  LOOP
    UPDATE policy_xs_underlying
    SET limit_type_seq = 0
    WHERE rowid = rec.rowid;
      
    IF UpdateCount>1000 THEN    
      UpdateCount:=1;
      COMMIT;
    ELSE
      UpdateCount:=UpdateCount+1;
    END IF;
  END LOOP;
  
  COMMIT;
END;

Notice I'm using the pseudo column rowid as if it were a primary key, but it is not. It is simply a property of the row that can be used in place of a primary key if one does not exist.

 

[blom0344]

ddiamond,

I really do not know if DB2 has anything similar.
However i am curious what would be the use of such a dummy for updates. I am used to updating records based on some sort of logic. If you number your dataset with a set of unique dummy keys, what sort of logic can you invent to make meaningful updates?

Ties Blom

 

[gregsimpson]

ddiamond,

to the best of my knowledge it does not.

Greg

 

[MarcLodge]

ddiamond,

I'm not totally certain I understand exactly what you are after, but could you define an identity column to the table? I have a feeling that this might be what you are after. Have a look a Graeme Birchall's cookbook p267 and/or Craig Mullins here

Hope this helps.

Marc

 

[ddiamond]

MarcLodge,

Yes an identity column would work, or putting a primary key in the where portion of the update statement would work. In Oracle the rowid is used for convenience and or performance. The rowid is essentially a pointer that points directly to the physical address of the row you are after. That way the sql engine can go directly to the correct record without even traversing an index. But it sounds like DB2 has nothing equivalent. Sorry for the obscure question. I looked at Graeme Birchall's cookbook p267 as you suggested, and it looks like that would produce similar results. Thanks.

 

[juliane26]

ddiamond,
when using cursors, why not using

update ... where current of ...

setting a commit counter then is also no problem.
then you could save the trouble of using artificial rowids.

Juliane

 

[ddiamond]

Juliane,

Thanks. Looks like "current of" is exactly what I was looking for.