469,951 Members | 2,717 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,951 developers. It's quick & easy.

Stored procedure

Hi,

How to extract only the stored procedures which has been already
created in the database.

db2look has an option to extract only the tables but not for
procedures,triggers, udfs. However, db2look extracts evrerything
including SP's,Triggers,UDF's...

I tried hitting simple select against syscat.procedures and
syscat.routines, but the column "Text" defined for both the views is
"Clob 2MB" and it does not redirects the entire procedure body into a
file. Infact, it truncates the procedure body.

I m using DB2 v8.2 on AIX5.2

Please advise.

Regards
Jignesh.

Jan 11 '06 #1
3 4037
to***********@gmail.com wrote:
Hi,

How to extract only the stored procedures which has been already
created in the database.

db2look has an option to extract only the tables but not for
procedures,triggers, udfs. However, db2look extracts evrerything
including SP's,Triggers,UDF's...

I tried hitting simple select against syscat.procedures and
syscat.routines, but the column "Text" defined for both the views is
"Clob 2MB" and it does not redirects the entire procedure body into a
file. Infact, it truncates the procedure body.

I m using DB2 v8.2 on AIX5.2

What about simply using EXPORT?

--
Serge Rielau
DB2 Solutions Development
DB2 UDB for Linux, Unix, Windows
IBM Toronto Lab
Jan 12 '06 #2
to***********@gmail.com wrote:
Hi,

How to extract only the stored procedures which has been already
created in the database.

db2look has an option to extract only the tables but not for
procedures,triggers, udfs. However, db2look extracts evrerything
including SP's,Triggers,UDF's...

I tried hitting simple select against syscat.procedures and
syscat.routines, but the column "Text" defined for both the views is
"Clob 2MB" and it does not redirects the entire procedure body into a
file. Infact, it truncates the procedure body.


This is due to the CLP truncating the output (not the data!) so that your
terminal doesn't overflow.

--
Knut Stolze
DB2 Information Integration Development
IBM Germany
Jan 12 '06 #3
Yeah, db2 clp is truncating the o/p to prevent from overflowing. It has
limitation fo 2048 characters.

Export works fine with filetype modifier LOBSINFILE.

Thanx for all your help.

Regards
Jignesh.
Knut Stolze wrote:
to***********@gmail.com wrote:
Hi,

How to extract only the stored procedures which has been already
created in the database.

db2look has an option to extract only the tables but not for
procedures,triggers, udfs. However, db2look extracts evrerything
including SP's,Triggers,UDF's...

I tried hitting simple select against syscat.procedures and
syscat.routines, but the column "Text" defined for both the views is
"Clob 2MB" and it does not redirects the entire procedure body into a
file. Infact, it truncates the procedure body.


This is due to the CLP truncating the output (not the data!) so that your
terminal doesn't overflow.

--
Knut Stolze
DB2 Information Integration Development
IBM Germany


Jan 12 '06 #4

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

4 posts views Thread by Rhino | last post: by
8 posts views Thread by Thomasb | last post: by
2 posts views Thread by Dino L. | last post: by
3 posts views Thread by kd | last post: by
2 posts views Thread by jed | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.