can not drop table but can not find dependence either

[uwcssa]

I try to drop a table as:

drop table sch.tab;

I got: During SQL processing it returned:
SQL0478N The object type "TABLE" cannot be dropped because there is
an object "sch.SQL0705151 04729271", of type "FUNCTION", which depends
on it. SQLSTATE=42893

Then I tried to do

>drop function sch.SQL07051510 4729271

but it says this is not defined. Actually, I tried to search that
function with
SELECT tabname FROM SYSCAT.TABLES
union SELECT procname FROM SYSCAT.procedur es
union SELECT funcname FROM SYSCAT.function s
union SELECT pkgname FROM SYSCAT.packages
but failed to find that function name. I also search dependency by:

select bschema, bname, dschema, dname from sysibm.sysdepen dencies;

I found:
BSCHEMA BNAME DSCHEMA DNAME
sch tab sch SQL070515104729 271
What does this mean to me? since I can not find what the heck is the
object "SQL07051510472 9271"
Thanks!

[Lennart]

uw****@gmail.co m wrote:

I try to drop a table as:

>drop table sch.tab;

I got: During SQL processing it returned:
SQL0478N The object type "TABLE" cannot be dropped because there is
an object "sch.SQL0705151 04729271", of type "FUNCTION", which depends
on it. SQLSTATE=42893

Then I tried to do

>>drop function sch.SQL07051510 4729271

but it says this is not defined. Actually, I tried to search that
function with

I think that this is the *specific name*, try:

drop specific function sch.SQL07051510 4729271

You probably want to look at the function before dropping it, you should
be able to find it with:

select funcname, body from syscat.function s where specificname =
'SQL07051510472 9271'
/Lennart

[uwcssa]

Thanks for the quick reply!

I found the definition of the function SQL070515104729 271 using the
command:

select funcname, body from syscat.function s where specificname =
'SQL07051510472 9271'

And I get:

SUMSALARIES CREATE FUNCTION SUMSALARIES(DEP T CHAR(3))
RETURNS DECIMAL(9,2)
LANGUAGE SQL
RETURN
SELECT sum(salary)
FROM employee
WHERE workdept = dept

However, as I try to drop this function: using either

drop specific function SQL070515104729 271 ;

I get: SQL0658N The object "sch.SUMSALARIE S" cannot be explicitly
dropped.
SQLSTATE=42917

I also tried:

drop function sumsalaries;

Which gives me the same error. How should I do then? thanks again!

On May 17, 2:18 pm, Lennart <erik.lennart.j [e-mail] mwrote:

[e-mail] m wrote:

I try to drop a table as:

drop table sch.tab;

I got: During SQL processing it returned:
SQL0478N The object type "TABLE" cannot be dropped because there is
an object "sch.SQL0705151 04729271", of type "FUNCTION", which depends
on it. SQLSTATE=42893

Then I tried to do

>drop function sch.SQL07051510 4729271

but it says this is not defined. Actually, I tried to search that
function with

I think that this is the *specific name*, try:

drop specific function sch.SQL07051510 4729271

You probably want to look at the function before dropping it, you should
be able to find it with:

select funcname, body from syscat.function s where specificname =
'SQL07051510472 9271'

/Lennart

[Dave Hughes]

uw****@gmail.co m wrote:

However, as I try to drop this function: using either

drop specific function SQL070515104729 271 ;

I believe you need to include the schema name as well as the specific
name of the function, i.e.:

DROP SPECIFIC FUNCTION SCH.SQL07051510 4729271;

I also tried:

drop function sumsalaries;

Which gives me the same error. How should I do then? thanks again!

Again, include the schema name. Furthermore, if the function has
overloaded versions (functions with the same name, but a different
parameter list), you will need to include the parameter list to
distinguish exactly which overloaded version you wish to drop, i.e.:

DROP FUNCTION SCH.SUMSALARIES (CHAR(3));

Alternatively, change the current schema to the schema containing the
function you wish to drop:

SET SCHEMA SCH;
DROP FUNCTION SUMSALARIES(CHA R(3));
HTH,

Dave.

--

[Knut Stolze]

uw****@gmail.co m wrote:

I try to drop a table as:

>drop table sch.tab;

I got: During SQL processing it returned:
SQL0478N The object type "TABLE" cannot be dropped because there is
an object "sch.SQL0705151 04729271", of type "FUNCTION", which depends
on it. SQLSTATE=42893

Something else that the other posts didn't cover: your schema name is in
lower case. Thus, you have to put double-quotes around it so that DB2
won't convert it to upper case first and then search for the function in
the wrong schema. (Note that your shell may interfere with quotes. The
simples way to avoid that is to start a DB2 interactive shell.)

db2 =drop specific function "sch"."SQL07051 5104729271"

I used double-quotes for the (unqualified) function name as well, even if it
would not have been necessary because it is all upper case.

but it says this is not defined. Actually, I tried to search that
function with
SELECT tabname FROM SYSCAT.TABLES
union SELECT procname FROM SYSCAT.procedur es
union SELECT funcname FROM SYSCAT.function s
union SELECT pkgname FROM SYSCAT.packages

Search in SYSCAT.ROUTINES and use the LIKE predicate on the SPECIFIC name.

SELECT *
FROM syscat.routines
WHERE specificname LIKE '%SQL0705151047 29271'
--
Knut Stolze
DB2 z/OS Utilities Development
IBM Germany

[%NAME%]

I doubled checked, and I did include the schema name with it. It
still can not find it. Moreover, I fail to select from
syscat.routines ,
this is exactly what I copied from terminal:

