Dropping procedure - problem

[WB]

Hi,

I'm trying to drop procedure on DB2 UDB v.8.1 fix 4 / AIX 5.1.

SYSIBM.SYSROUTINES contains row with my procedure and points to not existing
package.

When I try to drop my procedure (drop procedure xxx.xxx) I get "SQL0100W No
row was found for FETCH, UPDATE or DELETE; or the result of a query is an
empty table".

When I try to create procedure I get "SQL0454N The signature provided in the
definition for routine xxx.xxx matches the signature of some other routine
that already exists in the schema or for the type."

Procedure and specific name are the same.
Can anyone help? Thanks Wojtek

[Serge Rielau]

Looks like a bug in eth DROP PROCEDURE DDL.
Is the procedure to be dropped very simple?
Can you post it?

Cheers
Serge

--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

[Database Guy]

"WB" <wb*******@taran.com.pl> wrote in message news:<c5**********@nemesis.news.tpi.pl>...

When I try to drop my procedure (drop procedure xxx.xxx) I get "SQL0100W No
row was found for FETCH, UPDATE or DELETE; or the result of a query is an
empty table".

When I try to create procedure I get "SQL0454N The signature provided in the
definition for routine xxx.xxx matches the signature of some other routine
that already exists in the schema or for the type."

Unsure, I've not got an SP-compiler to hand, but think this might be
DB2's unfriendly way of telling you that you have overloaded
procedures. Just a hunch (or faint memory).
DG

[Ian]

WB wrote:

Hi,

I'm trying to drop procedure on DB2 UDB v.8.1 fix 4 / AIX 5.1.

SYSIBM.SYSROUTINES contains row with my procedure and points to not existing
package.

When I try to drop my procedure (drop procedure xxx.xxx) I get "SQL0100W No
row was found for FETCH, UPDATE or DELETE; or the result of a query is an
empty table".

When I try to create procedure I get "SQL0454N The signature provided in the
definition for routine xxx.xxx matches the signature of some other routine
that already exists in the schema or for the type."

Have you tried DROP SPECIFIC PROCEDURE?


-----= Posted via Newsfeeds.Com, Uncensored Usenet News =-----
http://www.newsfeeds.com - The #1 Newsgroup Service in the World!
-----== Over 100,000 Newsgroups - 19 Different Servers! =-----

[WB]

Uzytkownik "Serge Rielau" <sr*****@ca.eye-be-em.com> napisal w wiadomosci
news:c5**********@hanover.torolab.ibm.com...

Looks like a bug in eth DROP PROCEDURE DDL.
Is the procedure to be dropped very simple?
Can you post it?

Hi,

Average compilation (bind) time for this procedure is 30 minutes.
In case of communication failure (or by issuing db2 force application
command) package is not created but procedure is registered in system
tables. There are some entries in db2diag.log like:

2004-04-14-00.07.20.680241 Instance:db2inst1 Node:000
PID:29432(db2agent (MPGKB)) TID:1 Appid:GA0A0A73.E80B.008703173815
PSM - SQL Procedure psm_prep_and_compile Probe:50 Database:MPGKB

DIA0001E An internal error occurred. Report the following error code :
"ZRC=0x8012006D".

PID:29432 TID:1 Node:000 Title: SQLCA
sqlcaid : SQLCA sqlcabc: 136 sqlcode: -1131 sqlerrml: 0
sqlerrmc:
sqlerrp : SQLRRRCP?
sqlerrd : (1) 0x80040003 (2) 0x00000003 (3) 0x00000000
(4) 0x00000000 (5) 0x00000000 (6) 0x00000000
sqlwarn : (1) (2) (3) (4) (5) (6)
(7) (8) (9) (10) (11)
sqlstate: 55032

2004-04-14-00.07.20.758159 Instance:db2inst1 Node:000
PID:29432(db2agent (MPGKB)) TID:1 Appid:GA0A0A73.E80B.008703173815
PSM - SQL Procedure psm_prep_and_compile Probe:0 Database:MPGKB

DIA0001E An internal error occurred. Report the following error code :
"ZRC=0x8012006D".

PID:29432 TID:1 Node:000 Title: SQL procedure prep_and_compile:
2004-04-14-00.07.20.738401 Instance:db2inst1 Node:000
PID:14782(db2fmp (29432)) TID:1 Appid:none
oper system services sqloSetFileAccessMode Probe:100

errno:
0x2FF18890 : 0x00000002 ....

PID:14782 TID:1 Node:000 Title: Path/Filename
/taransys/db2inst1/sqllib/function/routine/sqlproc/MPGKB/DB2TARAN/tmp/P33251
14.bnd

2004-04-14-00.07.21.582432 Instance:db2inst1 Node:000
PID:14782(db2fmp (29432)) TID:1 Appid:none
routine_infrastructure sqlerFmpListener Probe:350

DIA0001E An internal error occurred. Report the following error code :
"ZRC=0xFFFFFB95".

I checked a SYSIBM.SYSROUTINES table:

Select ROUTINENAME, ROUTINESCHEMA, DEFINER, SPECIFICNAME, ROUTINE_ID,
RETURN_TYPE, IMPLEMENTATION from SYSIBM.SYSROUTINES WHERE ROUTINENAME =
'M_DEL_POZ_OBROTY'

