Prev: db2cos script
Next: SQL4401C The DB2 Administration Server encountered an error during startup
From: uwcssa on 17 May 2007 15:08
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.SQL070515104729271", of type "FUNCTION", which depends
on it. SQLSTATE=42893

Then I tried to do

>drop function sch.SQL070515104729271

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.procedures
union SELECT funcname FROM SYSCAT.functions
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.sysdependencies;

I found:
BSCHEMA BNAME DSCHEMA DNAME
sch tab sch SQL070515104729271


What does this mean to me? since I can not find what the heck is the
object "SQL070515104729271"


Thanks!

From: Lennart on 17 May 2007 15:18
uwcssa(a)gmail.com 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.SQL070515104729271", of type "FUNCTION", which depends
> on it. SQLSTATE=42893
>
> Then I tried to do
>
>>drop function sch.SQL070515104729271
>
> 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.SQL070515104729271

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

select funcname, body from syscat.functions where specificname =
'SQL070515104729271'


/Lennart

From: uwcssa on 17 May 2007 15:39
Thanks for the quick reply!

I found the definition of the function SQL070515104729271 using the
command:
> select funcname, body from syscat.functions where specificname =
> 'SQL070515104729271'
>
And I get:

SUMSALARIES CREATE FUNCTION SUMSALARIES(DEPT 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 SQL070515104729271 ;

I get: SQL0658N The object "sch.SUMSALARIES" 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.jons...(a)gmail.com> wrote:
> uwc...(a)gmail.com 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.SQL070515104729271", of type "FUNCTION", which depends
> > on it. SQLSTATE=42893
>
> > Then I tried to do
>
> >>drop function sch.SQL070515104729271
>
> > 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.SQL070515104729271
>
> You probably want to look at the function before dropping it, you should
> be able to find it with:
>
> select funcname, body from syscat.functions where specificname =
> 'SQL070515104729271'
>
> /Lennart


From: Dave Hughes on 17 May 2007 16:36
uwcssa(a)gmail.com wrote:

> However, as I try to drop this function: using either
>
> > drop specific function SQL070515104729271 ;

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

DROP SPECIFIC FUNCTION SCH.SQL070515104729271;


> 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(CHAR(3));


HTH,

Dave.

--

From: Knut Stolze on 17 May 2007 17:27
uwcssa(a)gmail.com 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.SQL070515104729271", 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"."SQL070515104729271"

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.procedures
> union SELECT funcname FROM SYSCAT.functions
> 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 '%SQL070515104729271'


--
Knut Stolze
DB2 z/OS Utilities Development
IBM Germany
 |  Next  |  Last
Pages: 1 2 3
Prev: db2cos script
Next: SQL4401C The DB2 Administration Server encountered an error during startup