How to retreive stored procedure from DB2/400
Home > IT Answers > AS/400 > How to retreive stored procedure from DB2/400
Sivu
105 pts.
0
Q:
How to retreive stored procedure from DB2/400
DB2/400, AS/400 DB2, DB2 stored procedures, AS/400 development
How to retrieve the stored procedure body from db2/as400.

for example :
I created a Stored Procedure as
create procedure botest/spload
...
...
ASKED: Feb 4 2009  9:34 AM GMT
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
RELATED QUESTIONS
0
Cwc
3860 pts.
0
A:
 RATE THIS ANSWER
+1
Click to Vote:
  •   1
  •  0
  • AddThis Social Bookmark Button
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
---------------
Last Answered: Feb 4 2009  5:12 PM GMT by Cwc   3860 pts.
  •   
0
0
RSS - Discussions Help
Discuss This Answer:
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _



_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _

Sivu   105 pts.  |   Feb 6 2009  8:59AM 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.

 

Cwc   3860 pts.  |   Feb 6 2009  3:19PM 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.

 

Ashnirody   100 pts.  |   Feb 8 2009  8:28PM 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.

 
0