Incrementing sequences in the same select statement

[Antanas]

The problem is that when AddID is used multiple times in the same
select statement, it returns the same value in all places. How could I
force function AddID to increment OBJECTID sequence?

Here is a basic sample of usage:
SELECT AddID(), AddID(), AddID(), column1 from table1
Here is how AddID looks like:

CREATE FUNCTION Admin.AddID()
RETURNS INTEGER
F1: BEGIN ATOMIC
DECLARE I INTEGER;
SET I = (NEXT VALUE FOR OBJECTID);
RETURN I;
END

[Artur]

Les's assume, that multiple call to AddID() within the same select is
incrementing the sequence. The next question is: what was generated
(hint: the order of incrementing)?

You can increment the sequence and assign generated values to variables
sequentially outside of the select.

But, what is your real (business) need for that?

-- Artur Wronski

[Antanas]

I need incremented values to be displayed inside select statement.
Outside of select statement incrementation and assignment to variables
is not an option for me.

[Knut Stolze]

Antanas wrote:

The problem is that when AddID is used multiple times in the same
select statement, it returns the same value in all places. How could I
force function AddID to increment OBJECTID sequence?

Here is a basic sample of usage:
SELECT AddID(), AddID(), AddID(), column1 from table1
Here is how AddID looks like:

CREATE FUNCTION Admin.AddID()
RETURNS INTEGER
F1: BEGIN ATOMIC
DECLARE I INTEGER;
SET I = (NEXT VALUE FOR OBJECTID);
RETURN I;
END

I already said in the other news group that you could use an external
function. And here is the code I just wrote to verify this:

---------------------------------------------------------------
import java.sql.*;

public class Test extends COM.ibm.db2.app.UDF {
public void getNextSequenceId(int value) throws Exception {
Connection con = getConnection();
Statement stmt = con.createStatement();
ResultSet rs = stmt.executeQuery("VALUES NEXT VALUE FOR seq");
while (rs.next()) {
value = rs.getInt(1);
}
rs.close();
stmt.close();
set(1, value);
}
}
---------------------------------------------------------------
CREATE SEQUENCE seq@

CREATE FUNCTION getNextSeqValue()
RETURNS INTEGER
EXTERNAL NAME 'Test.getNextSequenceId'
LANGUAGE JAVA
PARAMETER STYLE DB2GENERAL
NOT DETERMINISTIC
NO EXTERNAL ACTION
READS SQL DATA
ALLOW PARALLEL@
---------------------------------------------------------------
$ db2 "values ( getnextseqvalue(), getnextseqvalue(), getnextseqvalue() )"

1 2 3
----------- ----------- -----------
9 8 7

1 record(s) selected.

---------------------------------------------------------------

If you want to, you can parameterize the name of the sequence used or you
hard-wire it like I did.

But as Artur said, you might want to explain what you need this for,
especially if you consider that you have no guarantee of the execution
order for the single occurences of the function. In my case above, you see
that the first function is actually called last - again, that's nothing you
can rely on!

--
Knut Stolze
DB2 Information Integration Development
IBM Germany

[Serge Rielau]

Antanas wrote:

The problem is that when AddID is used multiple times in the same
select statement, it returns the same value in all places. How could I
force function AddID to increment OBJECTID sequence?

Here is a basic sample of usage:
SELECT AddID(), AddID(), AddID(), column1 from table1
Here is how AddID looks like:

CREATE FUNCTION Admin.AddID()
RETURNS INTEGER
F1: BEGIN ATOMIC
DECLARE I INTEGER;
SET I = (NEXT VALUE FOR OBJECTID);
RETURN I;
END

Anatanas,

I'm confused.
To me it looks like you posted te solution to your problem yourself.
When you use:
SELECT NEXT VALUE FOR s1 AS X, NEXT VALUES FOR s1 AS Y FROM T
You will receive the same values for X and Y.
(On the "why" I refer you to Oracle. I'd have done it differently.....)
When you wrap the NEXT VALUES FOR s1 into a UDF the invocations are
decoupled and each call to the UDF will give you a different value.
During parsing DB2 detects the multiple sequence expressions and
transforms the query into:
SELECT nv AS X, nv AS Y FROM (SELECT NEXT VALUE FOR s1 AS nv FROM T)
When the UDF is present this code doens't kick it (quite intentionally I
might add).

Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

[Knut Stolze]

Serge Rielau wrote:

Antanas wrote:

The problem is that when AddID is used multiple times in the same
select statement, it returns the same value in all places. How could I
force function AddID to increment OBJECTID sequence?

Here is a basic sample of usage:
SELECT AddID(), AddID(), AddID(), column1 from table1
Here is how AddID looks like:

CREATE FUNCTION Admin.AddID()
RETURNS INTEGER
F1: BEGIN ATOMIC
DECLARE I INTEGER;
SET I = (NEXT VALUE FOR OBJECTID);
RETURN I;
END
Anatanas,

