Results 1 to 6 of 6
  1. #1
    Join Date
    Jan 2008
    Posts
    45

    Unanswered: db2look to get a stored procedure DDL

    Hi friends,

    I am coding a shell script that takes a procedure name as input and gives the DDL of procedure as output by parsing db2look output.I am checking for create procedure statement and then checking for END statement followed by semicolon.

    Do you think the above logic would work for all kinds of SQL stored procedure??

    Thanks

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    No, because if the SP has an internal BEGIN-END block you would terminate too early. Have you tried:

    select text from syscat.routines where ...

    This will get you the entire CREATE PROCEDURE for SQL procedures. I have seen the CLP truncate the return though.

    Andy

  3. #3
    Join Date
    Jan 2008
    Posts
    45
    Thanks for the reply.You are right there might be inner END statements..How about counting the number of BEGIN and number of END statements??

    What you said is 100% correct.Extracting from syscat.routines.But I am always getting lot of garbage when i do that.

  4. #4
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    If you are going to count BEGIN and END statements, then you are also going to have to count IF, LOOP, WHILE, and every other statement that ends with END.

    Andy

  5. #5
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by ARWinner
    you are also going to have to count IF, LOOP, WHILE, and every other statement that ends with END.
    ...but ignore comments that might contain "end", or string literals that might contain "end", or...
    ---
    "It does not work" is not a valid problem statement.

  6. #6
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    You can run db2look with specifying your own statement terminator (other than the default ";"). It will still use ";" at the end of each line, but will use the terminator you specify at the end of the SP. I prefer using the "@" symbol. This should solve your problem.
    Last edited by Marcus_A; 02-15-08 at 17:19.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

Posting Permissions

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