Results 1 to 12 of 12
  1. #1
    Join Date
    Mar 2009
    Posts
    73

    Unanswered: Update or Insert DB2 stored procedure.

    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.

  2. #2
    Join Date
    Mar 2009
    Posts
    73
    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

  3. #3
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    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)
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  4. #4
    Join Date
    Mar 2009
    Posts
    73
    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".

    Quote Originally Posted by Marcus_A
    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)

  5. #5
    Join Date
    Mar 2009
    Posts
    73
    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

  6. #6
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    How about this on Command Editor?
    !db2 .....

  7. #7
    Join Date
    Mar 2009
    Posts
    73
    its not working.

    Quote Originally Posted by tonkuma
    How about this on Command Editor?
    !db2 .....

  8. #8
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    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.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  9. #9
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    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.

  10. #10
    Join Date
    Mar 2009
    Posts
    73
    thanks guys that helped

  11. #11
    Join Date
    Jul 2012
    Posts
    2
    Hi,

    Can any one provide an example stored procedure with merge statement for DB2 database.

    Thanks,
    Veerababu K

  12. #12
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    on this page - many samples
    MERGE
    with or without sp does not matter : sql=sql
    Best Regards, Guy Przytula
    Database Software Consultant
    Good DBAs are not formed in a week or a month. They are created little by little, day by day. Protracted and patient effort is needed to develop good DBAs.
    Spoon feeding : To treat (another) in a way that discourages independent thought or action, as by overindulgence.
    DB2 UDB LUW Certified V7-V8-V9-V9.7-V10.1-V10.5 DB Admin - Advanced DBA -Dprop..
    Information Server Datastage Certified
    http://www.infocura.be

Posting Permissions

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