470,567 Members | 1,497 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 470,567 developers. It's quick & easy.

How can I generate a sequence where CREATE SEQUENCE is not supported?

On my local installation of DB2 on AS/400 (BTW., how do I find out
which version od DB2 I have?) I have created these SQL statements:

CREATE SEQUENCE a.seq AS INTEGER
START WITH 8955
INCREMENT BY 1

INSERT INTO a.table1 (id, name) SELECT (next value for a.seq, name)
from a.table2

This works. However I need to execute it at a server with an older
version of DB2 (I don't know which), which to my dissapointment doesn't
allow me to create a sequence. As a workaround, I created a trigger
which uses a function (function is maybe unneccessary but I was unable
to get it to work from the trigger alone). So this also works locally
on my DB2 installation, but not on the remote DB2 installation!:

CREATE FUNCTION a.f_GENVAL()
RETURNS INTEGER
LANGUAGE SQL
NO EXTERNAL ACTION
NOT DETERMINISTIC
RETURN (SELECT MAX(id) from a.table1)

--------- Token MAX was not valid. Valid tokens: + ) -.

I have by now exhausted my ideas. How old would DB2 have to be to not
support MAX?

My question is, how can I reliably make sure that my inserted rows get
IDs from 8955 onwards, each greater by 1 from the last? The solution (I
hope there is one!) should also be as simple and standard as possible,
so I can run it on the problematic remote DB2.

Jan 19 '07 #1
1 4996
si*********@gmail.com wrote:
On my local installation of DB2 on AS/400 (BTW., how do I find out
which version od DB2 I have?) I have created these SQL statements:

CREATE SEQUENCE a.seq AS INTEGER
START WITH 8955
INCREMENT BY 1

INSERT INTO a.table1 (id, name) SELECT (next value for a.seq, name)
from a.table2

This works. However I need to execute it at a server with an older
version of DB2 (I don't know which), which to my dissapointment doesn't
allow me to create a sequence. As a workaround, I created a trigger
which uses a function (function is maybe unneccessary but I was unable
to get it to work from the trigger alone). So this also works locally
on my DB2 installation, but not on the remote DB2 installation!:

CREATE FUNCTION a.f_GENVAL()
RETURNS INTEGER
LANGUAGE SQL
NO EXTERNAL ACTION
NOT DETERMINISTIC
RETURN (SELECT MAX(id) from a.table1)

--------- Token MAX was not valid. Valid tokens: + ) -.

I have by now exhausted my ideas. How old would DB2 have to be to not
support MAX?

My question is, how can I reliably make sure that my inserted rows get
IDs from 8955 onwards, each greater by 1 from the last? The solution (I
hope there is one!) should also be as simple and standard as possible,
so I can run it on the problematic remote DB2.
I don't think your problem is MAX
Your problem is the scalar subquery in the SQL Function in that version
of DB2
Have you considered using an IDENTITY column instead? I think that
feature goes further back on all platform.
Certainly on DB2 for LUW any supported platform has both.
IDENTITY was introduced in DB2 V7.1 and Sequence DB2 V7.2.
I think DB2 for zOS had IDENTITY in DB2 V7 as well.
Can't comment of iSeries.

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

WAIUG Conference
http://www.iiug.org/waiug/present/Fo...Forum2006.html
Jan 19 '07 #2

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

reply views Thread by Bob Rundle | last post: by
2 posts views Thread by PeterW | last post: by
7 posts views Thread by urban.widmark | last post: by
12 posts views Thread by Jim Michaels | last post: by
8 posts views Thread by Marc | last post: by
1 post views Thread by livre | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.