how to drop table and drop related views/functions etc...

[alessandro menchini]

Hi all,

When i try to drop a table, and this table have (for example) some
function related...db2 don't allow to drop the table

how can i enter a command to drop the table AND all the objects
(functions...triggers...views) that depends on that table?

A.M.

[Mehmet Baserdem]

Alessandro,

When checking dependencies, would it be enough to check only below
catalog tables:

SYSCAT.INDEXDEP
SYSCAT.PACKAGEDEP
SYSCAT.ROUTINEDEP
SYSCAT.TABDEP

Am I missing any other db2 objects?

BTW: I don't think such a drop command exists b/c of circular & self
referencing complications. They need to be resolved delicately.

Regards,

Mehmet Baserdem

[Serge Rielau]

Mehmet Baserdem wrote:

Alessandro,

When checking dependencies, would it be enough to check only below
catalog tables:

SYSCAT.INDEXDEP
SYSCAT.PACKAGEDEP
SYSCAT.ROUTINEDEP
SYSCAT.TABDEP

Am I missing any other db2 objects?

BTW: I don't think such a drop command exists b/c of circular & self
referencing complications. They need to be resolved delicately.

You can use the SYSPROC.DB2LK_DEP_OF() table function.
It's undocumented, but the arguments are self explanatory.

Cheers
Serge

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

WAIUG Conference
http://www.iiug.org/waiug/present/Fo...Forum2006.html

[Lennart]

Serge Rielau wrote:
[...]

BTW: I don't think such a drop command exists b/c of circular & self
referencing complications. They need to be resolved delicately.

You can use the SYSPROC.DB2LK_DEP_OF() table function.
It's undocumented, but the arguments are self explanatory.

Thats nifty. But shouldnt it work for all kinda objects? I tried the
following:

create function tmp.test1() returns int return 3;
create function tmp.test2() returns int return values tmp.test1();

thus test2 is dependent of test1. But neither:

DB2LK_DEP_OF('F',TMP','TEST1'), DB2LK_DEP_OF('F',TMP','TEST2'), nor
using the specificname returns a row.

I noticed that DB2LK_DEP_OF invokes DB2LK_DEP_FIRST, and the part that
handles functions looks like:

SELECT 'F', T1.ROUTINESCHEMA, T1.ROUTINENAME, T2.SPECIFICNAME,
T2.DEFINER, T2.CREATE_TIME
FROM SYSCAT.ROUTINEDEP T1, SYSCAT.ROUTINES T2
WHERE T1.BTYPE = 'F'
AND T1.BSCHEMA = 'TMP'
AND T1.ROUTINESCHEMA = T2.ROUTINESCHEMA
AND T1.ROUTINENAME = T2.ROUTINENAME
;

but that join does not work because of:

SELECT ROUTINESCHEMA , ROUTINENAME
FROM SYSCAT.ROUTINEDEP
WHERE ROUTINESCHEMA = 'TMP';

ROUTINESCHEMA
ROUTINENAME
--------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------
TMP

SQL070104223111900
1 record(s) selected.
SELECT ROUTINESCHEMA , ROUTINENAME
FROM SYSCAT.ROUTINES
WHERE ROUTINESCHEMA = 'TMP';

ROUTINESCHEMA
ROUTINENAME
--------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------
TMP
TEST1
TMP
TEST2
That is, ROUTINENAME in SYSCAT.ROUTINES is different from ROUTINENAME
in SYSCAT.ROUTINEDEP

If the join is changed to:

SELECT 'F', T1.ROUTINESCHEMA, T1.ROUTINENAME, T2.SPECIFICNAME,
T2.DEFINER, T2.CREATE_TIME
FROM SYSCAT.ROUTINEDEP T1, SYSCAT.ROUTINES T2
WHERE T1.BTYPE = 'F'
AND T1.BSCHEMA = 'TMP'
AND T1.ROUTINESCHEMA = T2.ROUTINESCHEMA
AND T1.ROUTINENAME = T2.SPECIFICNAME
;

it seems to work:

1 ROUTINESCHEMA
ROUTINENAME

SPECIFICNAME

DEFINER

CREATE_TIME
-
--------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------
--------------------------
F TMP

SQL070104224459500

SQL070104224459500
DB2INST1

2007-01-04-22.44.59.521521

1 record(s) selected.
It looks like a bug, but I'm not convinced that I understand how it
should work.

[db2inst1@ft-05 ~]$ db2level
DB21085I Instance "db2inst1" uses "32" bits and DB2 code release
"SQL08026"
with level identifier "03070106".
Informational tokens are "DB2 v8.1.2.120", "s060801", "MI00163", and
FixPak
"13".
Product is installed at "/opt/IBM/db2/V8.1".

