Results 1 to 8 of 8
  1. #1
    Join Date
    Jan 2009
    Posts
    35

    Checking to see if a Stored Procedure exist in DB2 SQL

    Hi, I am new to DB2 and I come from an SQL Server background.

    In Transact-SQL, when making a Stored Procedure, I always had an "IF EXISTS" clause at the top to check to see if the Stored Procdure already existed and if so Drop it.

    How can I do the same thing with a Stored Procedure in DB2?

    I have a database project in Visual Studio.Net and it is connected to the AS400. When I make a Stored Procedure, everything is fine. However, whenever I modify the Stored Procedure and Re-apply it to the AS400, it correctly errors and states that the Stored Procedure exists already.

    Thanks in advance

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    You can't with regular SQL. You could query the syscat.procuedures veiw or do a conditional create if you use a scripting language or a SP, but you cannot do it with simple SQL.

    Just do the drop and ignor the error if it doesn't exist.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  3. #3
    Join Date
    Jan 2009
    Posts
    35
    Quote Originally Posted by Marcus_A
    You can't with regular SQL. You could query the syscat.procuedures veiw or do a conditional create if you use a scripting language or a SP, but you cannot do it with simple SQL.

    Just do the drop and ignor the error if it doesn't exist.
    Marcus, thanks for answering my question.

    So would I do something like this:

    Code:
    DROP PROCEDURE TEST
    
    CREATE PROCEDURE TEST()
    I don't have access to the simple stored procedure I wrote today at work, so I apologize for the simplicity.

  4. #4
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Yes, but don't forget your statement terminator. For a script that contains SP's you must use something other than the default ";" since many statements within the SP have a ";".
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  5. #5
    Join Date
    May 2003
    Posts
    113
    maybe you also need a 'commit' after the 'drop proc'

  6. #6
    Join Date
    Jan 2009
    Posts
    35
    I will look into that when I get to work.

    When I tried to do:

    Code:
    DROP PROCEDURE sp_Test();
    
    CREATE PROCEDURE sp_Test()
    I got an error. I believe it said the Create Statement wasn't expected.

    Regardless, if the Stored Procedure doesn't exist the first time, it will error out anyway.

    The Procedure is not going to change much, so it won't be a big deal.

  7. #7
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    You cannot use ";" as a terminator in a script that creates SP's or triggers. That is becasue the semi-colon is used as a terminator of lines within the SP. Use something else like "@" to terminate the create SP statement (and all other statemnets in the script) so that ";" can be used a lines within the SP.

    When you run the script, you will have to change the default terminator:

    db2 -td@ -vf <file-name>
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  8. #8
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Or use:
    Code:
    --#SET TERMINATOR @
    in the script itself
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

Posting Permissions

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