Possible to manually mark routine packages as inoperative in DB2LUW?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

mryangza@gmail.com - 23 Oct 2008 10:48 GMT

Hi all,

Is there a mechanism for manually marking routine packages as
inoperative in DB2LUW?

The problem I'm trying to solve is one of recreating interdependent
functions. My application creates a number of functions during its
installation. A later version of the application introduces changes to
some of these functions and needs to recreate those that were
affected. Now, I cannot drop some of these since they are used by
other functions.

I've been looking for a way of avoiding dropping all dependant before
recreating a function. I was wondering whether dependant functions
associated with inoperative packages would restrict a drop operation
of the function they depend on?

Reply to this Message

Serge Rielau - 23 Oct 2008 12:31 GMT

> Hi all,
>
[quoted text clipped - 12 lines]
> associated with inoperative packages would restrict a drop operation
> of the function they depend on?

I'm somewhat dumbfounded by what you are saying.
In DB2 as of today SQL Functions are inlined, they cannot be
interdependent directly.
The only way to get interdependent SQL Functions is to either go through
an external UDF (with SQL in it) or by CALLing a PROCEDURE in the mix.
That would also be the only objects with packages (inline SQL Funnctions
by definition have none.
It should be no problem to drop the functions that the procedure depnds on.
Do you have a repro scenario?

Cheers
Serge

Signature

Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

Reply to this Message

mryangza@gmail.com - 23 Oct 2008 12:45 GMT

Sorry, I think I've mixed up my terminology or at least, haven't been
particularly clear.

Consider the following situation:

CREATE FUNCTION MYSUM(
    i1 INTEGER,
    i2 INTEGER)
RETURNS INTEGER
LANGUAGE SQL
DETERMINISTIC
NO EXTERNAL ACTION
CONTAINS SQL
    RETURN i1 + i2

CREATE FUNCTION MYFUNC(
    i1 INTEGER,
    i2 INTEGER)
RETURNS INTEGER
LANGUAGE SQL
DETERMINISTIC
NO EXTERNAL ACTION
CONTAINS SQL
    RETURN MYSUM(i1, i2)

Now, if I decide I want to recreate MYSUM, for some reason, then when
I attempt to drop it I am restricted because MYFUNC depends on it.

Is there a mechanism for somehow marking MYFUNC as inoperative (not
sure that you can do this with UDF's?) that will allow me to drop
MYSUM successfully?

Thanks,

Ryan

Reply to this Message

Serge Rielau - 23 Oct 2008 18:18 GMT

> Sorry, I think I've mixed up my terminology or at least, haven't been
> particularly clear.
[quoted text clipped - 27 lines]
> sure that you can do this with UDF's?) that will allow me to drop
> MYSUM successfully?

You have to drop MYFUNC first.
Are you coming to IOD? If so you want to attend:
2343 SQL PL, All Grown Up
2505 Shifting Shapes: Transforming the Way You Evolve Your DB Schema
2999 You will also like.

Cheers
Serge

Signature

Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

Reply to this Message

mryangza@gmail.com - 24 Oct 2008 16:24 GMT

Thanks for the (as usual) quick and helpful response Serge, this is
pretty much what I expected and implemented in my application upgrade.
I just wanted to confirm that there wasn't some magic hidden somewhere
that would allow us to sidestep the restrictions :)

I'm afraid I won't be attending IOD, it seems like it would have been
a great learning experience.

Thanks again,

Ryan

Reply to this Message

Ian - 25 Oct 2008 06:18 GMT

> You have to drop MYFUNC first.
> Are you coming to IOD? If so you want to attend:
> 2343 SQL PL, All Grown Up

2343, are you sure? I don't see that...

Reply to this Message

Serge Rielau - 25 Oct 2008 19:51 GMT

>> You have to drop MYFUNC first.
>> Are you coming to IOD? If so you want to attend:
>> 2343 SQL PL, All Grown Up
>
> 2343, are you sure? I don't see that...

TLU-2343 SQL PL - All Grown up
2343A Wed, 29/Oct 11:30 AM - 12:30 PM Mandalay Bay North Convention
Center - Islander E

I'll try to find out why it isn't listed yet.

Cheers
serge

Signature

Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

Reply to this Message

Serge Rielau - 25 Oct 2008 22:36 GMT

>>> You have to drop MYFUNC first.
>>> Are you coming to IOD? If so you want to attend:
[quoted text clipped - 6 lines]
>
> I'll try to find out why it isn't listed yet.

Fixed.

Cheers
Serge

Signature

Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

Reply to this Message

Ian - 26 Oct 2008 15:02 GMT

>>>> You have to drop MYFUNC first.
>>>> Are you coming to IOD? If so you want to attend:
[quoted text clipped - 7 lines]
>> I'll try to find out why it isn't listed yet.
> Fixed.

Awesome, thanks!

Reply to this Message