473,902 Members | 5,194 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

can not drop table but can not find dependence either

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!

May 17 '07 #1
15 10847
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

May 17 '07 #2
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 ons...@gmail.co mwrote:
uwc...@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

May 17 '07 #3
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.

--

May 17 '07 #4
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
May 17 '07 #5
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".
May 18 '07 #6
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
May 18 '07 #7
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 ons...@gmail.co 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

May 18 '07 #8
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;

May 19 '07 #9
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....

May 23 '07 #10

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
1448
by: Leslie | last post by:
I have 2 dlls and they reference each other, thus causing the circular dependence error. I have read about other cases and people say use Interfaces to solve the problem with the shared methods. My case is getting the error, not because methods need shared, but because forms need shared. Example: A.DLL has form TestA1, TestA2 B.DLL has form TestB TestA1 navigates to display TestB, thus A.DLL needs to reference B.DLL so the form will...
5
3026
by: simon_s_li | last post by:
Hi, I have 5 fields in line where I need to drag and drop the text from one field to another field and then all the fields need to re-order themselves. So for instance if I drag the text in field 1 to field 3, then field 2 text and field 3 move to field 1 and field 2. I add the new order of text into an array so when the onDragEnd event
10
26160
by: BuddhaBuddy | last post by:
Platform is DB2/NT 7.2.9 The table was created like this: CREATE TABLE MYTEST ( MYTESTOID bigint not null primary key, FK_OTHEROID bigint not null references other, FK_ANOTHEROID bigint not null references another, FK_LASTLYOID bigint not null references lastly, unique (FK_OTHEROID,FK_ANOTHEROID))
2
2482
by: tpaulson | last post by:
I have a couple of DIV's that I hide/display based on radio buttons. On the DIV's, I have multiple drop down boxes. The source shows that they are populated, but I can't make them drop down. Only click on the Unplatted Radio button, the rest isn't functional, yet. Any ideas how to make these drop down in FF. This works in IE. Here is the code: <HTML> <HEAD> <TITLE>Door County Register of Deeds - Tract Inquiry</TITLE>
2
3345
by: Timbo | last post by:
Hi there, I’m not used to working in VB and I think this situation calls for excactly that. I use Access 97 SR-2. My first table is a table containing all the Tickets I got. The field ”Ticket” is simply a ticket-number. I’ve made a form in which I am to choose an existing Ticket and write a description for it pluss other things. The description to these Tickets are stored in another table called ”LogBook”. I then made a query on all of...
3
6692
by: Rahul B | last post by:
Hi, I have a user UCLDEV1 which is a part of staff and a group(db2schemagrp1) to which i have not given any permissions. The authorizations of that user are shown as db2 =get authorizations Administrative Authorizations for Current User
5
13835
by: Romulo NF | last post by:
Greetings, I´m back here to show the new version of the drag & drop table columns (original script ). I´ve found some issues with the old script, specially when trying to use 2 tables with drag&drop on the same page (which was not possible). Now i´ve a new concept of the script, more object oriented. I´ve also commented the whole code so you guys can easier understand it engine. What exactly we need when trying to make a column drag &...
4
12939
by: Joseph | last post by:
Can i drop a cloumn from a table in DB2 for Z/OS?
10
49632
by: Dean | last post by:
My client has a db I am working that uses temp tables. During an update procedure, I had the code If fTableExists(tempTblName) = True Then DoCmd.DeleteObject acTable, tempTblName Then I thought of using: If fTableExists(tempTblName) = True Then CurrentDb.Execute "DROP TABLE " & tempTblName Is there an advantage to either? What really be cool is DROP TABLE deleted the table and I didn't have to compact the db so much.
0
9845
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
11279
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
10872
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
10981
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
10499
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
9675
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
0
6085
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
4725
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
4307
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.