A user has a DBADM and DataAccess privilege in 9.7 but when it tries to Create or Replace a view, it fails with an error complaining that the user doesn't have the required privilege. Please let me know if the user needs to have any other privilege other than DBADM/DATAACESS in order to create/replace view.

Any help in this regard is much appreciated !!

To create a view, the privileges you hold seems to be sufficient
IBM DB2 9.7 for Linux, UNIX and Windows Information Center

But to replace, there is another condition
If the authid is not the owner, then from a SECADM user, issue TRANSFER OWNERSHIP (lookup docs for syntax) and perform the replace action.
Of course, the other option is to use the current owner to replace the view.

HTH

Sathyaram

Interestingly, I believe that DBADM can drop and re-create the view, even if it is not the owner.

@Sathyaram: Thanks !!

@Marcus: You are right. I followed the same workaround to resolve the issue. Instead of replacing the view, I dropped and recreated it.

We frequently do redirected restores of PROD data into our lower env's like QA. After the restore, some of the DDL using CREATE or REPLACE a new feature in db2 9.7 is failing saying only the owner of the object can invoke CREATE or REPLACE. The instance ID of PROD is still the owner of the objects in the lower env's after the restore, so we are not able to use the CREATE or REPLACE. As part of the redirected restore or through any other process/config can we make the instance ID of the lower lanes as owner of all objects to fix this issue? IF we cant use CREATE or REPLACE, we have to do 3 times more work for DDL with DROP and CREATE as lot of dependencies exist. Please advise.

I understand that we can do a transfer ownership for each and every single object after the restore. But is there any other easier way to accomplish this ??

Blazer789, AFAIK there isn't a short-cut for chaning ownership.

BTW, changing ownership is lot easier than you might imaging.

A view with all object owners info :

OBJECTOWNERS

and create a script to build and execute transfer ownerships using the view.

DB2 Database for Linux, UNIX, and Windows

I hope you already have the process for granting SECADM to a new user. The SECADM issuing this command cannot transfer ownership to itself.

HTH

Sathyaram

Thanks Satya for your kind response. When I say an alternate way to accomplish this, I meant to ask if there is any way the objects in the lowerlane get the ownership of the instance id of the lower lane through an option in restore.

For example:
"A" is the instance user of database "d1". "A" owns all the objects in "d1".
"B" is the instance user of database "d2". "B" owns all the objects in "d2".

"d1" is restored on to "d2". Now "d2" is located on instance "B" but all the objects are owned by user "A". Now when I try to run "create or replace view" on the newly restored database with user B it fails complaining that "B" is not the owner of the object. So I am looking for an option in restore or someother process with which all the objects in the newly restored database would be owned by user "B" after the restore.

@Blazer789 , Yes I did get your point, but unfortunately(AFAIK) changing ownership during a redirected restore is not possible

HTH

Sathyaram

Okay, Thanks !