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

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

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.

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

...but ignore comments that might contain "end", or string literals that might contain "end", or...

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.