and I got:

ROUTINENAME
ROUTINESCHEMA
DEFINER
SPECIFICNAME
ROUTINE_ID
RETURN_TYPE
IMPLEMENTATION
-----------------------------
M_DEL_POZ_OBROTY
DB2TARAN
DBTARAN1
M_DEL_POZ_OBROTY
66706
-2
P3325114!pgsjmp

And when i checked sysibm.packages for P3325114 I got no rows.
There are no files in sqllib/function/routine/sqlproc/MPGKB/DB2TARAN with
similar name.
The procedure is complicated. It contains many select statements and calls
to other procedures.

Any ideas?

Wojtek

[Serge Rielau]

Interesting indeed....
My first gues was that your procedure is pathological in the sense of
not creating a package (because it is so simple).
Obviously that isn't the case.
Digging deeper I learned that a similar PMR is currently being
investigated. Your info sheds some more light on this puppy.
I've two requests:
1. Run a small experiment (I have a suspicion :-):
a) Make sure you run with autocommit OFF (db2 +c)
b) do some DDL or DML (e.g. create table)
c) Create the procedure
d) Force the app while creating the procedure
Is the change done in b) still around?
2. Contact support, get a PMR opened.

Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

[PM \(pm3iinc-nospam\) CGO]

0x8012006D -2146303891 SQLR_CA_BUILT -902 SQLCA has already been built

PM

"WB" <wb*******@taran.com.pl> a écrit dans le message de
news:c5**********@nemesis.news.tpi.pl...

Uzytkownik "Serge Rielau" <sr*****@ca.eye-be-em.com> napisal w wiadomosci
news:c5**********@hanover.torolab.ibm.com...

Looks like a bug in eth DROP PROCEDURE DDL.
Is the procedure to be dropped very simple?
Can you post it?

Hi,

Average compilation (bind) time for this procedure is 30 minutes.
In case of communication failure (or by issuing db2 force application
command) package is not created but procedure is registered in system
tables. There are some entries in db2diag.log like:

2004-04-14-00.07.20.680241 Instance:db2inst1 Node:000
PID:29432(db2agent (MPGKB)) TID:1 Appid:GA0A0A73.E80B.008703173815
PSM - SQL Procedure psm_prep_and_compile Probe:50 Database:MPGKB

DIA0001E An internal error occurred. Report the following error code :
"ZRC=0x8012006D".

PID:29432 TID:1 Node:000 Title: SQLCA
sqlcaid : SQLCA sqlcabc: 136 sqlcode: -1131 sqlerrml: 0
sqlerrmc:
sqlerrp : SQLRRRCP?
sqlerrd : (1) 0x80040003 (2) 0x00000003 (3) 0x00000000
(4) 0x00000000 (5) 0x00000000 (6) 0x00000000
sqlwarn : (1) (2) (3) (4) (5) (6)
(7) (8) (9) (10) (11)
sqlstate: 55032

2004-04-14-00.07.20.758159 Instance:db2inst1 Node:000
PID:29432(db2agent (MPGKB)) TID:1 Appid:GA0A0A73.E80B.008703173815
PSM - SQL Procedure psm_prep_and_compile Probe:0 Database:MPGKB

DIA0001E An internal error occurred. Report the following error code :
"ZRC=0x8012006D".

PID:29432 TID:1 Node:000 Title: SQL procedure prep_and_compile:
2004-04-14-00.07.20.738401 Instance:db2inst1 Node:000
PID:14782(db2fmp (29432)) TID:1 Appid:none
oper system services sqloSetFileAccessMode Probe:100

errno:
0x2FF18890 : 0x00000002 ....

PID:14782 TID:1 Node:000 Title: Path/Filename

/taransys/db2inst1/sqllib/function/routine/sqlproc/MPGKB/DB2TARAN/tmp/P33251 14.bnd

2004-04-14-00.07.21.582432 Instance:db2inst1 Node:000
PID:14782(db2fmp (29432)) TID:1 Appid:none
routine_infrastructure sqlerFmpListener Probe:350

DIA0001E An internal error occurred. Report the following error code :
"ZRC=0xFFFFFB95".

I checked a SYSIBM.SYSROUTINES table:

Select ROUTINENAME, ROUTINESCHEMA, DEFINER, SPECIFICNAME, ROUTINE_ID,
RETURN_TYPE, IMPLEMENTATION from SYSIBM.SYSROUTINES WHERE ROUTINENAME =
'M_DEL_POZ_OBROTY'

and I got:

ROUTINENAME
ROUTINESCHEMA
DEFINER
SPECIFICNAME
ROUTINE_ID
RETURN_TYPE
IMPLEMENTATION
-----------------------------
M_DEL_POZ_OBROTY
DB2TARAN
DBTARAN1
M_DEL_POZ_OBROTY
66706
-2
P3325114!pgsjmp

And when i checked sysibm.packages for P3325114 I got no rows.
There are no files in sqllib/function/routine/sqlproc/MPGKB/DB2TARAN with
similar name.
The procedure is complicated. It contains many select statements and calls
to other procedures.

Any ideas?

Wojtek