[db2inst1@ft-05 ~]$ uname -a
Linux ft-05 2.6.9-42.ELsmp #1 SMP Wed Jul 12 23:27:17 EDT 2006 i686
i686 i386 GNU/Linux

/Lennart

[Serge Rielau]

Lennart wrote:

Serge Rielau wrote:
[...]

>>BTW: I don't think such a drop command exists b/c of circular & self
referencing complications. They need to be resolved delicately.

You can use the SYSPROC.DB2LK_DEP_OF() table function.
It's undocumented, but the arguments are self explanatory.

Thats nifty. But shouldnt it work for all kinda objects? I tried the
following:

... the initial version I wrote was meant to be extensible. Don't recall
whether it was changed since then....

create function tmp.test1() returns int return 3;
create function tmp.test2() returns int return values tmp.test1();

What is it with VALUES? I see a lot of unnecessary usage of VALUES, is
there some book that's spreading this coding style?
RETURN tmp.test1() should work just fine...
VALUES actually produces a table, making RETURN VALUES .. a scalar
subquery...

thus test2 is dependent of test1. But neither:

DB2LK_DEP_OF('F',TMP','TEST1'), DB2LK_DEP_OF('F',TMP','TEST2'),

Ok, this should definitely not work. You need the specific name for sure

nor using the specificname returns a row.

I noticed that DB2LK_DEP_OF invokes DB2LK_DEP_FIRST, and the part that
handles functions looks like:

SELECT 'F', T1.ROUTINESCHEMA, T1.ROUTINENAME, T2.SPECIFICNAME,
T2.DEFINER, T2.CREATE_TIME
FROM SYSCAT.ROUTINEDEP T1, SYSCAT.ROUTINES T2
WHERE T1.BTYPE = 'F'
AND T1.BSCHEMA = 'TMP'
AND T1.ROUTINESCHEMA = T2.ROUTINESCHEMA
AND T1.ROUTINENAME = T2.ROUTINENAME

<snip>

That is, ROUTINENAME in SYSCAT.ROUTINES is different from ROUTINENAME
in SYSCAT.ROUTINEDEP

If the join is changed to:

SELECT 'F', T1.ROUTINESCHEMA, T1.ROUTINENAME, T2.SPECIFICNAME,
T2.DEFINER, T2.CREATE_TIME
FROM SYSCAT.ROUTINEDEP T1, SYSCAT.ROUTINES T2
WHERE T1.BTYPE = 'F'
AND T1.BSCHEMA = 'TMP'
AND T1.ROUTINESCHEMA = T2.ROUTINESCHEMA
AND T1.ROUTINENAME = T2.SPECIFICNAME
;

<snip>

It looks like a bug, but I'm not convinced that I understand how it
should work.

I think you are correct. That is a bug.

I'll follow up. In the meantime any potential consumer can create their
own variety.

>
[db2inst1@ft-05 ~]$ db2level
DB21085I Instance "db2inst1" uses "32" bits and DB2 code release
"SQL08026"
with level identifier "03070106".
Informational tokens are "DB2 v8.1.2.120", "s060801", "MI00163", and
FixPak
"13".
Product is installed at "/opt/IBM/db2/V8.1".

[db2inst1@ft-05 ~]$ uname -a
Linux ft-05 2.6.9-42.ELsmp #1 SMP Wed Jul 12 23:27:17 EDT 2006 i686
i686 i386 GNU/Linux

/Lennart

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

WAIUG Conference
http://www.iiug.org/waiug/present/Fo...Forum2006.html

[Lennart]

Serge Rielau wrote:
[...]

What is it with VALUES? I see a lot of unnecessary usage of VALUES, is
there some book that's spreading this coding style?

I actually tested it with an existing function first, but needed
something simpler for an example. Did not put to much thought into the
function though. But, if it is a problem that people use values this
way it might be caused by how the function is fully declared:

CREATE FUNCTION ... ()
RETURNS INT
LANGUAGE SQL
....

