Problem with sequence in UDB 8.2

[Hemant Shah]

Folks,

I am having problem running select query from a sequence. I get SQL0204N
error. I am running UDB 8.2 on AIX 5.2.

# oslevel
5.2.0.0

# db2level
DB21085I Instance "dba2test" uses "32" bits and DB2 code release "SQL08021"
with level identifier "03020106".
Informational tokens are "DB2 v8.1.1.80", "s041221", "U800400", and FixPak "8".
Product is installed at "/usr/opt/db2_08_01".

# db2 -svtf ~/tmp/w
CREATE SEQUENCE A2TEST.NEXTFRAGNUM AS INTEGER MINVALUE 1032 MAXVALUE 2147483647 START WITH 1032 INCREMENT BY 1 CACHE 20 NO CYCLE ORDER
DB20000I The SQL command completed successfully.
# db2 "SELECT NEXT_NUMBER FROM A2TEST.NEXTFRAGNUM FOR UPDATE OF NEXT_NUMBER"
SQL0204N "A2TEST.NEXTFRAGNUM" is an undefined name. SQLSTATE=42704
What am I doing wrong?

The above SQL statements works fine on a UDB 7.2 database.


--
Hemant Shah /"\ ASCII ribbon campaign
E-mail: No************@xnet.com \ / ---------------------
X against HTML mail
TO REPLY, REMOVE NoJunkMail / \ and postings
FROM MY E-MAIL ADDRESS.
-----------------[DO NOT SEND UNSOLICITED BULK E-MAIL]------------------
I haven't lost my mind, Above opinions are mine only.
it's backed up on tape somewhere. Others can have their own.

[Serge Rielau]

Hemant Shah wrote:

Folks,

I am having problem running select query from a sequence. I get SQL0204N
error. I am running UDB 8.2 on AIX 5.2.

# oslevel
5.2.0.0

# db2level
DB21085I Instance "dba2test" uses "32" bits and DB2 code release "SQL08021"
with level identifier "03020106".
Informational tokens are "DB2 v8.1.1.80", "s041221", "U800400", and FixPak "8".
Product is installed at "/usr/opt/db2_08_01".

# db2 -svtf ~/tmp/w
CREATE SEQUENCE A2TEST.NEXTFRAGNUM AS INTEGER MINVALUE 1032 MAXVALUE 2147483647 START WITH 1032 INCREMENT BY 1 CACHE 20 NO CYCLE ORDER
DB20000I The SQL command completed successfully.
# db2 "SELECT NEXT_NUMBER FROM A2TEST.NEXTFRAGNUM FOR UPDATE OF NEXT_NUMBER"
SQL0204N "A2TEST.NEXTFRAGNUM" is an undefined name. SQLSTATE=42704
What am I doing wrong? A sequence is not a table.
The above SQL statements works fine on a UDB 7.2 database.

Presumably you have a table named "A2TEST"."NEXTFRAGNUM" in the V7.2 DB?

To access a sequence value use:
NEXT VALUE FOR A2TEST.NEXTFRAGNUM
to access the last generated value:
PREVIOUS VALUE FOR A2TEST.NEXTFRAGNUM

(In V7 you may need NEXTVAL and PREVVAL instead.. We didn't have the SQL
Standard syntax nailed yet)

Cheers
Serge

--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab