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!

[Ian]

uw****@gmail.co m 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.FUNCTION S 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.co m 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.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

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

/Lennart

[...]

[Lennart]

Lennart wrote:
[...]

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

and deptsalaries as well

/Lennart

[...]

[uwcssa]

Here is the function definition:
CREATE FUNCTION DEPTSALARIESF(D EPT CHAR(3))
RETURNS TABLE(SALARY DECIMAL(9,2))
LANGUAGE SQL
RETURN
SELECT salary
FROM employee
WHERE workdept = dept
CREATE FUNCTION SUMSALARIES(DEP T 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.employ ee;
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.sumsal aries? If its an sql function
you can find it in syscat.function s (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