3 is not a valid SQL stmt (at least I don't think it is), and if one
tries to be a good citizen (:-) one might be fooled to use a valid sql
stmt in there.

RETURN tmp.test1() should work just fine...
VALUES actually produces a table, making RETURN VALUES .. a scalar
subquery...

Does the values clause cause any overhead? I could not resist checking
out my functions. None of them returned a scalar constant, so I'm just
curious.

>

thus test2 is dependent of test1. But neither:

DB2LK_DEP_OF('F',TMP','TEST1'), DB2LK_DEP_OF('F',TMP','TEST2'),

Ok, this should definitely not work. You need the specific name for sure

Yep, that figures. Just wanted to make sure by including this as well.

[...]

That is, ROUTINENAME in SYSCAT.ROUTINES is different from ROUTINENAME
in SYSCAT.ROUTINEDEP

While I'm at it (I fully understand that it is not possible to change
the schema, just reflecting), the name of the column in
SYSCAT.ROUTINEDEP seems wrong. It would make more sense if it where
named SPECIFICNAME. I guess that is what fooled you in the first place?

[...]

>
I'll follow up. In the meantime any potential consumer can create their
own variety.

Definitely, I have been thinking about creating such a function for
some time, and this will certainly make a nice start.

For the wish list: it would be nice if the function returned
VISUAL_NAME as well as OBJECTNAME. For tables and such they would be
the same, but for PROCEDURES, FUNCTIONS and such, both MY_PROCEDURE and
P4183825 where returned.

Just some thoughts, keep up the good work
/Lennart

[Lennart]

Serge Rielau wrote:
[...]

What is it with VALUES? I see a lot of unnecessary usage of VALUES, is
there some book that's spreading this coding style?

I actually tested it with an existing function first, but needed
something simpler for an example. Did not put to much thought into the
function though. But, if it is a problem that people use values this
way it might be caused by how the function is fully declared:

CREATE FUNCTION ... ()
RETURNS INT
LANGUAGE SQL
....

3 is not a valid SQL stmt (at least I don't think it is), and if one
tries to be a good citizen (:-) one might be fooled to use a valid sql
stmt in there.

RETURN tmp.test1() should work just fine...
VALUES actually produces a table, making RETURN VALUES .. a scalar
subquery...

Does the values clause cause any overhead? I could not resist checking
out my functions. None of them returned a scalar constant, so I'm just
curious.

>

thus test2 is dependent of test1. But neither:

DB2LK_DEP_OF('F',TMP','TEST1'), DB2LK_DEP_OF('F',TMP','TEST2'),

Ok, this should definitely not work. You need the specific name for sure

Yep, that figures. Just wanted to make sure by including this as well.

[...]

That is, ROUTINENAME in SYSCAT.ROUTINES is different from ROUTINENAME
in SYSCAT.ROUTINEDEP

While I'm at it (I fully understand that it is not possible to change
the schema, just reflecting), the name of the column in
SYSCAT.ROUTINEDEP seems wrong. It would make more sense if it where
named SPECIFICNAME. I guess that is what fooled you in the first place?

[...]

>
I'll follow up. In the meantime any potential consumer can create their
own variety.

Definitely, I have been thinking about creating such a function for
some time, and this will certainly make a nice start.

For the wish list: it would be nice if the function returned
VISUAL_NAME as well as OBJECTNAME. For tables and such they would be
the same, but for PROCEDURES, FUNCTIONS and such, both MY_PROCEDURE and
P4183825 where returned.

Just some thoughts, keep up the good work
/Lennart

[Knut Stolze]

Lennart wrote:

>
Serge Rielau wrote:
[...]

>What is it with VALUES? I see a lot of unnecessary usage of VALUES, is
there some book that's spreading this coding style?

I actually tested it with an existing function first, but needed
something simpler for an example. Did not put to much thought into the
function though. But, if it is a problem that people use values this
way it might be caused by how the function is fully declared:

CREATE FUNCTION ... ()
RETURNS INT
LANGUAGE SQL
...

3 is not a valid SQL stmt (at least I don't think it is), and if one
tries to be a good citizen (:-) one might be fooled to use a valid sql
stmt in there.

Right, 3 is not a SQL statement. It is an expression. But "RETURN 3" is a
valid statement as is "RETURN function()"

>RETURN tmp.test1() should work just fine...
VALUES actually produces a table, making RETURN VALUES .. a scalar
subquery...

Does the values clause cause any overhead? I could not resist checking
out my functions. None of them returned a scalar constant, so I'm just
curious.

Another question would be: given that VALUES is unnecessarily used quite
often as Serge says, couldn't the optimizer or query rewrite "just" remove
it in such cases?

--
Knut Stolze
DB2 z/OS Utilities Development
IBM Germany

[Lennart]

Knut Stolze wrote:
[...]

Right, 3 is not a SQL statement. It is an expression. But "RETURN 3" is a
valid statement as is "RETURN function()"

Just to avoid confusion, I'm not saying that it is the case, nor that
it is a correct conclusion. I'm just speculating over what might be the
cause for Serges observations.

/Lennart