0

According to http://www-01.ibm.com/support/knowledgecenter/SSEPGG_10.5.0/com.ibm.db2.luw.admin.dbobj.doc/doc/r0054951.html?lang=en it should be possible to create stored procedures and views referencing not existing tables if the setting AUTO_REVAL is set to DEFERRED.

On my system however the following commands (creating a view referencing an invalid table) still result in an error:

  • db2 get database configuration | grep REVAL

Auto-Revalidation (AUTO_REVAL) = DEFERRED

  • db2 "create view v1 as select * from not_existing_table"

DB21034E The command was processed as an SQL statement because it was not a valid Command Line Processor command. During SQL processing it returned: SQL0204N "DB2ADMIN.NOT_EXISTING_TABLE" is an undefined name. SQLSTATE=42704

DB2 does not create the view, also not as an invalid object.

Am I missing anything? Are there other options I need to verify? I'm using DB2 LUW 10.5 (fixpack 4).

1

I think the DEFERRED setting only affects CREATE OR REPLACE statements. If you want simple CREATEs to behave in this manner, set AUTO_REVAL to DEFERRED_FORCE.

  • Thanks for your reply: I justed tested it and after setting the setting AUTO_REVAL to DEFERRED_FORCE I was able to create my view. – Matthias Jan 13 '16 at 12:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.