DB2 – Validation of Views with DEFERRED Auto_Reval

db2

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).

Best Answer

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.

Related Solutions

Db2 – the locking behavior for Updatable/Insertable Views

The short answer is that it depends on the isolation level of your connection and/or statement being run.

If you are taking the default isolation level (which is usually Cursor Stability - CS) in DB2, then I would assume it only puts a lock on the actual rows being modified (as an update lock) and it puts a read lock on the row currently being held by the cursor. All other rows are not technically locked in any sort of way.

Now if you go up higher into Read Stability (RS) or Repeatable Read (RR), then you start to get into where it will lock more rows in the result set (and even the whole table in a join case)....

You may wish to check out the Isolation Level settings in the DB2 Information Center, as this will give you a better picture of how it will act.

Also note, even though there is a default isolation level for the connection, this can be overridden in the session, as well as at the statement level, including the underlying statement that builds the view. So if the default is CS, but the view was built with a SELECT that uses RR, then you are using RR and probably locking on all rows in both the result set and all rows utilized by the engine to create the result set. I'm guessing this is not likely (or at least hopefully someone didn't write the view that way....) and you are using CS, in which what I explained at the beginning is more likely how it behaves.

DB2 9.7 LUW – How to SET SESSION_USER when I am DBADM,SECADM

Ok, I think I actually figured out how to handle this. I needed to grant the ability for my DBADM,SECADM to transfer to any ID through the group not the specific ID. In this way I get around the SECADM restrictions.

db2 grant setsessionuser on public to group DB2ADMNS

Since my ID db2admin is in this group, it can now switch to any ID (the public part). If I only wanted to be able to switch to that ID, I could have used:

db2 grant setsessionuser on user bobabob97 to group DB2ADMNS

So now I can perform

db2 set session_user=bobabob97

and it works.

To switch back I only need to run

db2 set session_user=system_user

Best Answer

Related Solutions

Db2 – the locking behavior for Updatable/Insertable Views

DB2 9.7 LUW – How to SET SESSION_USER when I am DBADM,SECADM

Related Question