select * from SYSCAT.ROUTINES ;
SQL0204N "SYSCAT.ROUTINE S" is an undefined name. SQLSTATE=42704
I am sure my function is not overloaded, and I tried:

db2set schema dedehaan;
db2drop table employee;
SQL0478N The object type "TABLE" cannot be dropped because there is
an object
"DEDEHAAN.SQL07 0515104729271", of type "FUNCTION", which depends on
it.
SQLSTATE=42893

db2 =drop function DEDEHAAN.SQL070 515104729271;
SQL0204N "DEDEHAAN.SQL07 0515104729271" is an undefined name.
SQLSTATE=42704
Any clue? btw, I am using db2 :

$ db2level
DB21085I Instance "db2_inst" uses DB2 code release "SQL07020" with
level
identifier "03010105" and informational tokens "DB2 v7.1.0.40",
"s010415" and
"U475377".

[Serge Rielau]

Any clue? btw, I am using db2 :

>
$ db2level
DB21085I Instance "db2_inst" uses DB2 code release "SQL07020" with
level
identifier "03010105" and informational tokens "DB2 v7.1.0.40",
"s010415" and
"U475377".

Oh! Replace SYSCAT.ROUTINES with SYSCAT.FUNCTION S
And ROUTINENAME with FUNCNAME in your queries.

Cheers
Serge

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

[Lennart]

uw****@gmail.co m wrote:

Thanks for the quick reply!

I found the definition of the function SQL070515104729 271 using the
command:

> select funcname, body from syscat.function s where specificname =
'SQL0705151047 29271'

And I get:

SUMSALARIES CREATE FUNCTION SUMSALARIES(DEP T CHAR(3))
RETURNS DECIMAL(9,2)
LANGUAGE SQL
RETURN
SELECT sum(salary)
FROM employee
WHERE workdept = dept

However, as I try to drop this function: using either

>drop specific function SQL070515104729 271 ;

I get: SQL0658N The object "sch.SUMSALARIE S" cannot be explicitly
dropped.
SQLSTATE=42917

I also tried:

>drop function sumsalaries;

Which gives me the same error. How should I do then? thanks again!

either do:

drop specific function SQL070515104729 271 restrict

or

select funcschema, funcname, body from syscat.function s where specificname =
'SQL07051510472 9271'

then:

drop function <funcschema>.<f uncnamerestrict

also beware Knuts info regarding ucase

/Lennart

>

On May 17, 2:18 pm, Lennart <erik.lennart.j [e-mail] mwrote:

>uwc...@gmail.c om wrote:

I try to drop a table as:

>drop table sch.tab;

I got: During SQL processing it returned:
SQL0478N The object type "TABLE" cannot be dropped because there is
an object "sch.SQL0705151 04729271", of type "FUNCTION", which depends
on it. SQLSTATE=42893

Then I tried to do

>>drop function sch.SQL07051510 4729271

but it says this is not defined. Actually, I tried to search that
function with

I think that this is the *specific name*, try:

drop specific function sch.SQL07051510 4729271

You probably want to look at the function before dropping it, you should
be able to find it with:

select funcname, body from syscat.function s where specificname =
'SQL0705151047 29271'

/Lennart

[Tonkuma]

On May 19, 2:08 am, %NAME% <huaxinzh...@gm ail.comwrote:

db2set schema dedehaan;
db2drop table employee;
SQL0478N The object type "TABLE" cannot be dropped because there is
an object
"DEDEHAAN.SQL07 0515104729271", of type "FUNCTION", which depends on
it.
SQLSTATE=42893

db2 =drop function DEDEHAAN.SQL070 515104729271;
SQL0204N "DEDEHAAN.SQL07 0515104729271" is an undefined name.
SQLSTATE=42704

Any clue? btw, I am using db2 :

How about this?
db2drop specific function DEDEHAAN.SQL070 515104729271;

[uwcssa]

I guess my problem is more serious than i once thought. I have
tried all possible means as you guys suggested, so here are them all:

db2 =select funcschema, funcname, specificname from syscat.function s
where funcschema='DED EHAAN'

FUNCSCHEMA FUNCNAME SPECIFICNAME
-------------------------------------------------------------------------------------
DEDEHAAN SUMSALARIES SQL070515104729 271
DEDEHAAN DEPTSALARIESF SQL070515141344 272

2 record(s) selected.
db2=drop function DEDEHAAN.sumsal aries
SQL0658N The object "DEDEHAAN.SUMSA LARIES" cannot be explicitly
dropped.
SQLSTATE=42917

db2 =drop specific function "DEDEHAAN"."SQL 070515104729271 "
SQL0658N The object "DEDEHAAN.SUMSA LARIES" cannot be explicitly
dropped.
SQLSTATE=42917

It occurs me to see the dependency, so I tried below:

db2 =select * from sysibm.sysdepen dencies

BNAME BSCHEMA BTYPE DNAME DSCHEMA TABAUTH
DTYPE
-----------------------------------------------------------------------------------------------------------
------- -----
P3650010 DB2_INST K DRPSCHEMA DB2_INST
0 L
EMPLOYEE DEDEHAAN T SQL070515104729 271 DEDEHAAN 32 F
EMPLOYEE DEDEHAAN T SQL070515141344 272 DEDEHAAN 32 F
P1344330 DEDEHAAN K SQL070515141344 330 DEDEHAAN 0
L
P1345740 DEDEHAAN K SQL070515141345 750 DEDEHAAN 0
L

5 record(s) selected.

But this does not tell me what to do

I also tried :

==drop specific function SQL070515104729 271 restrict
(syntax error)

SO... I have no clue what to do next...
Thanks for your help again....