How to retreive stored procedure from DB2/400

Sivu

105 pts.

Tags:

AS/400 DB2

AS/400 development

DB2 stored procedures

DB2/400

How to retrieve the stored procedure body from db2/as400. for example : I created a Stored Procedure as create procedure botest/spload ... ...

Asked: February 4, 2009 9:34 AM Last updated: May 9, 2012 12:46 AM

Answer Wiki

Last updated: February 4, 2009 5:12 PM GMT

Cwc4,290 pts.
History
Contributors
- Ordered by most recent
- Cwc4,290 pts.

Thanks. We'll let you know when a new response is added.

Do you have iSeries Navigator on your PC? You can use it to drill down to where the procedure is located on the i:

After entering Navigator, from the Environment box on the left, select your system name, then Databases, [system name], then Schemas, then [library or schema name], and then Procedures. In the window on the right, you can select the procedure you want to view. Right click on it, and select Properties. Under the SQL Statements tab, you can see the statements the procedure executes. (It shows them as one long string, so they’re not formatted for best readability, but it does have the info.)

CWC
—————

Discuss This Question: 6 Replies

There was an error processing your information. Please try again later.

Thanks. We'll let you know when a new response is added.

Send me notifications when members answer or reply to this question.

Sivu Feb 6, 2009 8:59 AM GMT

Still unable to find the code which i have written I think, the procedure is getting generated as a program while running the same in STRSQL. The job log says, a module is getting created and a program is created, finally the module is deleted by the system itself. We are unable to find the source details for the procedure since it is created as program (same as EXE format: You can not view the coding) i created the below proc CREATE PROCEDURE BOTEST/tempproc LANGUAGE SQL P1: BEGIN DECLARE PROC_DATE CHAR(10); SET PROC_DATE = SUBSTR(PROCDATE,1,4)||'-' ||SUBSTR(PROCDATE,5,2)||'-'||SUBSTR(PROCDATE,7,2); END P1 following are the sys msgs: File QSQLSRC in library QTEMP already exists. File QSQLSRC in library QTEMP changed. Member TESTPROC added to file QSQLSRC in QTEMP. Member TESTPROC added to file QSQLT00160 in QTEMP. Module TESTPROC was created in library BOTEST on 06/02/09 at 13:15:57. Program TESTPROC created in library BOTEST. Object TESTPROC in BOTEST type *MODULE deleted.

105 pointsBadges:

report
Cwc Feb 6, 2009 3:19 PM GMT

Once created, a stored procedure should remain intact until someone or some program instructs the system otherwise. In your log below, the system is creating some temporary files and modules 'behind the scenes', and then it's cleaning up these temporary objects after the stored procedure has been created. So your stored procedure is still there. The code to create it is one and the same as your statement. I suspect the statement is stored in a system library, but I'm not sure exactly where or which file. But do you even need that, since you already have the statement which created your procedure? Also, have you looked through iSeries Navigator? You can view the SQL procedures through that application.

4,290 pointsBadges:

report
Ashnirody Feb 8, 2009 8:28 PM GMT

The log is from the creation of a SQLCBL program TESTPROC in library BOTEST. This is standard - a module is created, a program is created and then the module deleted. The CREATE PROCEDURE statement is for an stored procedure TEMPPROC (NOT TESTPROC). You are confused with the similar names. There is no connection between the two.

100 pointsBadges:

report
Kantoborgy May 8, 2012 6:06 PM GMT

Interesting, but how to support the store procedures? I use db2 dds with rpg and when I do a restorelib savelib and another box will not pass the store procedures. Where are stored storesprocedures to copy them?

20 pointsBadges:

report
Kantoborgy May 8, 2012 6:07 PM GMT
Interesting, but how to support the store procedures? I use db2 dds with rpg and when I do a restorelib savelib and another box will not pass the store procedures.
20 pointsBadges:
report
TomLiotta May 9, 2012 0:46 AM GMT

Where are stored storesprocedures to copy them? That depends on whether you mean the compiled stored procedures or the DB2 registration of the stored procedures. The physical stored procedures are programs that can be saved and restored. But if you want DB2 to know how to interface with your stored procedures, then you would save the source that you used when you created them. Transfer the source and run it after restoring the source on the target system. If you did not save the source, you can retrieve it from DB2. One easy way is to use iSeries Navigator. Drill down into your connection -- Databases-> dbname-> Schemas-> schemaname. Click Procedures. Select the procedures you want, right-click and select 'Generate SQL...'. A SQL CREATE PROCEDURE statement will be generated for you from the database. You might want to practice on a few of them to learn all of the possible options. Tom

125,585 pointsBadges:

report