Strange problem

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Gregor Kovač - 20 Jun 2006 07:42 GMT

Hi!

I have a procedure that is compiled in the database and I can also recompile
it without any problems, but when I call it I get this strange error:
[IBM][CLI Driver][DB2/NT] SQL0727N An error occurred during implicit system
action type "1". Information returned for the error includes
SQLCODE "-206", SQLSTATE "42703" and message tokens "ARTI_ID".
SQLSTATE=56098

As I read the manuals I know that SQLCODE 206 means that ARTI_ID is not
valid in the context where it is used. But If this would be the case then
the procedure would not compile, right ?

Any suggestions ?

Best regards,
Kovi

Signature

-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-

| Gregor Kovac | Gregor.Kovac@mikropis.si |

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

| In A World Without Fences Who Needs Gates? |
| Experience Linux. |

-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-

Reply to this Message

Mark A - 20 Jun 2006 07:45 GMT

> Hi!
>
[quoted text clipped - 15 lines]
> Best regards,
> Kovi

It could be an SQL statement that is dynamically prepared in the SP, which
cannot be validated at procedure create time.

It would help if you could post the procedure code, and indicate the DB2
level and OS you are using (type db2level from the command line).

Reply to this Message

Gregor Kovač - 20 Jun 2006 07:49 GMT

>> Hi!
>>
[quoted text clipped - 22 lines]
> It would help if you could post the procedure code, and indicate the DB2
> level and OS you are using (type db2level from the command line).

There is NO dynamic SQL that is prepared in the procedure.
db2level gives:
DB21085I Instance "DB2" uses "32" bits and DB2 code release "SQL08024" with
level identifier "03050106".
Informational tokens are "DB2 v8.1.11.973", "s060120", "WR21365", and FixPak
"11".
Product is installed at "C:\PROGRA~1\IBM\SQLLIB".

Signature

-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-

| Gregor Kovac | Gregor.Kovac@mikropis.si |

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

| In A World Without Fences Who Needs Gates? |
| Experience Linux. |

-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-

Reply to this Message

Mark A - 20 Jun 2006 07:55 GMT

> There is NO dynamic SQL that is prepared in the procedure.
> db2level gives:
[quoted text clipped - 5 lines]
> "11".
> Product is installed at "C:\PROGRA~1\IBM\SQLLIB".

OK, but it would help to see the SP source code.

Reply to this Message

Gregor Kovač - 20 Jun 2006 08:06 GMT

>> There is NO dynamic SQL that is prepared in the procedure.
>> db2level gives:
[quoted text clipped - 7 lines]
>
> OK, but it would help to see the SP source code.

Here it is and is quite large.

Signature

-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-

| Gregor Kovac | Gregor.Kovac@mikropis.si |

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

| In A World Without Fences Who Needs Gates? |
| Experience Linux. |

-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-

Reply to this Message

Mark A - 20 Jun 2006 08:40 GMT

> Here it is and is quite large.

I don't what what the -206 is, but what the heck is this:

DECLARE XDELETE_UPDATE_SQL VARCHAR(10000);

I don't see that variable referenced anyplace else in the SP.

Reply to this Message

Gregor Kovač - 20 Jun 2006 08:47 GMT

>> Here it is and is quite large.
>
[quoted text clipped - 3 lines]
>
> I don't see that variable referenced anyplace else in the SP.

hehe.. probably this remained here over the evolution of the SP. Ignore
it .:)

Signature

-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-

| Gregor Kovac | Gregor.Kovac@mikropis.si |

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

| In A World Without Fences Who Needs Gates? |
| Experience Linux. |

-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-

Reply to this Message

Serge Rielau - 20 Jun 2006 15:09 GMT

>>> Here it is and is quite large.
>>>
[quoted text clipped - 6 lines]
> hehe.. probably this remained here over the evolution of the SP. Ignore
> it .:)

Kovi,

Send me a repro script. I'll take a look.

Cheers
Serge

Signature

Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

IOD Conference
http://www.ibm.com/software/data/ondemandbusiness/conf2006/

Reply to this Message

mike - 20 Jun 2006 08:08 GMT

DB2 is performing an implicit rebind for some
reason. You say that the procedure lacks
any dynamic SQL, but does it contain any
session tables? Are any of the packages
invalidated by external reasons like
schema-changes (check syscat.packages
for any invalidated packages), or
sql-function changes, trigger-changes etc?

Reply to this Message

Gregor Kovač - 20 Jun 2006 08:17 GMT

> DB2 is performing an implicit rebind for some
> reason. You say that the procedure lacks
[quoted text clipped - 4 lines]
> for any invalidated packages), or
> sql-function changes, trigger-changes etc?

There are no session tables and I'm not sure for other changes.
The thing is that REBIND command fails, but DROP and CREATE of the same
procedure and the same code works. Hmm....

Signature

-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-

| Gregor Kovac | Gregor.Kovac@mikropis.si |

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

| In A World Without Fences Who Needs Gates? |
| Experience Linux. |

-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-

Reply to this Message

mike - 20 Jun 2006 08:25 GMT

Is REOPT active ?

Reply to this Message

Gregor Kovač - 20 Jun 2006 08:30 GMT

> Is REOPT active ?