DB2 Sequence Objects

, , ,
1

We are trying to use a sequence object to generate values for one of our DB2 v9 tables.

The sequence number needs to be generated before the SQL INSERT statement and then passed to an Object Maint Dialog module.

Interactive SQL is switched off in the production and AUT environments.

Via the development environment the sample CALLISQL program in SYSDB2 does reference and increment the sequence successfully so there is no problem with the database or the database object itself.

The only way we can get any program to stow is to define SYSDUMMY1 in Predict with a reference to the sequence object.

However, when this code (or quite a few variations thereof:
SELECT SEQUENCE_OBJECT.NEXTVAL() INTO #SEQ FROM SYSIBM.SYSDUMMY1
END-SELECT

is executed it gives the following error:
NAT3700 Error -440 with SQLSTATE 42884 from call to DB2.

The error text returned by SQLERR reads as follows:
DSNT408I SQLCODE = -440, ERROR: NO FUNCTION BY THE NAME SEQUENCE_OBJECT.NEXTVAL HAVING COMPATIBLE ARGUMENTS WAS FOUND IN THE CURRENT PATH

So the big question is how to use sequence objects in Natural?
Any help or indications would be greatly appreciated.

2

I haven’t used sequences much, but I don’t think that you are accessing them correctly. You should be able to access them directly when doing the insert:

INSERT INTO ORDERS (ORDERNO, CUSTNO) 
  VALUES (NEXT VALUE FOR SEQUENCE_OBJECT, 123456)

The sequence object doesn’t need to be defined to Predict (Heck, at our shop we stopped using Predict for our DB2 objects because it is just too flaky!)

You might need to put the “NEXT VALUE FOR SEQUENCE_OBJECT” part in carets to bypass the Natural compiler:

INSERT INTO ORDERS (ORDERNO, CUSTNO) VALUES (<<NEXT>>, 123456)

Edit: for some reason this forum doesn’t like the carets and chopped off the rest of the statement after the word NEXT, but the entire “NEXT VALUE FOR SEQUENCE_OBJECT” should be between the carets.

You could also try using Identity columns instead, which have worked successfully in our shop.