| |
|
If this is your first visit, be sure to check out the FAQ by clicking the link above.
You may have to register before you can post: click the register link above to proceed.
To start viewing messages, select the forum that you want to visit from the selection below.
|
 |
12-03-09, 23:32
|
|
Registered User
|
|
Join Date: Dec 2009
Posts: 6
|
|
|
retrieving identity column for current insert.
|
|
Do you know of a slick way to do this:
CREATE TABLE A
ID INT IDENTITY,
ID_MIRROR CHAR(20)
INSERT INTO A (ID_MIRROR) VALUES ( 'PRE' || CHAR(A.ID))
I basically want what ever is in the ID column to be mirrored into the
ID_MIRROR column as a character, but prefixed with 'PRE'.
My actual insert is more complex, but if the above works, then I can
get my example to work.
I need to do this in one insert rather than a INSERT and UPDATE
because one of the other columns has a trigger and I do not want the
trigger to fire twice.
I tried this:
insert into A (id_mirror)
VALUES (CONCAT('PRE', CHAR(identity_val_local())))
But it fails because identity_val_local() is null. I suspect it is
returning the previous insert, and because there is no
previous insert it is returning null.
I also tried, this:
insert into A (id_mirror)
VALUES (CONCAT('PRE', CHAR(scope_identity())))
But scope_identity() is apparently not a db2 function. Must be for some
other database.
|
|
12-04-09, 00:23
|
|
Registered User
|
|
Join Date: Oct 2009
Posts: 24
|
|
create view a1 as select id, 'PRE'||char(id) as id_mirror from a
|
|
12-04-09, 00:30
|
|
Registered User
|
|
Join Date: Dec 2009
Posts: 6
|
|
|
creating view a good idea, but will not work for my case
|
|
The view will not work for my case because there is already data in the id_mirror column that does not mirror the id column.
All data going forward will mirror the id column.
Creating solution tho. I am trying to think of a way to make it work like leaving id_mirror null and then use COLLEASE to have the view only fill it in if it is null.
I would rather not rename this table and then create a new view tho so I am hoping that someone will respond with the way to get the current identity column value.
|
|
12-04-09, 00:48
|
|
Registered User
|
|
Join Date: Feb 2008
Location: Japan
Posts: 2,193
|
|
Please write exactly what you did and what result(including error messages) you got.
Please don't let me a guess.
I got error with this:
Code:
------------------------------ Commands Entered ------------------------------
CREATE TABLE A
ID INT IDENTITY,
ID_MIRROR CHAR(20);
------------------------------------------------------------------------------
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0104N An unexpected token "CREATE TABLE A ID INT IDENTITY, I" was found
following "BEGIN-OF-STATEMENT". Expected tokens may include:
"<create_variable>". SQLSTATE=42601
|
|
12-04-09, 01:14
|
|
Registered User
|
|
Join Date: Dec 2009
Posts: 6
|
|
|
exact create statement.
Here is the exact create statement (thanks for calling me out on not providing it in the first place):
CREATE TABLE A
( ID BIGINT NOT NULL GENERATED BY DEFAULT AS IDENTITY (
START WITH +1
INCREMENT BY +1
NO MINVALUE
NO MAXVALUE
NO CYCLE
CACHE 20
NO ORDER )
, ID_MIRROR CHAR(20))
All three of the INSERT statements fail. I would like help finding an INSERT statement that works.
Thank you
|
|
12-04-09, 01:48
|
|
Registered User
|
|
Join Date: Dec 2009
Posts: 6
|
|
Here is a concise repost of the question:
I have created a table like this:
CREATE TABLE A
( ID BIGINT NOT NULL GENERATED BY DEFAULT 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 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
|
|
12-04-09, 01:57
|
|
Registered User
|
|
Join Date: Feb 2008
Location: Japan
Posts: 2,193
|
|
Here is an example tested on DB2 9.7 for Windows:
Code:
------------------------------ Commands Entered ------------------------------
DROP TABLE A;
------------------------------------------------------------------------------
DB20000I The SQL command completed successfully.
------------------------------ Commands Entered ------------------------------
CREATE TABLE A
( ID BIGINT NOT NULL GENERATED BY DEFAULT AS IDENTITY (
START WITH +1
INCREMENT BY +1
NO MINVALUE
NO MAXVALUE
NO CYCLE
CACHE 20
NO ORDER )
, ID_MIRROR CHAR(20));
------------------------------------------------------------------------------
DB20000I The SQL command completed successfully.
------------------------------ Commands Entered ------------------------------
INSERT INTO A (ID_MIRROR) VALUES ( 'PRE' || IDENTITY_VAL_LOCAL());
------------------------------------------------------------------------------
DB20000I The SQL command completed successfully.
------------------------------ Commands Entered ------------------------------
SELECT * FROM A;
------------------------------------------------------------------------------
ID ID_MIRROR
-------------------- --------------------
1 PRE1
1 record(s) selected.
|
|
12-04-09, 02:03
|
|
Registered User
|
|
Join Date: Dec 2009
Posts: 6
|
|
|
Thanks
That looks like exactly what I need.
What reference are you looking at to find the functions that are available?
|
|
12-04-09, 02:08
|
|
Registered User
|
|
Join Date: Feb 2008
Location: Japan
Posts: 2,193
|
|
Quote:
That looks like exactly what I need.
What reference are you looking at to find the functions that are available?
|
I saw "IBM DB2 9.7 for Linux, UNIX, and Windows SQL Reference, Volume 1".
Here is more example:
Code:
------------------------------ Commands Entered ------------------------------
INSERT INTO A(ID) VALUES DEFAULT;
------------------------------------------------------------------------------
DB20000I The SQL command completed successfully.
------------------------------ Commands Entered ------------------------------
SELECT * FROM A;
------------------------------------------------------------------------------
ID ID_MIRROR
-------------------- --------------------
1 PRE1
2 -
2 record(s) selected.
------------------------------ Commands Entered ------------------------------
SET INTEGRITY FOR A OFF;
------------------------------------------------------------------------------
DB20000I The SQL command completed successfully.
------------------------------ Commands Entered ------------------------------
ALTER TABLE A
ALTER COLUMN ID_MIRROR SET GENERATED ALWAYS AS ('PRE' || id);
------------------------------------------------------------------------------
DB20000I The SQL command completed successfully.
------------------------------ Commands Entered ------------------------------
SET INTEGRITY FOR A IMMEDIATE CHECKED NOT INCREMENTAL FORCE GENERATED FULL ACCESS;
------------------------------------------------------------------------------
DB20000I The SQL command completed successfully.
------------------------------ Commands Entered ------------------------------
SELECT * FROM A;
------------------------------------------------------------------------------
ID ID_MIRROR
-------------------- --------------------
1 PRE1
2 PRE2
2 record(s) selected.
------------------------------ Commands Entered ------------------------------
INSERT INTO A(id) VALUES DEFAULT;
------------------------------------------------------------------------------
DB20000I The SQL command completed successfully.
------------------------------ Commands Entered ------------------------------
SELECT * FROM A;
------------------------------------------------------------------------------
ID ID_MIRROR
-------------------- --------------------
1 PRE1
2 PRE2
3 PRE3
3 record(s) selected.
|
|
12-04-09, 09:37
|
|
Registered User
|
|
Join Date: Feb 2008
Location: Japan
Posts: 2,193
|
|
Quote:
------------------------------------------------
insert into A (id_mirror)
VALUES (CONCAT('PRE', CHAR(identity_val_local())))
ID_MIRROR is NULL, subsequent inserts are previous value of ID.
|
You are right!
The value of identity_val_local() remains until it is replaced by the next assigned by INSERT.
In my previous test, I did CREATE/INSERT table A before the sample I showed.
So, the value of identity_val_local() was remained and it was accidentally same as the value of ID.
I want to recommend to use "GENERATED ALWAYS".
Here is an example which identity_val_local() is not equal to ID.
Even if I did DROP/CREATE table, identity_val_local() was not reset.
Code:
------------------------------ Commands Entered ------------------------------
connect to SAMPLE ;
------------------------------------------------------------------------------
Database Connection Information
Database server = DB2/NT 9.7.0
SQL authorization ID = DB2ADMIN
Local database alias = SAMPLE
A JDBC connection to the target has succeeded.
------------------------------ Commands Entered ------------------------------
DROP TABLE A;
------------------------------------------------------------------------------
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0204N "DB2ADMIN.A" is an undefined name. SQLSTATE=42704
------------------------------ Commands Entered ------------------------------
CREATE TABLE A
( ID BIGINT NOT NULL GENERATED BY DEFAULT AS IDENTITY (
START WITH +1
INCREMENT BY +1
NO MINVALUE
NO MAXVALUE
NO CYCLE
CACHE 20
NO ORDER )
, ID_MIRROR CHAR(20));
------------------------------------------------------------------------------
DB20000I The SQL command completed successfully.
------------------------------ Commands Entered ------------------------------
INSERT INTO A (ID_MIRROR) VALUES ( 'PRE' || IDENTITY_VAL_LOCAL());
------------------------------------------------------------------------------
DB20000I The SQL command completed successfully.
------------------------------ Commands Entered ------------------------------
SELECT * FROM A;
------------------------------------------------------------------------------
ID ID_MIRROR
-------------------- --------------------
1 -
1 record(s) selected.
------------------------------ Commands Entered ------------------------------
INSERT INTO A (ID_MIRROR) VALUES ( 'PRE' || IDENTITY_VAL_LOCAL());
------------------------------------------------------------------------------
DB20000I The SQL command completed successfully.
------------------------------ Commands Entered ------------------------------
SELECT * FROM A;
------------------------------------------------------------------------------
ID ID_MIRROR
-------------------- --------------------
1 -
2 PRE1
2 record(s) selected.
------------------------------ Commands Entered ------------------------------
DROP TABLE A;
------------------------------------------------------------------------------
DB20000I The SQL command completed successfully.
------------------------------ Commands Entered ------------------------------
CREATE TABLE A
( ID BIGINT NOT NULL GENERATED BY DEFAULT AS IDENTITY (
START WITH +1
INCREMENT BY +1
NO MINVALUE
NO MAXVALUE
NO CYCLE
CACHE 20
NO ORDER )
, ID_MIRROR CHAR(20));
------------------------------------------------------------------------------
DB20000I The SQL command completed successfully.
------------------------------ Commands Entered ------------------------------
INSERT INTO A (ID_MIRROR) VALUES ( 'PRE' || IDENTITY_VAL_LOCAL());
------------------------------------------------------------------------------
DB20000I The SQL command completed successfully.
------------------------------ Commands Entered ------------------------------
SELECT * FROM A;
------------------------------------------------------------------------------
ID ID_MIRROR
-------------------- --------------------
1 PRE2
1 record(s) selected.
This is an example to set GENERATED ALWAYS AS ('PRE' || id) and reset the value of ID_MIRROR.
Code:
------------------------------ Commands Entered ------------------------------
SELECT * FROM A;
------------------------------------------------------------------------------
SELECT * FROM A
ID ID_MIRROR
-------------------- --------------------
1 PRE2
1 record(s) selected.
------------------------------ Commands Entered ------------------------------
SET INTEGRITY FOR A OFF;
------------------------------------------------------------------------------
DB20000I The SQL command completed successfully.
------------------------------ Commands Entered ------------------------------
ALTER TABLE A
ALTER COLUMN ID_MIRROR SET GENERATED ALWAYS AS ('PRE' || id);
------------------------------------------------------------------------------
DB20000I The SQL command completed successfully.
------------------------------ Commands Entered ------------------------------
SET INTEGRITY FOR A IMMEDIATE CHECKED NOT INCREMENTAL FORCE GENERATED FULL ACCESS;
------------------------------------------------------------------------------
DB20000I The SQL command completed successfully.
------------------------------ Commands Entered ------------------------------
SELECT * FROM A;
------------------------------------------------------------------------------
ID ID_MIRROR
-------------------- --------------------
1 PRE1
1 record(s) selected.
------------------------------ Commands Entered ------------------------------
INSERT INTO A(id) VALUES DEFAULT;
------------------------------------------------------------------------------
DB20000I The SQL command completed successfully.
------------------------------ Commands Entered ------------------------------
SELECT * FROM A;
------------------------------------------------------------------------------
ID ID_MIRROR
-------------------- --------------------
1 PRE1
2 PRE2
2 record(s) selected.
|
Last edited by tonkuma; 12-04-09 at 09:40.
|
12-04-09, 11:41
|
|
Registered User
|
|
Join Date: Dec 2009
Posts: 6
|
|
|
GENERATED ALWAYS vs GENERATED BY DEFAULT
I only want the ID_MIRROR to be updated for new inserts.
Do I use BY DEFAULT rather than ALWAYS?
|
|
12-04-09, 20:38
|
|
Registered User
|
|
Join Date: Feb 2008
Location: Japan
Posts: 2,193
|
|
Quote:
|
I only want the ID_MIRROR to be updated for new inserts.
|
You can use IMMEDIATE UNCHECKED, like this
SET INTEGRITY FOR A GENERATED COLUMN IMMEDIATE UNCHECKED;
But, there are long warnings in "IBM DB2 9.7 for Linux, UNIX, and Windows SQL Reference, Volume 2".
Quote:
v Warning about the use of the IMMEDIATE UNCHECKED clause:- This clause is intended to be used by utility programs, and its use by
application programs is not recommended. If there is data in the table that
does not meet the integrity specifications that were defined for the table, and
the IMMEDIATE UNCHECKED option is used, incorrect query results might
be returned.
.....
.....
|
Quote:
|
Do I use BY DEFAULT rather than ALWAYS?
|
I don't think so.
You can try it yourself.
|
|
12-07-09, 15:25
|
|
Registered User
|
|
Join Date: Dec 2008
Location: Toronto, Canada
Posts: 381
|
|
why don't you make ID_MIRROR col generated? in this case you don't have to worry abt inserting into it. Something like this:
ID_MIRROR CHAR(20) generated always as (‘PRE’ || char(ID))
__________________
DB2 v9.5 ESE on AIX v6.1/ v9./10 on z/OS
|
|
| Thread Tools |
Search this Thread |
|
|
|
| Display Modes |
 Linear Mode
|
Posting Rules
|
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
HTML code is Off
|
|
|
|
|
dBforums
>
retrieving identity column for current insert.
