469,266 Members | 1,768 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

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

How to get stored procedure definition?

On a remote system I got a stored procedure named MY_STORED_PROCEDURE.
However there is a problem because I am not sure of the interface of
it (number of
parameters and types of them). I have googled, read documentation but
I haven't
found an appropriate syntax for quering database for description of
already created
stored procedure.

Does such statement exist? If it exists does someone know how it
should look
like?

--
Best regards
Jagger
Jun 27 '08 #1
3 29682
The system catalog is your friend :-)

select text from syscat.ROUTINES where
routinename='MY_STORED_PROCEDURE'

or for parameter specific info:

select * from syscat.ROUTINEPARMS where
routinename='MY_STORED_PROCEDURE'

Jagger wrote:
On a remote system I got a stored procedure named MY_STORED_PROCEDURE.
However there is a problem because I am not sure of the interface of
it (number of
parameters and types of them). I have googled, read documentation but
I haven't
found an appropriate syntax for quering database for description of
already created
stored procedure.

Does such statement exist? If it exists does someone know how it
should look
like?

--
Best regards
Jagger
Jun 27 '08 #2
"Jagger" <Pa*********@gmail.comwrote in message
news:8d**********************************@a1g2000h sb.googlegroups.com...
On a remote system I got a stored procedure named MY_STORED_PROCEDURE.
However there is a problem because I am not sure of the interface of
it (number of
parameters and types of them). I have googled, read documentation but
I haven't
found an appropriate syntax for quering database for description of
already created
stored procedure.

Does such statement exist? If it exists does someone know how it
should look
like?
--
Best regards
Jagger
In addition to the other suggestions, can do a db2look on the database. Of
course you will get all the packages if you use the option to get DDL for
SP's.
Jun 27 '08 #3
On 5 Cze, 16:39, Otto Carl Marte <Otto.Ma...@gmail.comwrote:
The system catalog is your friend :-)

select text from syscat.ROUTINES where
routinename='MY_STORED_PROCEDURE'

or for parameter specific info:

*select * from syscat.ROUTINEPARMS where
routinename='MY_STORED_PROCEDURE'
Hi, Otto,

this solution looks really reasonable, however
I have problems with executing the query. Each
time I get

DB2 SQL error: SQLCODE: -204, SQLSTATE: 42704, SQLERRMC:
SYSCAT.TABLES.

Even for such a simple query as
SELECT count(*) FROM syscat.tables
I get the same error code.

The database that I am connecting to is said to be:

Database product name: DB2
Database product version: DSN08015 (DB2 v8 for z/OS)

The above information is taken from the trace
of the connection. I use JDBC connectivity

Driver name: IBM DB2 JDBC Universal Driver Architecture

--
Best regards
Jagger
Jun 27 '08 #4

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

7 posts views Thread by Jeff Wang | last post: by
7 posts views Thread by rzagars | last post: by
reply views Thread by Frank Swarbrick | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by suresh191 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.