cancel
Showing results for 
Search instead for 
Did you mean: 

SAP IQ:Migrating objects from one schema to other

SybDBA
Participant

Hi All,

Can we change/alter owner of the views and procedures in SAP IQ, like tables in SAP IQ.

As we have already moved tables to new schema from old one. We wanted to remove old schema, due to certain requirement.

But due to underlying procedures and views we are unable to drop schema.

Regards

Accepted Solutions (1)

Accepted Solutions (1)

markmumy
Advisor
Advisor

As far as I know, no you cannot change the owner of views and procedures. I think this mostly has to do with the code that is encapsulated inside each of those and the underlying issues that could be caused by just changing the name. What if the new owner didn't have the same permissions to see the tables, views, or procedures that the original owner did? What if the original view or procedure didn't include the owner for all objects? Or worse, what if it did? Now you've got code that will likely not run.

Unless you've used the "SET HIDDEN" option, you can get the underlying code (or use something like PowerDesigner) and simply recreate the objects with the new owner. Not as easy as just renaming the user, but certainly more stable and a guaranteed way to make sure that all ownership and permissions are set proper on the objects.

Mark

Answers (1)

Answers (1)

SybDBA
Participant
0 Kudos

Completely agree with your answer Mark !! Thanks so much !!

Is there any way to extract the code from Command Line(I am not interested in PowerDesigner), like iqunload or something like that, as the count is more of views and procedures, hence we can't go with PD approach. Though it is clean.

Like we can create an small script to grab the DDLs of views and procs ?

Regards