The DBA pointed out that a stored procedure "A" "Valid" status was set to "No".
- So I compiled the DB2 file.
- No Errors
- Stored Procedure "A" "Valid" status went to "Yes"
- Store Procedure "D" "Valid" status went to "No"
- recompiled the DB2 file again.
- No Errors
- all the Stored Procedures "Valid" status are now "Yes"
We are wondering how is this even possible since we have never seen this behavior before.
Also is there some option that can be triggered to tell us that a Stored Procedure, Function, etc went invalid on a compile?
Also when a Stored Procedure goes invalid for some unknown reason, should the "Last alter time" reflect that change in status? If not, then what do we have to go on to tell us when it when invalid?
Another issue. When adding a new field to a table, sometimes, triggers and views become invalidated as well. When I run a query to update a table with a new column, if something gets invalidated, I should really be informed of us via something in the messages box.
We would need more information to assist you in determining why you are having issues with your invalidated procedures. Are you stating that those procedures only get invalidated when using Toad DB2? Are you running any other tools or applications that may be causing those to be invalidated? The DDL for all of your procs and the files\scripts you run when you 'compile' or 'recompile' would be needed. If Toad is involved, I would suggest opening up a support ticket at support.quest.com so all pertinent information can be captured. Re-validating objects can be a complex task and DB2 LUW does have a database configuration parameter that controls if and when objects are re-validated. Perhaps you can set that parm to assist you. You can read more about that at: https://www.ibm.com/support/knowledgecenter/en/SSEPGG_11.1.0/com.ibm.db2.luw.admin.config.doc/doc/r0053784.html Toad for DB2 LUW displays invalid objects. Once you select the database, select the 'Invalid Objects' tab on the right hand-side to see such objects as shown below:
Toad also displays the 'Invalidate Time' which should be helpful to you and you should probably reference that instead of the procedure's 'last alter time'. You can also query SYSCAT.INVALIDOBJECTS on your own too.
As there are many ways objects can be invalidated (inside and outside of Toad DB2) and re-validating those is related to the auto_reval database parameter Toad does not display a message box when altering an object. I suggest you use the 'Invalid Objects' tab as shown above.
You can also right-click on an object in the 'Invalid Objects' grid and select Revalidate:
...to have Toad call the ADMIN_REVALIDATE_DB_OBJECTS DB2 stored procedure for you...
I only work in the TOAD environment.
I am a Programmer/Analyst and therefore don't have the DBA version of TOAD.
I do have the 6.5.0.125 (64 bit) Registered Version
And that is one of the problems. We the programmers created/modify the DB2 file and compile it in the Developer Database. Once we are satisfied with the results, we submit the DB2 file to the DBAs to be compiled in 4 other higher databases.
Now if the non DBA version allows me to see what you have shown above, then I really don't know how to access it....
Even if you have a 'Base Edition' Toad DB2 LUW license - with the Database Explorer open - you can select the 'database' tab on the left side and then select the 'Invalid Objects' tab on the right:
What type of Toad DB2 license do you have?
Well with that last pic you provided, an UBER lightbulb went off in my head. I've never clicked on the "Database" tab in the 4 1/2 years I've worked in TOAD.
My experience has been limited to:
Functions
Procedures
Tables
Triggers
Views
Problem solved....called the 5 other Programmer/Analysts over to my Desk to do a Show & Tell.
Now I wonder why any one of the 2 DBAs could not show me this....
As for Version:
