By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,017 Members | 951 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,017 IT Pros & Developers. It's quick & easy.

SYSCAT.ROUTINES VALID column

P: n/a
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
Share this Question
Share on Google+
2 Replies


P: n/a
"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

P: n/a
> 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.