Prev: db2cos script
Next: SQL4401C The DB2 Administration Server encountered an error during startup
From: %NAME% on 18 May 2007 13:08
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.ROUTINES" is an undefined name. SQLSTATE=42704


I am sure my function is not overloaded, and I tried:

db2> set schema dedehaan;
db2> drop table employee;
SQL0478N The object type "TABLE" cannot be dropped because there is
an object
"DEDEHAAN.SQL070515104729271", of type "FUNCTION", which depends on
it.
SQLSTATE=42893

db2 => drop function DEDEHAAN.SQL070515104729271;
SQL0204N "DEDEHAAN.SQL070515104729271" 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".


From: Serge Rielau on 18 May 2007 13:40
> 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.FUNCTIONS
And ROUTINENAME with FUNCNAME in your queries.

Cheers
Serge



--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab
From: Lennart on 18 May 2007 14:31
uwcssa(a)gmail.com wrote:
> 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!
>

either do:

drop specific function SQL070515104729271 restrict

or

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

then:

drop function <funcschema>.<funcname> restrict

also beware Knuts info regarding ucase

/Lennart


>
>
> 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: Tonkuma on 19 May 2007 06:17
On May 19, 2:08 am, %NAME% <huaxinzh...(a)gmail.com> wrote:
> db2> set schema dedehaan;
> db2> drop table employee;
> SQL0478N The object type "TABLE" cannot be dropped because there is
> an object
> "DEDEHAAN.SQL070515104729271", of type "FUNCTION", which depends on
> it.
> SQLSTATE=42893
>
> db2 => drop function DEDEHAAN.SQL070515104729271;
> SQL0204N "DEDEHAAN.SQL070515104729271" is an undefined name.
> SQLSTATE=42704
>
> Any clue? btw, I am using db2 :
How about this?
db2> drop specific function DEDEHAAN.SQL070515104729271;

From: uwcssa on 23 May 2007 16:53
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.functions
where funcschema='DEDEHAAN'

FUNCSCHEMA FUNCNAME SPECIFICNAME
-------------------------------------------------------------------------------------
DEDEHAAN SUMSALARIES SQL070515104729271
DEDEHAAN DEPTSALARIESF SQL070515141344272

2 record(s) selected.


db2=> drop function DEDEHAAN.sumsalaries
SQL0658N The object "DEDEHAAN.SUMSALARIES" cannot be explicitly
dropped.
SQLSTATE=42917

db2 => drop specific function "DEDEHAAN"."SQL070515104729271"
SQL0658N The object "DEDEHAAN.SUMSALARIES" cannot be explicitly
dropped.
SQLSTATE=42917

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

db2 => select * from sysibm.sysdependencies

BNAME BSCHEMA BTYPE DNAME DSCHEMA TABAUTH
DTYPE
-----------------------------------------------------------------------------------------------------------
------- -----
P3650010 DB2_INST K DRPSCHEMA DB2_INST
0 L
EMPLOYEE DEDEHAAN T SQL070515104729271 DEDEHAAN 32 F
EMPLOYEE DEDEHAAN T SQL070515141344272 DEDEHAAN 32 F
P1344330 DEDEHAAN K SQL070515141344330 DEDEHAAN 0
L
P1345740 DEDEHAAN K SQL070515141345750 DEDEHAAN 0
L

5 record(s) selected.

But this does not tell me what to do



I also tried :

==> drop specific function SQL070515104729271 restrict
(syntax error)

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

First  |  Prev  |  Next  |  Last
Pages: 1 2 3
Prev: db2cos script
Next: SQL4401C The DB2 Administration Server encountered an error during startup