How to retreive stored procedure from DB2/400

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 ... ...

Answer Wiki

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.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • Sivu
    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
    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
    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
    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
    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
  • TomLiotta
    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

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

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

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following