Changing Existing Views and Stored Procedures

If you’re a DBA on a changing system, as most of us are, there are times when you need to change some of the objects (view, stored procedures, etc) in your databases. Since it is not possible just to re-create an object, you need to drop it and create it again. This wouldn’t be so difficult if you have a relatively simple schema. But if you have multiple objects depending on one another, there are some things you need to be aware of.

The way Informix currently is, when you drop one object, all objects that depend on it (views, synonyms, permissions) are consequently dropped. Which is perfectly OK. But not if you dropped it just to create it again the very next second. In that case, you need to be know that all this dependent objects, and all objects depending on those, and so on, recursively are dropped, so you need to create those as well.

Here is a little example. Let’s say there is a table t1 with many rows in it. User can work only with reduced set of data, so reduction views are used to limit the data each user can see. There may be some common criteria to reduce the data, so there is a main reduction view (v1) and several other views that reduce the data in several ways for different kinds of users (v1a, v1b, …). Private synonyms are created for each user and a view he is using, and permissions are granted as well. The schema would look something like this:

create table t1(a int, b int, ...);
create view v1 as select * from t1 where...;
create view v1a as select * from v1 where exists (..some sub-query..);
create view v1b as select * from v1 where exists (..other sub-query..);
create private synonym angie.t1 for v1a;
create private synonym bob.t1 for v1b;
create private synonym john.t1 for v1a;
...more private synonyms for other users...
grant select on v1a to angie;
grant select on v1b to bob;
grant select on v1a to john;
...more permissions for other users...

And now, if there is a change in v1 schema, and you need to do drop-create on it, all of these objects below v1 will be missing from your database after v1 drop. You’ll get no warning for that, so this is just one of the things you have to know.

Until this problem is solved, you need to know what will get dropped so you could create it. Here are some helpful queries. Of course, all of those will only work before you drop the initial object.

To find out what you need to recreate when you drop a view named v1:

select tabname from systables where tabid in 
( select dtabid from sysdepend
   start with btabid = (select tabid from systables where tabname = 'v1')
 connect by prior dtabid = btabid);

If you don’t have the code for your views, you can extract it from the database. Example for v1:

select viewtext from sysviews
 where tabid = (select tabid from systables where tabname = 'v1') 
 order by seqno;

Which private and public synonyms exist for a table or view (only first level synonyms; if you have multiple level synonyms, write a hierarchical query on syssyntable):

select 'create private synonym ' || trim(t2.owner) || '.' || trim(t2.tabname)
      || ' for ' || trim(t1.tabname) || ';'
 from systables t1, systables t2, syssyntable syn
 where t1.tabid = syn.btabid and t2.tabid = syn.tabid
 and t2.tabtype = 'P'
 and t1.tabname = 'v1';

select 'create public synonym ' || trim(t2.tabname) || ' for ' || trim(t1.tabname) || ';'
 from systables t1, systables t2, syssyntable syn
 where t1.tabid = syn.btabid and t2.tabid = syn.tabid
 and t2.tabtype = 'S'
 and t1.tabname = 'v1';

Which permissions exist for a table or view:

select * from systabauth where tabid = 
 (select tabid from systables where tabname = 'v1');

Which permissions exist for a procedure or function:

select 'grant execute on ' || procname || ' to ' || trim(grantee) || ';'
 from sysprocedures, sysprocauth 
 where sysprocedures.procid = sysprocauth.procid
 and procname = 'my_proc';

Instead of writing all these queries and digging through a code that really shouldn’t be taken into consideration while doing some schema update, we really need some kind of possibility to recreate an existing object without dropping dependent objects, like an  ALTER VIEW (and similar for stored procedures, ALTER PROCEDURE) SQL statement.

There is a RFE posted for this here, so feel free to vote.

Advertisements

, , , , , ,

  1. #1 by Pedro Barriga on June 13, 2014 - 20:36

    Thanks a lot for the info and the querys 🙂

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: