Generate Runnning number

phanita · #1 (**permalink**) 09-24-09, 00:57

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 ;

tonkuma · #2 (**permalink**) 09-24-09, 03:39

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".

phanita · #3 (**permalink**) 09-24-09, 05:02

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?

tonkuma · #4 (**permalink**) 09-24-09, 07:09

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.

Quote:

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.

phanita · #5 (**permalink**) 09-24-09, 21:54

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

tonkuma · #6 (**permalink**) 09-24-09, 22:27

Quote:

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.

phanita · #7 (**permalink**) 09-28-09, 03:01

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

umayer · #8 (**permalink**) 09-28-09, 07:53

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@

tonkuma · #9 (**permalink**) 09-28-09, 11:22

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.