Hi I'm really new to db2 and in a scenario in our application where there is an update else insert situation on a grid.

Can someone help me write a stored procedure inside DB2 for this and help regarding db2 stored procedure syntax?

We are using db2 express C and .NET.

Regards, Mike.

I tried to write a sproc but Im having some errors.

An unexpected token "END-OF-STATEMENT" was found following "URITYSRNO = CompSrNo". Expected tokens may
include: "<psm_semicolon>". LINE NUMBER=17. SQLSTATE=42601

--

CREATE PROCEDURE ADMINISTRATOR.FtUpdSecSh(
IN CompSrNo INTEGER,
IN TrtSrNo INTEGER,
IN CltCode INTEGER,
IN CltShare DECIMAL(10, 6),
IN CltComm DECIMAL(10, 6)
)
LANGUAGE SQL

BEGIN

UPDATE ADMINISTRATOR.UWFTRTYSCUTY
SET GENCLIENTCODE = CltCode,
GENCLIENTSHARE = CltShare,
GENCOMMPERCENT = CltComm
WHERE a.GENFORTREATYSRNO = TrtSrNo
AND GENSECURITYSRNO = CompSrNo ;



IF (ROW_COUNT = 0)
INSERT
INTO ADMINISTRATOR.UWFTRTYSCUTY(GENFORTREATYSRNO,
GENCOMMPERCENT,
GENCLIENTSHARE,
GENCLIENTCODE)
VALUES (TrtSrNo,
CltComm,
CltShare,
CltCode) ;



END IF;

END

SP's and UDF's are tricky to create using the command processor because they use ";" terminators in the body of the SP, which is interpreted by the command processor as the end of the SQL statement (but the end of the SP is not until the very end). So do the following:

CREATE PROCEDURE ADMINISTRATOR.FtUpdSecSh(
IN CompSrNo INTEGER,
IN TrtSrNo INTEGER,
IN CltCode INTEGER,
IN CltShare DECIMAL(10, 6),
IN CltComm DECIMAL(10, 6)
)
LANGUAGE SQL

BEGIN

UPDATE ADMINISTRATOR.UWFTRTYSCUTY
SET GENCLIENTCODE = CltCode,
GENCLIENTSHARE = CltShare,
GENCOMMPERCENT = CltComm
WHERE a.GENFORTREATYSRNO = TrtSrNo
AND GENSECURITYSRNO = CompSrNo ;

IF (ROW_COUNT = 0)
INSERT
INTO ADMINISTRATOR.UWFTRTYSCUTY(GENFORTREATYSRNO,
GENCOMMPERCENT,
GENCLIENTSHARE,
GENCLIENTCODE)
VALUES (TrtSrNo,
CltComm,
CltShare,
CltCode) ;

END IF;

END@

db2 -td@ -vf <file-name-with-SP-text-above>

But I think you may need to use this syntax to check the rows updated:

GET DIAGNOSTICS rcount = ROW_COUNT;

IF (rcount= 0)

Thanks for the reply Mr. Marcus. I saved the stored procedure inside a text file (.sql), but when I run the command on db2 express c command editor
its giving an error .

db2 -td@ -vf <C:\SP_CompShare.sql>


An unexpected token "db2" was found following "BEGIN-OF-STATEMENT". Expected tokens may include: "SELECT".

Ok I managed to find where to run the db2 command which I messed up previously , Now I'm running into another error which says on db2 CLP

DB21034E the command was processed as a sql statement because it was not a valid CLP command.During SQL processing it returned : SQL0104N
An unexpected token "-" was found following "BEGIN-OF STATEMENT".Expected tokens may include "SELECT" SQLState=42601

How about this on Command Editor?
!db2 .....

its not working.

Use the DB2 Command Window, not the DB2 Command Processor. Do not include the "<" or ">" in the file name. I am not sure if you can use the drive letter. It would be safer to cd to the path were the file aleady exists and do this:

cd c:\ (or whatever you need to do in Windows to get to were your file is located.)

db2 -td@ -vf SP_CompShare.sql

All of this is explained in the Command Reference manual. I think you should look at it.

You can write your procedure body by one MERGE statement. So, BEGIN and END BIGIN are not necessary.
Then, you don't worry about statement terminator.
One terminator(";") is enough for end of procedure body(a MERGE statement) and end of procedure.

thanks guys that helped