Thema:

Kommentar:
: Hallo Wolfgang, : Quelle IBM: : Abstract: How do I generate primary keys automatically? : DB2 has numerous ways of generating primary keys. DB2 has external row ID's only on OS/390, but you can use GENERATED IDENTITY COLUMNS, functions and triggers to assign unique values to rows. GENERATED IDENTITY COLUMNS are supported in DB2 Version 7 or later. Version 7.2 supports SEQUENCE columns. : These techniques work in all supported DB2 Versions for DB2 on Windows, UNIX and OS/2: : First, if the column is a primary key, define it as not null with default (the trigger will always overwrite the default value, but this will allow rows to be inserted without a dummy value for the column). : a. CREATE TRIGGER AutoIncrement NO CASCADE BEFORE INSERT ON Foobar REFERENCING NEW AS n FOR EACH ROW MODE DB2SQL SET (n.col1) = (SELECT COALESCE(MAX(col1),0) + 1 FROM Foobar) : b. DB2 Universal Database also has a function called GENERATE_UNIQUE: : >>-GENERATE_UNIQUE--(--)--------------------------------------->< : The GENERATE_UNIQUE function returns a bit data character string 13 bytes long (CHAR(13) FOR BIT DATA) that is unique compared to any other execution of the same function. : c. for a trigger that handle multi-row inserts: : Look in the samples section for the sample function called db2Udf. : In it you will find a method call ctr. : Load the function as follows: : CREATE FUNCTION NULLID.COUNTER() RETURNS INT EXTERNAL NAME 'DB2Udf!ctr' LANGUAGE JAVA PARAMETER STYLE DB2GENERAL NO SQL NOT DETERMINISTIC NOT FENCED SCRATCHPAD NO EXTERNAL ACTION; : Then do your insert with a trigger of (this is mine for my ADDRESSES Table (adjust to fit)): : CREATE TRIGGER NULLID.ADDRESSAUTOINC NO CASCADE BEFORE INSERT ON NULLID.ADDRESSES REFERENCING NEW AS n FOR EACH ROW MODE DB2SQL BEGIN ATOMIC SET (n.ADDR_UID ) = (SELECT value(MAX(ADDR_UID),0) +NULLID.COUNTER() : FROM NULLID.ADDRESSES); END : Then do your insert. : d. Use a scalar subquery expression: : INSERT INTO Foobar (key_col, ...) VALUES (COALESCE((SELECT MAX(key_col) FROM Foobar) +1, 0) ...); : ===================================================== : Außerdem gibt es einen ausgezeichneten Aufsatz aus dem IBM Labor zu dem Thema "Generating unique values in DB2 Universal Database" mit folgendem Inhalt: : - GENERATE_UNIQUE function available in DB2 UDB Version 6.1 and later : - IDENTITY column in the CREATE TABLE statement available in DB2 UDB Version 7.1 and later : - SEQUENCE object available in DB2 UDB Version 7.2 : Folge dem Link unten. : Viel Erfolg : Gruß : Gernot

Optionale Link URL:   
Link Titel:                  
Optionale Image URL: