drop an inactive parent NSP(Native stored procedure),

Sue howe

drop an inactive parent NSP(Native stored procedure),

Hi DB2 SME's

I am referring docs, but I want to ask these question for DB2 V12 z/OS NSP's

 

Question  1 : if I  drop an inactive parent NSP(Native stored procedure),what will happen for any child inactive  NSP’s?

Question  2 : if I  drop an inactive parent NSP(Native stored procedure),what will happen for any child  active NSP’s?

Question  3 : if I  drop an inactive child NSP(Native stored procedure), what will happen for any Parent inactive  NSP’s?

Question  4 : if I  drop an inactive child NSP (Native stored procedure),what will happen for any Parent active NSP’s?

Question 5: if any inactive NSP whether it parent or child getting drop what will happen to dependent NSP’s?

 

--

Sue

Nadir Doctor

drop an inactive parent NSP(Native stored procedure),
(in response to Sue howe)
Hi Sue,

If you're unable to get any specific leads from the documentation,
recommend that it will assist in raising a PMR with Db2 support to get
definite answers for them.

Nadir




On Tue, Jul 28, 2020 at 6:28 PM Sue howe <[login to unmask email]> wrote:

> Hi DB2 SME's
>
> I am referring docs, but I want to ask these question for DB2 V12 z/OS
> NSP's
>
>
>
> Question 1 : if I drop an *inactive* *parent* NSP(Native stored
> procedure),what will happen for any *child* *inactive* NSP’s?
>
> Question 2 : if I drop an *inactive* *parent* NSP(Native stored
> procedure),what will happen for any *child* *active* NSP’s?
>
> Question 3 : if I drop an *inactive* *child* NSP(Native stored
> procedure), what will happen for any *Parent* *inactive* NSP’s?
>
> Question 4 : if I drop an *inactive* *child* NSP (Native stored
> procedure),what will happen for any *Parent* *active* NSP’s?
>
> Question 5: if any *inactive* NSP whether it parent or child getting drop
> what will happen to dependent NSP’s?
>
>
>
> --
>
> Sue
>
> -----End Original Message-----
>

Michael Hannan

RE: drop an inactive parent NSP(Native stored procedure),
(in response to Sue howe)

Sue,

There is no Referential Integrity maintained between Parent and Child Native Stored Procs, right? So DB2 should treat them as independent I would have thought. Not that I have tried any Child NSPs.

Therefore the effect on one, of dropping another, seems logically that it should no effect at all, whether current (active) or an old version (inactive). Just trying to apply logic to the question which seemed unusual to me.

Michael Hannan,
DB2 Application Performance Specialist

Sam Baugh

drop an inactive parent NSP(Native stored procedure),
(in response to Michael Hannan)
Can't say that I've ever tested anything with an "inactive" stored
procedures. It would be nice if Db2 would treat native stored procedures
as independents, but it doesn't, which can make it a pain to deal with. If
you don't have to change parameters, you can use ALTER PROCEDURE, but
anything that requires a DROP and CREATE will require the same for all
native stored procedures that call it. So if A calls B and you try to drop
and B, you will have to drop A before you can drop B (or did I get this
backwards?). You can always experiment with this, just create a set of
simple native stored procedures that call each other. Db2 may not
establish the dependencies between them until they have been executed, so
be sure to run them before you test with dropping them, otherwise it may be
misleading when it appears that you can drop one without impacting the
others.

On Wed, Jul 29, 2020 at 7:22 AM Michael Hannan <[login to unmask email]> wrote:

> Sue,
>
> There is no Referential Integrity maintained between Parent and Child
> Native Stored Procs, right? So DB2 should treat them as independent I would
> have thought. Not that I have tried any Child NSPs.
>
> Therefore the effect on one, of dropping another, seems logically that it
> should no effect at all, whether current (active) or an old version
> (inactive). Just trying to apply logic to the question which seemed unusual
> to me.
>
> Michael Hannan,
> DB2 Application Performance Specialist
>
> -----End Original Message-----
>

James Campbell

drop an inactive parent NSP(Native stored procedure),
(in response to Michael Hannan)
I think the 'referential integrity' is based on SYSPACKDEP.

Perhaps CREATE OR REPLACE PROCEDURE (and TRIGGER) (FL507) will make all this
easier.

James Campbell

On 29 Jul 2020 at 5:21, Michael Hannan wrote:

> Sue,
>
> There is no Referential Integrity maintained between Parent and Child Native Stored Procs, right? So DB2 should treat them as independent I would have thought. Not that I have tried any Child NSPs.
> Therefore the effect on one, of dropping another, seems logically that it should no effect at all, whether current (active) or an old version (inactive). Just trying to apply logic to the question which seemed unusual to me.
> Michael Hannan,
> DB2 Application Performance Specialist


--
This email has been checked for viruses by AVG.
https://www.avg.com

Sue howe

RE: drop an inactive parent NSP(Native stored procedure),
(in response to James Campbell)
thank you for sharing ideas and shedding some light... appreciate your kind help
i am the process of referring docs, but want to ask this
my manager kept this new requirement in front of me
he want to just keep total 4 versions ( 1 active version of NSP+ 3 latest inactive versions of NSP) and drop the rest( example below)
Active NSP XYZ created July/30 (don't drop)
Inactive NSP XYZ created July/ 29(don't drop)
Inactive NSP XYZ created July/ 28(don't drop)
Inactive NSP XYZ created July/ 27(don't drop)
Inactive NSP XYZ created July/ 26(drop)
Inactive NSP XYZ created July/ 25(drop
how we can achieve this ?(above dates are example only, in my shop inactive versions exist year back aswell)
any thoughts ?
--
Sue