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.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!

[Lennart]

uw****@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

[uwcssa]

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 <[e-mail]:

[e-mail] 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

[Dave Hughes]

uw****@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.

--

[Knut Stolze]

uw****@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

[%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.ROUTINES" 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.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".

[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.FUNCTIONS
And ROUTINENAME with FUNCNAME in your queries.

Cheers
Serge

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

[Lennart]

uw****@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>.<funcnamerestrict

also beware Knuts info regarding ucase

/Lennart

>

On May 17, 2:18 pm, Lennart <[e-mail]:

>[e-mail] 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

[Tonkuma]

On May 19, 2:08 am, %NAME% <[e-mail]:

db2set schema dedehaan;
db2drop 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?
db2drop specific function DEDEHAAN.SQL070515104729271;

[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.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....

[Ian]

uw****@gmail.com wrote:

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

Out of curiosity, what is the create_time in SYSCAT.FUNCTIONS for these
objects? Is it in the future?

[Serge Rielau]

Do you have distinct types? Are these cast functions?
Or are these really METHODS in a structured type?

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

[Lennart]

uw****@gmail.com wrote:

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

Can you post the code for DEDEHAAN.sumsalaries? If its an sql function
you can find it in syscat.functions (body?)

/Lennart

[...]

[Lennart]

Lennart wrote:
[...]

>
Can you post the code for DEDEHAAN.sumsalaries? If its an sql function
you can find it in syscat.functions (body?)

and deptsalaries as well

/Lennart

[...]

[uwcssa]

Here is the function definition:
CREATE FUNCTION DEPTSALARIESF(DEPT CHAR(3))
RETURNS TABLE(SALARY DECIMAL(9,2))
LANGUAGE SQL
RETURN
SELECT salary
FROM employee
WHERE workdept = dept
CREATE FUNCTION SUMSALARIES(DEPT CHAR(3))
RETURNS DECIMAL(9,2)
LANGUAGE SQL
RETURN
SELECT sum(salary)
FROM employee
WHERE workdept = dept

Here is the definition of the table:
db2 =describe table dedehaan.employee;
Column Type Type
name schema name Length
Scale Nulls
------------------------------ --------- ------------------ --------
----- -----
EMPNO SYSIBM CHARACTER
6 0 No
FIRSTNME SYSIBM VARCHAR
12 0 No
MIDINIT SYSIBM CHARACTER
1 0 No
LASTNAME SYSIBM VARCHAR
15 0 No
WORKDEPT SYSIBM CHARACTER
3 0 Yes
PHONENO SYSIBM CHARACTER
4 0 Yes
HIREDATE SYSIBM DATE
4 0 Yes
JOB SYSIBM CHARACTER
8 0 Yes
EDLEVEL SYSIBM SMALLINT
2 0 No
SEX SYSIBM CHARACTER
1 0 Yes
BIRTHDATE SYSIBM DATE
4 0 Yes
SALARY SYSIBM DECIMAL
9 2 Yes
BONUS SYSIBM DECIMAL
9 2 Yes
COMM SYSIBM DECIMAL
9 2 Yes
I can not drop the table, nor any of the two functions defined on it.
there is no
user-defined data structure in the functions, as i can see....

>

Can you post the code for DEDEHAAN.sumsalaries? If its an sql function
you can find it in syscat.functions (body?)

and deptsalaries as well

/Lennart

[...]- Hide quoted text -

- Show quoted text -

[Serge Rielau]

I think this should be a PMR.
A db2trc should be run to find out what DB2 is upset about.

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab