How to retreive stored procedure from DB2/400
How to retrieve the stored procedure body from db2/as400.
for example :
I created a Stored Procedure as
create procedure botest/spload
...
...
Software/Hardware used:
Software/Hardware used:
ASKED:
February 4, 2009 9:34 AM
UPDATED:
May 9, 2012 12:46 AM
Answer Wiki:
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
---------------
To see all answers submitted to the Answer Wiki: View Answer History.
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.
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.
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.
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?
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?
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