473,320 Members | 1,987 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

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
2 4765
"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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

4
by: alederer | last post by:
Hallo! I need to convert the values of a varchar (..) for bit data column to a string which can be stored in a normal varchar column (with additional information). Is there a function in db2...
2
by: Stephen Costanzo | last post by:
I have a control table that lists the columns to pull from the associated data table. If there is a column name in the control table that doesn't exist in the data table, it generates a...
1
by: James Rosewell | last post by:
I'm getting an error in the following scenario. - A GridView control is bound to an SQLDataSource. - The user selects a column for sorting. - Some time later in code the column used for sorting...
5
by: Veeru71 | last post by:
Given a table with an identity column (GENERATED BY DEFAULT AS IDENTITY), is there any way to get the last generated value by DB2 for the identity column? I can't use identity_val_local() as...
3
by: mrwillj49 | last post by:
I want to do a sql query in MS Access kind of like as follows... select iif( a.name is null, b.name, a.name ) as my_name from a,b order by my_name asc; (The from clause is actually a bunch of...
10
by: Lennart | last post by:
I see a bunch of packages where valid <'Y'. What I cant figure out is how to relate the package to a procedure, function or whatever. Does anyone have a reference to share on the relationship...
2
by: Lennart | last post by:
On Jun 27, 10:31 am, Lennart <Erik.Lennart.Jons...@gmail.comwrote: Hmm, on second thought. Looking in: SQL Reference Volume 1, Version 8. (havent checked 9.5 yet) in the comment on...
1
by: Plamen Ratchev | last post by:
You cannot use a column alias in the definition of another column. The solution is as you found to repeat the expression. Alternative solution is to define the first column in one CTE (or derived...
1
by: protoclown | last post by:
DB2 on AIX. Syscat.triggers have a valid column, and apparently 'N' means the trigger is not valid. How does one set this column to 'Y' or 'N', and if you can set it to 'N', will the trigger fire? I...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.