Results 1 to 9 of 9

Thread: DB2 sequences

  1. #1
    Join Date
    Feb 2009
    Posts
    27

    Unanswered: DB2 sequences

    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

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    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.
    ---
    "It does not work" is not a valid problem statement.

  3. #3
    Join Date
    Feb 2009
    Posts
    27
    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

  4. #4
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Check the manual for correct usage.
    ---
    "It does not work" is not a valid problem statement.

  5. #5
    Join Date
    Feb 2009
    Posts
    27
    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

  6. #6
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by mahesh_terdal
    cannot use a PREVVAL in a trigger.
    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.
    ---
    "It does not work" is not a valid problem statement.

  7. #7
    Join Date
    Feb 2009
    Posts
    27
    Hello Nick,

    Ok, thansk for your help.

    Thanks and Regards,
    Mahesh

  8. #8
    Join Date
    Feb 2009
    Posts
    9
    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 !!

  9. #9
    Join Date
    Feb 2009
    Posts
    27
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •