467,199 Members | 990 Online
Bytes | Developer Community
Ask Question

Home New Posts Topics Members FAQ

Post your question to a community of 467,199 developers. It's quick & easy.

SYSCAT.ROUTINES VALID column

I have a SQL stored proc in DB2 8.1.5 (linux)
This SQL proc depends on a table in the database. When I drop this
table from the database and try to execute the stored proc - I
correctly get the error message :Error: [IBM][CLI Driver][DB2/LINUX]
SQL0727N
However when I check SYSCAT.ROUTINES table - the VALID field for this
database is still marked Y. Why is this so? In what case will this be
marked invalid?
Nov 12 '05 #1
  • viewed: 4326
Share:
2 Replies
"Amit" <aa***@hotmail.com> wrote in message
news:85*************************@posting.google.co m...
I have a SQL stored proc in DB2 8.1.5 (linux)
This SQL proc depends on a table in the database. When I drop this
table from the database and try to execute the stored proc - I
correctly get the error message :Error: [IBM][CLI Driver][DB2/LINUX]
SQL0727N
However when I check SYSCAT.ROUTINES table - the VALID field for this
database is still marked Y. Why is this so? In what case will this be
marked invalid?


Do you mean "the VALID field for this procedure is still marked Y" You said
database)?

What action-type message did you get with the SQL0727N?
Nov 12 '05 #2
> Do you mean "the VALID field for this procedure is still marked Y" You said
database)?
Yes

What action-type message did you get with the SQL0727N?

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 "SALARY".
SQLSTATE=56098

The way to reproduce this on the db2 SAMPLE database is:
1. Create BUMP_SALARY sql proc from db2 samples.
2. Drop /rename STAFF table (which BUMP_SALARY references
3. Try to execute the proc.
One workaround I tried was to check if the package for the stored proc
was valid using the following query:
SELECT A.ROUTINESCHEMA, A.ROUTINENAME, B.VALID FROM SYSCAT.ROUTINES A,
SYSCAT.PACKAGES B WHERE SUBSTR(A.IMPLEMENTATION, 1, 8)= B.PKGNAME AND
A.ROUTINESCHEMA=B.PKGSCHEMA
Nov 12 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

2 posts views Thread by Stephen Costanzo | last post: by
1 post views Thread by James Rosewell | last post: by
2 posts views Thread by Lennart | last post: by
1 post views Thread by Plamen Ratchev | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.