Results 1 to 12 of 12
  1. #1
    Join Date
    Sep 2009
    Posts
    14

    Unanswered: Generate Runnning number

    Hi,

    I'm going to create a running number in db2. The running number will be reset each year so I tried to use merge statement like below


    SELECT MAX_NUMBER
    FROM FINAL TABLE (
    MERGE INTO T_RUNNING_NO AS A INCLUDE (MAX_NUMBER)
    USING VALUES ('TXN_NO', '2009') AS B (TYPE, FORMAT)
    ON A.ID = B.ID
    WHEN NOT MATCHED THEN
    INSERT (TYPE, FORMAT, MAX_NUMBER, CREATOR, CREATE_TIME)
    VALUES ('TXN_NO', '2009 ', 1, 'system_test', CURRENT TIMESTAMP)
    WHEN MATCHED THEN
    UPDATE SET
    A.MAX_NUMBER = A.MAX_NUMBER + 1
    )

    The idea is to update when match the TYPE and FORMAT if no then insert.

    But I keep get error from the query above. Any suggestion? I'm using DB2 9.5
    with below schema

    CREATE TABLE LT_RUNNING_NO (
    ID BIGINT NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 1 INCREMENT BY 1 MINVALUE 1 MAXVALUE 32672 NO CYCLE CACHE 20),
    TYPE VARCHAR(64) NOT NULL,
    FORMAT VARCHAR(10),
    MAX_NUMBER INTEGER,
    CREATOR VARCHAR(30) NOT NULL,
    CREATE_TIME TIMESTAMP NOT NULL
    )
    DATA CAPTURE NONE ;

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    1) DB2 doesn't support FROM FINAL TABLE (MERGE ...).
    Execute MERGE statement directly.

    2) Do you need "SELECT MAX_NUMBER ..."?
    If you want to know MAX_NUMBER, SELECT it after MERGE statement.

    3) INCLUDE clause is not supported for MERGE statement.

    4) There is no "B.ID" referenced in "ON A.ID = B.ID".
    You might want to use "ON A.TYPE = B.TYPE AND A.FORMAT = B.FORMAT".

  3. #3
    Join Date
    Sep 2009
    Posts
    14
    Thank you for the quick replied.

    I've read from this thread about select from merge
    DB2PORTAL Blog: SELECT from DELETE, UPDATE, and MERGE [DB2 9 for z/OS]

    so I thought that it might be possible to select from merge.

    So I've created the store procedure to handle this

    CREATE PROCEDURE sp_get_transaction_no
    (IN transactionType VARCHAR(10), IN year VARCHAR(4), OUT number INTEGER)
    LANGUAGE SQL
    READS SQL DATA

    BEGIN

    BEGIN ATOMIC
    MERGE INTO ESV_LT_RUNNING_NO AS A
    USING (VALUES (transactionType, year)) AS B (TYPE, FORMAT)
    ON (a.type = b.type and a.format = b.format)
    WHEN NOT MATCHED THEN
    INSERT (TYPE, FORMAT, MAX_NUMBER, CREATOR, CREATE_TIME)
    VALUES (transactionType, year, 1, 'ESV_SYSTEM', CURRENT TIMESTAMP);
    WHEN MATCHED THEN
    UPDATE SET A.MAX_NUMBER = A.MAX_NUMBER + 1;
    SELECT MAX_NUMBER INTO number FROM ESV_LT_RUNNING_NO WHERE type = transactionType and format = year;
    END;

    END


    but again it keeps error. What did i miss?

  4. #4
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    First of all, you should supply what error message(s) you got.
    Without that, it would be difficut to get usefull advise from other people.

    But, this time, you may have done simple syntax error and careless mistake, I thought.
    1) "MODIFIES SQL DATA" instead of "READS SQL DATA".
    2) semicolon(";") at the end of INSERT would be not necessary.

    I've read from this thread about select from merge
    DB2PORTAL Blog: SELECT from DELETE, UPDATE, and MERGE [DB2 9 for z/OS]

    so I thought that it might be possible to select from merge.
    You are right.
    DB2(newest is 9.7 now) for LUW does not support SELECT from MERGE, while DB2 Version 9.1 for z/OS does.

  5. #5
    Join Date
    Sep 2009
    Posts
    14
    Hi,
    Thank you again for the replied.


    I've change the storeprocedure to

    CREATE PROCEDURE sp_get_transaction_no
    (IN transactionType VARCHAR(10), IN year VARCHAR(4), OUT number INTEGER)
    LANGUAGE SQL
    MODIFIES SQL DATA

    BEGIN

    BEGIN ATOMIC
    MERGE INTO ESV_LT_RUNNING_NO AS A
    USING (VALUES (transactionType, year)) AS B (TYPE, FORMAT)
    ON (a.type = b.type and a.format = b.format)
    WHEN NOT MATCHED THEN
    INSERT (TYPE, FORMAT, MAX_NUMBER, CREATOR, CREATE_TIME)
    VALUES (transactionType, year, 1, 'ESV_SYSTEM', CURRENT TIMESTAMP)
    WHEN MATCHED THEN
    UPDATE SET A.MAX_NUMBER = A.MAX_NUMBER + 1;
    SELECT MAX_NUMBER INTO number FROM ESV_LT_RUNNING_NO WHERE type = transactionType and format = year;
    END;

    END

    But still got error below

    SQL State = 42601 SQL Code = -104 SQL Message = An unexpected token "END-OF-STATEMENT" was found following "END". Expected tokens may include: "JOIN <joined_table>".. SQLCODE=-104, SQLSTATE=42601, DRIVER=3.51.90 Exception message = com.ibm.db2.jcc.a.bn: An unexpected token "END-OF-STATEMENT" was found following "END". Expected tokens may include: "JOIN <joined_table>".. SQLCODE=-104, SQLSTATE=42601, DRIVER=3.51.90

  6. #6
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    BEGIN
    BEGIN ATOMIC
    ....
    END;
    END
    1) two begin blocks are redundant.
    2) specify statement termination character other than semicolon.
    Please search "statement termination character" in this forum.

  7. #7
    Join Date
    Sep 2009
    Posts
    14
    I've change it to below but still got error


    CREATE PROCEDURE sp_get_transaction_no
    (IN transactionType VARCHAR(10), IN year VARCHAR(4), OUT number INTEGER)
    LANGUAGE SQL
    MODIFIES SQL DATA

    BEGIN ATOMIC
    MERGE INTO ESV_LT_RUNNING_NO AS A
    USING (VALUES (transactionType, year)) AS B (TYPE, FORMAT)
    ON (a.type = b.type and a.format = b.format)
    WHEN NOT MATCHED THEN
    INSERT (TYPE, FORMAT, MAX_NUMBER, CREATOR, CREATE_TIME)
    VALUES (transactionType, year, 1, 'ESV_SYSTEM', CURRENT TIMESTAMP)
    WHEN MATCHED THEN
    UPDATE SET A.MAX_NUMBER = A.MAX_NUMBER + 1;
    SELECT MAX_NUMBER INTO number FROM ESV_LT_RUNNING_NO WHERE type = transactionType and format = year;
    END



    SQL0104N An unexpected token "END-OF-STATEMENT" was found following "END". Expected tokens may include: "JOIN <joined_table> ".

    Explanation:

    A syntax error in the SQL statement or the input command string for the
    SYSPROC.ADMIN_CMD procedure was detected at the specified token
    following the text "<text>". The "<text>" field indicates the 20
    characters of the SQL statement or the input command string for the
    SYSPROC.ADMIN_CMD procedure that preceded the token that is not valid.

    As an aid, a partial list of valid tokens is provided in the SQLERRM
    field of the SQLCA as "<token-list>". This list assumes the statement is
    correct to that point.

    The statement cannot be process


    I've also try with the statement termination character but again failed

    Below is sample
    CREATE PROCEDURE sp_get_transaction_no
    (IN transactionType VARCHAR(10), IN year VARCHAR(4), OUT number INTEGER)
    LANGUAGE SQL
    MODIFIES SQL DATA

    BEGIN ATOMIC
    MERGE INTO ESV_LT_RUNNING_NO AS A
    USING (VALUES (transactionType, year)) AS B (TYPE, FORMAT)
    ON (a.type = b.type and a.format = b.format)
    WHEN NOT MATCHED THEN
    INSERT (TYPE, FORMAT, MAX_NUMBER, CREATOR, CREATE_TIME)
    VALUES (transactionType, year, 1, 'ESV_SYSTEM', CURRENT TIMESTAMP)
    WHEN MATCHED THEN
    UPDATE SET A.MAX_NUMBER = A.MAX_NUMBER + 1;
    SELECT MAX_NUMBER INTO number FROM ESV_LT_RUNNING_NO WHERE type = transactionType and format = year;
    END@


    I've setting @ in Tool Setting
    Last edited by phanita; 09-28-09 at 04:30.

  8. #8
    Join Date
    Dec 2005
    Posts
    273
    Not sure about it, but try:

    CREATE PROCEDURE sp_get_transaction_no
    (IN transactionType VARCHAR(10), IN year VARCHAR(4), OUT number INTEGER)
    LANGUAGE SQL
    MODIFIES SQL DATA

    BEGIN ATOMIC
    MERGE INTO ESV_LT_RUNNING_NO AS A
    USING (VALUES (transactionType, year)) AS B (TYPE, FORMAT)
    ON (a.type = b.type and a.format = b.format)
    WHEN NOT MATCHED THEN
    INSERT (TYPE, FORMAT, MAX_NUMBER, CREATOR, CREATE_TIME)
    VALUES (B.TYPE, B.FORMAT, 1, 'ESV_SYSTEM', CURRENT TIMESTAMP)
    WHEN MATCHED THEN
    UPDATE SET A.MAX_NUMBER = A.MAX_NUMBER + 1;
    SELECT MAX_NUMBER INTO number FROM ESV_LT_RUNNING_NO WHERE type = transactionType and format = year;
    END@

  9. #9
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    phanita, I tried your code on my DB2 9.7 for Windows from Command Editor.

    My guess is that you failed to specify @ for Statement termination character.

    Followings are my results.(I removed some redundant messages and displayed informations.)

    1) DDL in your first post.
    Code:
    ------------------------------ Commands Entered ------------------------------
    CREATE TABLE LT_RUNNING_NO (
    ID BIGINT NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 1 INCREMENT BY 1 MINVALUE 1 MAXVALUE 32672 NO CYCLE CACHE 20),
    TYPE VARCHAR(64) NOT NULL,
    FORMAT VARCHAR(10),
    MAX_NUMBER INTEGER,
    CREATOR VARCHAR(30) NOT NULL,
    CREATE_TIME TIMESTAMP NOT NULL
    )
    DATA CAPTURE NONE ;
    ------------------------------------------------------------------------------
    DB20000I  The SQL command completed successfully.
    2) Your second code in "Yesterday 16:01".
    Code:
    ------------------------------ Commands Entered ------------------------------
    CREATE PROCEDURE sp_get_transaction_no
    (IN transactionType VARCHAR(10), IN year VARCHAR(4), OUT number INTEGER)
    LANGUAGE SQL
    MODIFIES SQL DATA
    
    BEGIN ATOMIC
    MERGE INTO ESV_LT_RUNNING_NO AS A 
    USING (VALUES (transactionType, year)) AS B (TYPE, FORMAT)
    ON (a.type = b.type and a.format = b.format)
    WHEN NOT MATCHED THEN
    INSERT (TYPE, FORMAT, MAX_NUMBER, CREATOR, CREATE_TIME) 
    VALUES (transactionType, year, 1, 'ESV_SYSTEM', CURRENT TIMESTAMP)
    WHEN MATCHED THEN
    UPDATE SET A.MAX_NUMBER = A.MAX_NUMBER + 1;
    SELECT MAX_NUMBER INTO number FROM ESV_LT_RUNNING_NO WHERE type = transactionType and format = year;
    END@
    ------------------------------------------------------------------------------
    DB21034E  The command was processed as an SQL statement because it was not a 
    valid Command Line Processor command.  During SQL processing it returned:
    SQL0204N  "DB2ADMIN.ESV_LT_RUNNING_NO" is an undefined name.  LINE NUMBER=7.  
    SQLSTATE=42704
    3) Changed "ESV_LT_RUNNING_NO" to "LT_RUNNING_NO".
    Code:
    ------------------------------ Commands Entered ------------------------------
    CREATE PROCEDURE sp_get_transaction_no
    (IN transactionType VARCHAR(10), IN year VARCHAR(4), OUT number INTEGER)
    LANGUAGE SQL
    MODIFIES SQL DATA
    
    BEGIN ATOMIC
    MERGE INTO LT_RUNNING_NO AS A 
    USING (VALUES (transactionType, year)) AS B (TYPE, FORMAT)
    ON (a.type = b.type and a.format = b.format)
    WHEN NOT MATCHED THEN
    INSERT (TYPE, FORMAT, MAX_NUMBER, CREATOR, CREATE_TIME) 
    VALUES (transactionType, year, 1, 'ESV_SYSTEM', CURRENT TIMESTAMP)
    WHEN MATCHED THEN
    UPDATE SET A.MAX_NUMBER = A.MAX_NUMBER + 1;
    SELECT MAX_NUMBER INTO number FROM LT_RUNNING_NO WHERE type = transactionType and format = year;
    END@
    ------------------------------------------------------------------------------
    DB20000I  The SQL command completed successfully.

  10. #10
    Join Date
    Sep 2009
    Posts
    14
    Thank you very much that's work like a charm.

  11. #11
    Join Date
    Sep 2009
    Posts
    14
    Hi,

    I've wonder that will this procedure working on DB2 version 8.1?

    Thank you in advance

  12. #12
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    MERGE statement was supported from DB2 8.2 for LUW.

Posting Permissions

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