473,322 Members | 1,736 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,322 software developers and data experts.

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 31838
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
by: Siva | last post by:
Hi, i would like to know the prcedure for viewing Stored Procedure Definition in SQL prompt. can anybody help me? thanxs & bye
6
by: Dave | last post by:
I have a stored procedure in sql 2000 that requires steps to be fully completed before moving to the next command in the procedure. I have tried to place the word "GO" after each statement. When I...
7
by: Jeff Wang | last post by:
Hi all, Can someone help me out? I've been struggling with this for almost a week and still have no clue what's wrong. Basically I want to write a DB2 stored procedure for OS/390 in REXX. In...
5
by: Raquel | last post by:
This is a very simple DB2 SQLJ stored procedure. The problem is that it seems to run fine but returns NOTHING. I mean..as if nothing has happened..not resultset is returned. I am passing value...
8
by: Mark Flippin | last post by:
This is for a reporting problem using: Access 2000 SQL Server 2000 Both at SP3 I've a stored procedure in SQL Server 2000 which builds a result set from a disparate set of tables, utilizing...
7
by: rzagars | last post by:
I have just started working on DB2 which the syntax is a lot different. I am trying to create a SQL stored procedure for generating reports. I want to use temporary database which in this example...
1
by: Muhammad Intikhab Qaiser | last post by:
Hi I am developing a parser to parse the stored procedure and function definition of MySQL.I need to extract the names of stored procedures,functions and tables which are being used in any...
1
by: jshunter | last post by:
I've got a weird one here. I'm running a DTS package on SQL Server 2005. It copies a bunch of stored procedures. I renamed them on the originating server and ran the DTS again. The came over...
0
by: Frank Swarbrick | last post by:
You should be able to use Control Center. Find the folder Stored Procedures in the Application Objects folder. Each procedure will be listed, and there is a column called Input Parameters that...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.