I'm confused.

Me too. ;-)
To me it looks like you posted te solution to your problem yourself.
When you use:
SELECT NEXT VALUE FOR s1 AS X, NEXT VALUES FOR s1 AS Y FROM T
You will receive the same values for X and Y.
(On the "why" I refer you to Oracle. I'd have done it differently.....)
When you wrap the NEXT VALUES FOR s1 into a UDF the invocations are
decoupled and each call to the UDF will give you a different value.

That's due to the BEGIN ATOMIC ... END block? Or just a general UDF thing?

I just saw the above and thought that it did work as the OP said and returns
the same value for both calls to the function. But that is not the case as
I just learned/verified. So it's even easier than my external function
approach.

$ db2 "create sequence seq"
$ db2 "create function x() returns integer begin atomic declare i int; set i
= ( next value for seq ); return i; end"
$ db2 "select x(), x() from sysibm.sysdummy1"

1 2
----------- -----------
1 2

1 record(s) selected.

--
Knut Stolze
DB2 Information Integration Development
IBM Germany

[Antanas]

I was surprised myself today when AddID() started incrementing value
when used multiple times in SELECT statement. Previously it would not,
no matter what I did. The reason for this miracle might be because I
restored my DB from backup, and previous db image might have had some
inconsistency.

[Antanas]

I am using this ADDID() function mutliple time in a single query with
SQL/XML functions.

[Antanas]

I was surprised myself today when AddID() started incrementing value
when used multiple times in SELECT statement. Previously it would not,
no matter what I did. The reason for this miracle might be because I
restored my DB from backup, and previous db image might have had some
inconsistency.

I am using it SQL/XML queries:
SELECT XML2CLOB (XMLELEMENT (NAME "Element1",
XMLELEMENT (NAME "OBJECTID", ADDID()),
XMLELEMENT (NAME "OBJECTID", ADDID()),
XMLELEMENT (NAME "OBJECTID", ADDID())
)
)
FROM TABLE1 WHERE COLUMN1 =1;

Output:
<Element1><OBJECTID>35</OBJECTID><OBJECTID>34</OBJECTID><OBJECTID>33</OBJECTID></Element1>
I notice that when statement is used with XMLAGG and XMLCONCAT, it
still does produce duplicate values:

SELECT XML2CLOB (XMLELEMENT (NAME "Element1",
XMLAGG( XMLCONCAT(
XMLELEMENT (NAME "OBJECTID", ADDID()),
XMLELEMENT (NAME "OBJECTID", ADDID()),
XMLELEMENT (NAME "OBJECTID", ADDID())
)
)
)

)
FROM TABLE1;

Output:
<Element1><OBJECTID>85</OBJECTID><OBJECTID>84</OBJECTID><OBJECTID>83</OBJECTID><OBJECTID>85</OBJECTID><OBJECTID>84</OBJECTID><OBJECTID>83</OBJECTID></Element1>

[Serge Rielau]

Antanas wrote:

I was surprised myself today when AddID() started incrementing value
when used multiple times in SELECT statement. Previously it would not,
no matter what I did. The reason for this miracle might be because I
restored my DB from backup, and previous db image might have had some
inconsistency.

I am using it SQL/XML queries:
SELECT XML2CLOB (XMLELEMENT (NAME "Element1",
XMLELEMENT (NAME "OBJECTID", ADDID()),
XMLELEMENT (NAME "OBJECTID", ADDID()),
XMLELEMENT (NAME "OBJECTID", ADDID())
)
)
FROM TABLE1 WHERE COLUMN1 =1;

Output:
<Element1><OBJECTID>35</OBJECTID><OBJECTID>34</OBJECTID><OBJECTID>33</OBJECTID></Element1>
I notice that when statement is used with XMLAGG and XMLCONCAT, it
still does produce duplicate values:

SELECT XML2CLOB (XMLELEMENT (NAME "Element1",
XMLAGG( XMLCONCAT(
XMLELEMENT (NAME "OBJECTID", ADDID()),
XMLELEMENT (NAME "OBJECTID", ADDID()),
XMLELEMENT (NAME "OBJECTID", ADDID())
)
)
)

)
FROM TABLE1;

Output:
<Element1><OBJECTID>85</OBJECTID><OBJECTID>84</OBJECTID><OBJECTID>83</OBJECTID><OBJECTID>85</OBJECTID><OBJECTID>84</OBJECTID><OBJECTID>83</OBJECTID></Element1>

Mark the function as NOT DETERMINISTIC.
Prior to V8.2 we did not allow SQL Functions to lie.
We have since decided that determinism is in the eye of the beholder.
So DB2 allows you to define a function as DETERMINISTIC (default!) which
isn't really (NEXT VALUE ...).
Reason being that you might use that value to play e.g. with a
scratch-file without the randomness "leaking" out of the function.

Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

[Antanas]

Serge and Knut, thanks for such responsive DB2 support!

Antanas