Re: CLRLIB + RSTLIB. Do they affect stored procedures?

I am still struggling with this. Yesterday, all stored procedures existed
in the restored library, for once. Today, all stored procedures except all
the EXTERNAL ones exist. I can not find any SQL* error messages or mentions
of any missing stored procedures in the joblog, so I am at a complete loss.
(I can't find any mention of the existing stored procedures in the joblog
either, even though their QSYS2/SYSPROCS entries exist and the related CLE
*PGM objects exist in the restored library.)

Should I create a script to "create or replace" all stored procedures after
the RSTLIB? Does a tool already exist to copy DB2 stored procedures from
one schema to another? (I have the source scripts, but some have the
original schema name hard-coded in them. A failing of our coding
specifications I think.)

Additionally, this link: <
http://www-01.ibm.com/support/docview.wss?uid=nas8N1016731> seems to
indicate that our use of "EXTERNAL pgmlib/rpgpgm" in the external stored
procedures would result in "EXTERNAL newfillib/rpgpgm" when the data
library is restored to newfillib. Does everyone put their shared business
logic programs in their data library? I was always taught to keep the *file
and *pgm objects in separate libraries but I've had to make an exception
for the non-EXTERNAL stored procedures; I am not happy with moving shared
business logic programs (i.e. EXTERNAL stored procs) into the file library!
Arco mentions an ALTER PROCEDURE call which I'm guessing is to correct this
very problem, but it is a problem *caused* by RSTLIB in my opinion.


-Paul.


PS If I am following Arco's comments correctly he seems to be saying that
the CREATE source can be found from the resulting PGM objects, and his
problem was caused by the PGM objects not being up to date. This probably
doesn't apply to me as the stored procedures mostly exist, but some of them
do or don't exist on different days. Its more like a race condition
somewhere but with no "missing object" errors to give me a clue.



On 20 December 2016 at 21:14, Arco Simonse <arco400@xxxxxxxxx> wrote:

I forgot to mention that there was also an ALTER PROCEDURE involved to
rename the external name but that did not update the object info. We have
had a ptf for that but I don't have details at hand yet.

Op dinsdag 20 december 2016 heeft Arco Simonse <arco400@xxxxxxxxx> het
volgende geschreven:

Hi,

Just a wild guess based on a situation we had here, and serving on memory
how it was explained to me then.

The restore process will check the SP program object whether it contains

a

CREATE PROCEDURE statement associated with it. This information is

normally

stored in the object, and can be retrieved by DMPOBJ

OBJ(YOURLIB/YOURSPPGM)

OBJTYPE(*PGM)

The QSYS2/SYSPROCS table is updated with that information by the restore
process.

We have had a situation where the SP program object did not contain such
CREATE PROCEDURE statement associated with it.

The procedure was created when the program object did not exist. Like

this:

CREATE OR REPLACE PROCEDURE
myproc
LANGUAGE RPGLE
EXTERNAL NAME 'FAKELIB/MYSPPGM'
;

An SQL message SQL7909 was issued but ignored:
Message . . . . : MYPROC was created, changed, or dropped, but object
not modified.

The program object was an external RPG program, and it was compiled

later.

Since the information about MYPROC was not correct, the program object

was

not updated with the SQL CREATE information.
This caused that the stored procedure was not recreated at restore time.

Best regards,
Arco Simonse



2016-12-20 16:29 GMT+01:00 Paul Bailey <PabloMotte+Midrange@xxxxxxxxx
<javascript:_e(%7B%7D,'cvml','PabloMotte%2BMidrange@xxxxxxxxx');>>:

Hi,

I'm having some confusing results recently. We're keeping a backup of
"last
night's" live data library on our system. We save the live data library

to

tape every night, then use CLRLIB to empty the backup library, and

finally

RSTLIB to restore the save from the tape to the backup library.

I'm expecting every file, logical, table, view, data area, stored
procedure
and user-defined function (everything specific to that library) to be
removed then restored long before we turn up for work in the morning.

We've found that *some* stored procedures do not get recreated during

the

RSTLIB. Are my expected results incorrect? CLRLIB and RSTLIB should work
on
SQL "objects" just like they do on traditional objects, right?

We've recently moved to IBM os v7.1, and are slowly introducing DB2

stored

procedures for use on our website.


-Paul.
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
<javascript:_e(%7B%7D,'cvml','MIDRANGE-L@xxxxxxxxxxxx');>
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
<javascript:_e(%7B%7D,'cvml','MIDRANGE-L-request@xxxxxxxxxxxx');>
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.

Please contact support@xxxxxxxxxxxx
<javascript:_e(%7B%7D,'cvml','support@xxxxxxxxxxxx');> for any
subscription related questions.

Help support midrange.com by shopping at amazon.com with our affiliate
link: http://amzn.to/2dEadiD

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.

Please contact support@xxxxxxxxxxxx for any subscription related
questions.

Help support midrange.com by shopping at amazon.com with our affiliate
link: http://amzn.to/2dEadiD