RE: Extract DDL for stored procedures...

RE: Extract DDL for stored procedures...

 

  

Very good question, and I'm not sure that experiment would yield a
definitive reply. One for the labs - db2look may just read the catalog in
whatever order it is in, or more sensibly it may try to list procedures in
parent child order; it doesn't do that for tables, but just lists the
foreign key constraints at the end of its output.

-----Original Message-----
From: vedant k7
[mailto:db2udbdba-ezmlmshield-x48119377.[Email address protected]
Sent: 24 April 2007 02:27
To: LazyDBA Discussion
Subject: RE: Extract DDL for stored procedures...

Yeah, The CLOB data makes it difficult to generate Deployment scripts.
Let me try DB2LOOK till someone suggests an alternate. Do you know if it
CREATES the dependee Stored Procedure BEFORE the dependent procedure?

Thanks,
Surendar.


>From: "Alex Levy "
><db2udbdba-ezmlmshield-x78993655.[Email address protected]
>To: "LazyDBA Discussion" <[Email address protected]
>Subject: RE: Extract DDL for stored procedures...
>Date: Tue, 24 Apr 2007 00:18:15 +0100
>
>Problem in AIX and almost certainly Windoze too: you can only
>manipulate the first 8 Kb or so, selecting direct from the CLOB in the
>catalog and directing into a file. So you're forced to db2look. As an
>aside, there's a bugette with db2look in this respect. It only started
>capturing SPs correctly from V8 FP8 or thereabouts. However at FP13 it
>reports the create procedure statement BEFORE the create statements for
>objects referenced in the procedure. Unless someone knows smarter? What
>I do is to run an awk script around a db2look output for the schema
>containing the procedure.
>Regards
>Alex Levy
>http://www.sustainablesoftware.net
>
>-----Original Message-----
>From: RAJESH CHANDRAMOHAN
>[mailto:db2udbdba-ezmlmshield-x26700796.[Email address protected]
>Sent: 23 April 2007 23:40
>To: LazyDBA Discussion
>Subject: RE: Extract DDL for stored procedures...
>
>
>Hey,
>
>Use the below query from db2cmd.
>db2 "select text from syscat.routines where routinetype='P' and
>routineshcema='ALS'" > CODE.TXT
>
>Use different schema or remove from query..
>
>Regards
>Rajesh
>
>-----Original Message-----
>From: vedant k7
>[mailto:db2udbdba-ezmlmshield-x42003445.[Email address protected]
>Sent: Monday, April 23, 2007 6:32 PM
>To: LazyDBA Discussion
>Subject: Extract DDL for stored procedures...
>
>What is the best way to extract DDL for all stored procs in a schema?
>
>1. I used db2look command. but it does not let me specify only stored
>procedures.
>2. Other way is Control Center. But, when I tried to Right-click on a
>procedure from Control center, it does not show Extract DDL option in
>the popup menu.
>
>In the help document I found we need "SYSPROC.ADB2RE" stored proc to
>use Extract DDL. Does anyone know how I can get these admin procedures
>in my database.
>
>
>Please suggest any straight forward way to accomplish this.
>
>
>Thanks,
>
>Surendar.
>
>_________________________________________________________________
>Catch the complete World Cup coverage with MSN
>http://content.msn.co.in/Sports/Cricket/Default.aspx
>
>
>
>---------------------------------------------------------------------
>TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY To post a
>dba
>job: http://jobs.lazydba.com To Subscribe : http://www.LazyDBA.com To
>unsubscribe: http://www.lazydba.com/unsubscribe.html
>
>
>- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
>- -
>- - - - - -
>
>This message is intended only for the personal and confidential use of
>the designated recipient(s) named above. If you are not the intended
>recipient of this message you are hereby notified that any review,
>dissemination, distribution or copying of this message is strictly
>prohibited. This communication is for information purposes only and
>should not be regarded as an offer to sell or as a solicitation of an
>offer to buy any financial product, an official confirmation of any
>transaction, or as an official statement of Lehman Brothers. Email
>transmission cannot be guaranteed to be secure or error-free.
>Therefore, we do not represent that this information is complete or
>accurate and it should not be relied upon as such. All information is
>subject to change without notice.
>
>--------
>IRS Circular 230 Disclosure:
>Please be advised that any discussion of U.S. tax matters contained
>within this communication (including any attachments) is not intended
>or written to be used and cannot be used for the purpose of (i)
>avoiding U.S. tax related penalties or (ii) promoting, marketing or
>recommending to another party any transaction or matter addressed
>herein.
>
>
>
>
>---------------------------------------------------------------------
>TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY To post a
>dba
>job: http://jobs.lazydba.com To Subscribe : http://www.LazyDBA.com To
>unsubscribe: http://www.lazydba.com/unsubscribe.html
>
>
>
>
>
>---------------------------------------------------------------------
>TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY To post a
>dba job: http://jobs.lazydba.com To Subscribe : http://www.LazyDBA.com
>To unsubscribe: http://www.lazydba.com/unsubscribe.html
>

_________________________________________________________________
Spice up your IM conversations. New, colorful and animated emoticons. Get
chatting! http://server1.msn.co.in/SP05/emoticons/



---------------------------------------------------------------------
TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
To post a dba job: http://jobs.lazydba.com
To Subscribe : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html



DB2 & UDB email list listserv db2-l LazyDBA home page