Can't drop view

danieles · #1 (**permalink**) 03-09-10, 09:57

Hi to all!
I have installed DB2 9.7 FP1 on AIX OS. I have activate ORACLE COMPATIBILITY
If i try drop view DB2 give me this response:

DB2 SQL Error: SQLCODE=-204, SQLSTATE=42704, SQLERRMC=HCSUITE.SQL100308144713000, DRIVER=3.50.152
Message: "HCSUITE.SQL100308144713000" is an undefined name.. SQLCODE=-204, SQLSTATE=42704, DRIVER=3.50.152

but the "HCSUITE.SQL100308144713000" i haven't create and i can not drop it.

Can you help me please?

Thanks.
D.

n_i · #2 (**permalink**) 03-09-10, 10:51

Why do you think it's a view?

Cougar8000 · #3 (**permalink**) 03-09-10, 10:55

What is the problem? It tells you that object you are trying to drop is not there in plain English.

BTW. I have never seen that naming convention used in the views. That resembles a PK that was auto created.

Run the following and post output.

db2 list tables for all | grep -i SQL100308144713000

I bet it will come back empty.

db2 "select indname, uniquerule from syscat.indexes where indname = 'SQL100308144713000'"

danieles · #4 (**permalink**) 03-10-10, 05:49

this command

Quote:

Originally Posted by Cougar8000

db2 list tables for all | grep -i SQL100308144713000

I bet it will come back empty.

return empty

Quote:

Originally Posted by Cougar8000

db2 "select indname, uniquerule from syscat.indexes where indname = 'SQL100308144713000'"

this statement retunt empty.
When i tried to recreate view "create or replace view ...."

DB2 give me this error:

[Error] Script lines: 1-20 -------------------------
DB2 SQL Error: SQLCODE=-901, SQLSTATE=58004, SQLERRMC=unable to retrieve function pd, DRIVER=3.50.152
Message: The SQL statement failed because of a non-severe system error. Subsequent SQL statements can be processed. (Reason "unable to retrieve function pd".). SQLCODE=-901, SQLSTATE=58004, DRIVER=3.50.152

I tried to catalog and found that the only point in citing that function is in the view syscat.ROUTINEDEP...

have you any idea?

n_i · #5 (**permalink**) 03-10-10, 08:11

Apparently, in your view definition you attempt to use a function named "pd", which is not implemented properly in the database.

Lenny77 · #6 (**permalink**) 03-10-10, 11:43

Quote:

Originally Posted by danieles

Hi to all!
I have installed DB2 9.7 FP1 on AIX OS. I have activate ORACLE COMPATIBILITY
If i try drop view DB2 give me this response:

DB2 SQL Error: SQLCODE=-204, SQLSTATE=42704, SQLERRMC=HCSUITE.SQL100308144713000, DRIVER=3.50.152
Message: "HCSUITE.SQL100308144713000" is an undefined name.. SQLCODE=-204, SQLSTATE=42704, DRIVER=3.50.152

but the "HCSUITE.SQL100308144713000" i haven't create and i can not drop it.

Can you help me please?

Thanks.
D.

Nomastei ! Show your query, before show the error. Could be funny.

Lenny

Cougar8000 · #7 (**permalink**) 03-10-10, 14:57

Show us the DDL that you are trying to run.

Quote:

return empty

If I give you ZERO dollars. How much bread can you buy with it?

db2girl · #8 (**permalink**) 03-10-10, 15:47

Just a guess... maybe this has something to do with auto_reval in v9.7:

IBM DB2 9.7 for Linux, UNIX and Windows Information Center

DB2 9.7: Run Oracle applications on DB2 9.7 for Linux, Unix, and Windows

db2girl · #9 (**permalink**) 03-10-10, 15:50

Quote:

Originally Posted by Cougar8000

If I give you ZERO dollars. How much bread can you buy with it?

If depends on where you're.

db2girl · #10 (**permalink**) 03-10-10, 15:54

Quote:

Originally Posted by danieles

DB2 give me this error:

[Error] Script lines: 1-20 -------------------------
DB2 SQL Error: SQLCODE=-901, SQLSTATE=58004, SQLERRMC=unable to retrieve function pd, DRIVER=3.50.152
Message: The SQL statement failed because of a non-severe system error. Subsequent SQL statements can be processed. (Reason "unable to retrieve function pd".). SQLCODE=-901, SQLSTATE=58004, DRIVER=3.50.152

-901 could be a db2 bug