Can we auto generate key values in DB2?

[Mainframe help]

Hi,

Can we auto generate key values in DB2? If yes, can you please guide me how to do that or if there is good tutorial around here, please guide me to that.

Thanks,

[enrico-sorichetti]

Can we auto generate key values in DB2?

while the question might be clear to You
it is not the same for us.

if You want good answers learn how to post good/CLEAR questions

[Chandan Yadav]

Hi,

Check for IDENTITY COLUMNS in DB2.. you will find ample info on google for this

Regards,
Chandan

[Anuj Dhawan]

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.

[Chandan Yadav]

Nicely put Anuj..

Just addition to IDENTITY columns, you cam mention one more parameter INCREMENT BY. The values advance by the INCREMENT BY value in ascending or descending order for each key.

Correct me if I am wrong. As per manual
A row ID is a value that uniquely identifies a row in a table. A column or a host variable can have a row ID data type.

A ROWID column enables queries to be written that navigate directly to a row in the table because the column implicitly contains the location of the row. Each value in a ROWID column must be unique. Although the location of the row might change, for example across a table space reorganization

Because of the line in Bold I am bit hesitant to use RowId if I am using that column as primary key because primary key values may get changed after reorg in this case

Thanks,
Chandan

[Anuj Dhawan]

Chandan - your hesitance about ROWID being used as primary key is something which usually comes up in DB2 discussions - whether it is a good approach or bad, is debatable. Your perception about "location" in bold line seems to convey different message than it really means.

Although the location of the row might change, as you've said - for example, across a table space reorganization. BUT DB2® does maintain the internal representation of the row ID value permanently. With ROWID you can have following two cases. It's in to the picture when:

1. a unique value which is generated when a row is inserted.
2. 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.
3. 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.

[Chandan Yadav]

Thanks for the detailed information Anuj..

[Anuj Dhawan]

You're welcome Chandan.

[Mainframe help]

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.

Thanks for the explanation but can we not use IDENTITY columns and ROWID to auto generate key values ?

[Anuj Dhawan]

You should get in touch with your DBA to find the answer for your peculiar situation. Why and why-not about it are already explained to you.