how to use the value of an Identity column for a diffent column in the same insert

I have created a table like this:

CREATE TABLE A
( ID BIGINT NOT NULL GENERATED BY DE开发者_如何转开发FAULT AS IDENTITY (
START WITH +1
INCREMENT BY +1
NO MINVALUE
NO MAXVALUE
NO CYCLE
CACHE 20
NO ORDER )
, ID_MIRROR CHAR(20))

I would like to do an insert such that ID would be automatically set, and ID_MIRROR would be what is in ID, but prefixed with 'PRE'.

I have unsuccessfully tried the following:

INSERT INTO A (ID_MIRROR) 
VALUES ( 'PRE' || CHAR(A.ID))

Error 12/4/2009 6:43:08 AM 0:00:00.296 DB2 Database Error: ERROR [42703] [IBM][DB2/AIX64] SQL0206N "A.ID" is not valid in the context where it is used. SQLSTATE=42703 1 0

insert into A (id_mirror)
VALUES (CONCAT('PRE', CHAR(identity_val_local())))

ID_MIRROR is NULL, subsequent inserts are previous value of ID.

insert into A (id_mirror)
VALUES (CONCAT('PRE', CHAR(scope_identity())))

Error 12/4/2009 6:11:11 AM 0:00:00.234 DB2 Database Error: ERROR [42884] [IBM][DB2/AIX64] SQL0440N No authorized routine named "SCOPE_IDENTITY" of type "FUNCTION" having compatible arguments was found. SQLSTATE=42884 1 0

---

Another forum answered the question like this:

INSERT INTO A (ID_MIRROR) VALUES ( 'PRE' || IDENTITY_VAL_LOCAL());

---

Why would you ever need to do this when you could simply create this column on an ad hoc basis any time you wanted to, in a SELECT statement?