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:

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:

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:

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:

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.

Where are stored storesprocedures to copy them?

20 pointsBadges:

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: