Hi All,

I have a problem with DB2 sequences, The db2 SEQUENCE increments by 3 instead of one. Is there any way to restrict the increment to 1.

The full query is as below:
CREATE SEQUENCE CLAPISCH.CLSyncSEQ AS SMALLINT START WITH 1 INCREMENT BY 1 NO CACHE ORDER;


--#SET DELIMITER ~
CREATE TRIGGER CLAPISCH.CLINS_TAB14_TR AFTER INSERT ON ADMINISTRATOR.TESTTAB REFERENCING NEW AS NEW_VAL FOR EACH ROW MODE DB2SQL BEGIN ATOMIC
INSERT INTO CLAPISCH.CLSync VALUES (CLAPISCH.CLSyncSEQ.NEXTVAL, 14) ;
INSERT INTO CLAPISCH.CLMT14 VALUES (NEW_VAL.FIRSTNAME, CLAPISCH.CLSyncSEQ.NEXTVAL - 1, 'I') ;
END~


CREATE TRIGGER CLAPISCH.CLDEL_TAB14_TR AFTER DELETE ON ADMINISTRATOR.TESTTAB REFERENCING OLD AS OLD_VAL FOR EACH ROW MODE DB2SQL BEGIN ATOMIC
INSERT INTO CLAPISCH.CLSync VALUES (CLAPISCH.CLSyncSEQ.NEXTVAL, 14) ;
INSERT INTO CLAPISCH.CLMT14 VALUES (OLD_VAL.FIRSTNAME, CLAPISCH.CLSyncSEQ.NEXTVAL - 1, 'D');
END~

CREATE TRIGGER CLAPISCH.CLUPD_TAB14_TR AFTER UPDATE OF FIRSTNAME, LASTNAME ON ADMINISTRATOR.TESTTAB REFERENCING NEW AS NEW_VAL OLD AS OLD_VAL FOR
EACH ROW MODE DB2SQL BEGIN ATOMIC
INSERT INTO CLAPISCH.CLSync VALUES (CLAPISCH.CLSyncSEQ.NEXTVAL, 14);
INSERT INTO CLAPISCH.CLMT14 VALUES (OLD_VAL.FIRSTNAME, CLAPISCH.CLSyncSEQ.NEXTVAL - 1, 'O') ;
INSERT INTO CLAPISCH.CLMT14 VALUES (NEW_VAL.FIRSTNAME, CLAPISCH.CLSyncSEQ.NEXTVAL - 2, 'N') ;
END~

COMMIT~


Thanks in Advance,
Mahesh

You did not show us how you determine that the sequence increments by 3. I doubt that this is indeed what happens.

You call NEXTVAL three times in the CLUPD_TAB14_TR trigger. Don't you think it would increment the sequence by 1 three times? Consider using PREVVAL.

Hello Nick,

Thanks for your quick response.

I tried using PREVVAL. but fails with error:

* SQL Statement Number 2:

CREATE TRIGGER CLAPISCH.CLINS_TAB14_TR AFTER INSERT ON ADMINISTRATOR.TESTTAB REF
ERENCING NEW AS NEW_VAL FOR EACH ROW MODE DB2SQL BEGIN ATOMIC
INSERT INTO CLAPISCH.CLSync VALUES (CLAPISCH.CLSyncSEQ.NEXTVAL, 14) ;
INSERT INTO CLAPISCH.CLMT14 VALUES (NEW_VAL.FIRSTNAME, CLAPISCH.CLSyncSEQ.PREVVA
L, 'I') ;
END~

** CLI error in executing the SQL statement:
(-348): [IBM][CLI Driver][DB2/NT] SQL0348N "CLAPISCH.CLSYNCSEQ.PREVVAL" cannot
be specified in this context. LINE NUMBER=3. SQLSTATE=428F9


can we use PREVVAL in triggers? if so, please let me know how.

Regards ,

Mahesh

Check the manual for correct usage.

Hello Nick,

I have been browsing some online content for this issue, some them pointed me to the fact that we cannot use a PREVVAL in a trigger. I just want know whether that is correct, If not can you please tell me where to look for a correct way of using PREVVAL in triggers.

Looking forward for you response.

Thanks and Regards,
Mahesh

Looks like you are correct. I guess you will need to put all logic into a stored procedure, which you will then call from the trigger.

Hello Nick,

Ok, thansk for your help.

Thanks and Regards,
Mahesh

Hi Mahesh,

this is really a problem which one of team mate faced. he did in the below way...

below is not a best practice but may help you in incrementing the counter exactly.

INSERT INTO CLAPISCH.CLSync VALUES (CLAPISCH.CLSyncSEQ.NEXTVAL, 14) ;

INSERT INTO CLAPISCH.CLMT14 VALUES (NEW_VAL.FIRSTNAME, 0, 'I') ;

UPDATE CLAPISCH.CLMT14 SET SEQUENCE_VAL = SELECT MAX(SEQVAL) FROM CLAPISCH.CLSync WHERE REF_VALUE = 14

if the above is possible you will increment the sequence only once per trigger and store the incremented value in CLAPISCH.CLSync .

insert dummy value (0) into CLAPISCH.CLMT14 intially and update it immediately with the maximum sequence value which was inserted earlier into the CLAPISCH.CLSync with some where conditions.

We will lose integrity for a short while but as ATOMIC can achieve as a whole.

please let me know your views / the solution you got if any?

Thanks !!

Hello Nick,

Again, thanks for you reply.

The work-around you suggested does not suit our requirement.

So what we are using is as below :

--#SET DELIMITER ~

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

* SQL Statement Number 1:

CREATE TRIGGER CLAPISCH.CLINS_TAB14_TR AFTER INSERT ON ADMINISTRATOR.TESTTAB REF
ERENCING NEW AS NEW_VAL FOR EACH ROW MODE DB2SQL
INS: BEGIN ATOMIC
DECLARE SPT INT;
SET SPT = CLAPISCH.CLSyncSEQ.NEXTVAL;
INSERT INTO CLAPISCH.CLSync VALUES (SPT, 14) ;
INSERT INTO CLAPISCH.CLMT14 VALUES (NEW_VAL.FIRSTNAME, SPT, 'I') ;
END INS~

That works fine for us.


Regards,
Mahesh.