Anuj Dhawan wrote:Chandan is correct. Apart from adding a columns IDENTITY COLUMNS you can also add a column with data type as ROW ID. Both of them can serve the purpose for you.
ROWID columns can be defined as GENERATED ALWAYS or GENERATED BY DEFAULT. If you have provided GENERATED ALWAYS that would mean that DB2 will generate a value for the column. You CANNOT insert data into that column. If the column is defined as GENERATED BY DEFAULT, you can insert a value; DB2 provides a default value if you do not supply a value. Consider the below example taken from the manual, suppose that tables T1 and T2 have two columns: an integer column and a ROWID column. For the following statement to execute successfully, ROWIDCOL2 must be defined as GENERATED BY DEFAULT.
Code: Select all
INSERT INTO T2 (INTCOL2,ROWIDCOL2)
SELECT INTCOL1, ROWIDCOL1 FROM T1
;
If ROWIDCOL2 is defined as GENERATED ALWAYS, you cannot insert the ROWID column data from T1 into T2, but you can insert the integer column data. To insert only the integer data, use one of the following methods:
Specify only the integer column in your INSERT statement:
Code: Select all
INSERT INTO T2 (INTCOL2)
SELECT INTCOL1 FROM T1;
Specify the OVERRIDING USER VALUE clause in your INSERT statement to tell DB2 to ignore any values that you supply for system-generated columns:
Code: Select all
INSERT INTO T2 (INTCOL2,ROWIDCOL2) OVERRIDING USER VALUE
SELECT INTCOL1, ROWIDCOL1 FROM T1;
The values that DB2 generates for an IDENTITY COLUMNs, about which Chandan has mentioned, again depends on how the column is defined. The START WITH parameter determines the first value that DB2 generates. The MINVALUE and MAXVALUE parameters determine the minimum and maximum values that DB2 generates. The CYCLE or NO CYCLE parameter determines whether DB2 wraps values when it has generated all values between the START WITH value and MAXVALUE, if the values are ascending, or between the START WITH value and MINVALUE, if the values are descending.
Although the location of the row might change, as you've said - for example, across a table space reorganization. BUT DB2® does maintains the internal representation of the row ID value permanently. With ROWID you can have following two cases. It's in to the picture when:
a. a unique value which is generated when a row is inserted.
b.and in another case when a table which is altered to include a ROWID column. The column that is derived from the location of the row when the alter happens - it contains the DBID, PSID, page# and RID of the row. This is what is stored in the row.
c.a value which is used in SQL when the ROWID is used. This contains the aforementioned unique value, the current SYSTABLEPART.EPOCH, and the row's current page# and RID. It should be 22 bytes , though it depends on the size of page#.
If you fetch a row's ROWID you get the 22 byte value. If you use that to find a row then if the EPOCH is still current you can get direct access using the page#/RID and if the EPOCH is no longer current (a REORG has happened, per the point of our discussion), then the unique value can be used to retrieve the row. Either via an index (provided they exist) or tablespace scan.
So after a REORG, where it all started, this is the situation:
1. the unique part of the ROWID stays the same
2.a ROWID value, before REORG, fetched by an application will still be valid, but cannot be used for direct access.