473,237 Members | 1,168 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,237 software developers and data experts.

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 5135
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

0
by: Bob Rundle | last post by:
I'm using the XML Schema Designer to create a dataset. I can't get the relationships to work from the XML. To get them to work, I have to explicitly code them in. What's wrong here? Here is...
2
by: PeterW | last post by:
I have an xml file from which I want to generate an xsd schema and at a later stage a cs class. The xml file has a mix of defined namespaces and also an empty namespace. These are defined as...
3
by: rajdb2 | last post by:
Can we create an alias for a sequence in db2 udb? We can do it in oracle. But the same thing is not working in db2. I can create an alias, but when i am trying to use it, there is an error that the...
1
by: hikums | last post by:
I am posting this here, just in case anyone may need this. Step 1: CREATE SEQUENCE ID_SEQ START WITH 1050000 INCREMENT BY 1 MAXVALUE 9999999 NO CYCLE NO CACHE ORDER
1
by: T | last post by:
Hi I am using Windows 2000(SP4) and recently had MS ACCESS 2000 (9.0.2720) installed. My problem is I cannot create or open any database without getting the following message "Select Collating...
7
by: urban.widmark | last post by:
Hello We are having some problems with triggers, sequences and union all in V8 on code that worked fine in V7. Was wondering if someone else has seen this and/or knows what to do. A trigger...
12
by: Jim Michaels | last post by:
I need to generate 2 random numbers in rapid sequence from either PHP or mysql. I have not been able to do either. I get the same number back several times from PHP's mt_rand() and from mysql's...
8
by: Les Caudle | last post by:
Using this command line to run XSD.exe xsd /c /o:outputDir input.xsd /f on the following XSD <?xml version="1.0"?> <schema xmlns="http://www.w3.org/2001/XMLSchema"...
8
by: Marc | last post by:
Hi all, I have to generate and send to a printer many 6 digit alphanumeric strings. they have to be unique but I cannot check in a database or something like that if it have already been printed....
0
by: abbasky | last post by:
### Vandf component communication method one: data sharing ​ Vandf components can achieve data exchange through data sharing, state sharing, events, and other methods. Vandf's data exchange method...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM). In this month's session, the creator of the excellent VBE...
0
by: fareedcanada | last post by:
Hello I am trying to split number on their count. suppose i have 121314151617 (12cnt) then number should be split like 12,13,14,15,16,17 and if 11314151617 (11cnt) then should be split like...
0
Git
by: egorbl4 | last post by:
Скачал я git, хотел начать настройку, а там вылезло вот это Что это? Что мне с этим делать? ...
1
by: davi5007 | last post by:
Hi, Basically, I am trying to automate a field named TraceabilityNo into a web page from an access form. I've got the serial held in the variable strSearchString. How can I get this into the...
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: Aftab Ahmad | last post by:
Hello Experts! I have written a code in MS Access for a cmd called "WhatsApp Message" to open WhatsApp using that very code but the problem is that it gives a popup message everytime I clicked on...
0
by: Aftab Ahmad | last post by:
So, I have written a code for a cmd called "Send WhatsApp Message" to open and send WhatsApp messaage. The code is given below. Dim IE As Object Set IE =...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.