473,855 Members | 2,068 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

How to get stored procedure definition?

On a remote system I got a stored procedure named MY_STORED_PROCE DURE.
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 31954
The system catalog is your friend :-)

select text from syscat.ROUTINES where
routinename='MY _STORED_PROCEDU RE'

or for parameter specific info:

select * from syscat.ROUTINEP ARMS where
routinename='MY _STORED_PROCEDU RE'

Jagger wrote:
On a remote system I got a stored procedure named MY_STORED_PROCE DURE.
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*********@gm ail.comwrote in message
news:8d******** *************** ***********@a1g 2000hsb.googleg roups.com...
On a remote system I got a stored procedure named MY_STORED_PROCE DURE.
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...@gma il.comwrote:
The system catalog is your friend :-)

select text from syscat.ROUTINES where
routinename='MY _STORED_PROCEDU RE'

or for parameter specific info:

*select * from syscat.ROUTINEP ARMS where
routinename='MY _STORED_PROCEDU RE'
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
19074
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
39160
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 create the procedure then take a look at it through em, it only shows the code up until the word "GO". Example: CREATE PROCEDURE mytest as create table mytable col1 varchar(5), col2 varchar(10)
7
15605
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 this procedure it reads a dataset and return the first line of the dataset. I met two problems: Problem 1:
5
5177
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 'D11' to :workdept and I have checked in the table that 6 rows should have returned. Any ideas why no resultset is being returned. import java.sql.*; import sqlj.runtime.*; import sqlj.runtime.ref.*; import java.io.*; // Input/Output classes
8
9875
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 a temp table. The procedure takes two parameters to specify the criteria on selecting the table information for inclusion in the result set, builds the temp table,
7
3723
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 it is trult not needed but due to past experience it is key to developing good reports for cliebt/server application. Here is my example which of course dopes not work and documentation on the Internet is limited. Please if anyone can clarify...
1
1858
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 particular stored procedure definition.How i can get this information? Also i want to know at what places within the stored procedures are the names of tables and other stored procedures can be used? Thanks
1
2403
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 with the old name and code! I deleted the DTS and built it from scratch, and the same thing happened. I ran SELECT * FROM sys.objects where type = 'P' on the source server and the names were correct
0
2198
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 should help. Also you can click on the one you want and it will show Parameter Details with the Name, Data type and Mode (IN, OUT, INOUT). n 6/5/2008 at 8:15 AM, in message <8d0a6c4b-3897-4ac7-a198-95be7b93366c@a1g2000hsb.googlegroups.com>,...
0
11044
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
10692
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
10767
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
10375
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
9526
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
0
5754
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
5952
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
2
4168
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
3194
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

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.