Dynamically ReCreate Views
User dropping/recreating tables in Informix 11.70 lost dependent views. Solutions provided: use sysdepend to identify dependent views, then use dbschema with -t and -p options to extract view DDL and permissions. Alternative: use myschema -F option to generate full schema DDL including views and foreign keys. User later asked about viewing permissions on views specifically.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Hi, We are running Informix 11.70. We have some tables that we drop/create via batch job. However we are losing the views on those tables when we drop the tables. Do you have a way to dynamically save the list of all the views on the table as well as save the syntax of the views (with permissions) so that after we recreate the tables we can simply recreate the views? Using sysdepend I see I can save the list of views for these specific tables. Though the actual syntax to recreate the view seems to be in sysviews and can span many rows. Any simple way to get this information into a statement that can be used to recreate the views? Other suggestions? --Dave --047d7bf1987ea143080516713b2d
Hi Dave,
As you have already found the names of the dependent views from sysdepend,
you should just be able to use "dbschema -t <viewname> -p all" to get the
view text and the permissions. You'll also need to make the process
recursive to handle views built on views.
Mike
-----Original Message-----
From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of
Informix DBA
Sent: Tuesday, May 19, 2015 9:48 AM
To: ids@iiug.org
Subject: Dynamically ReCreate Views [35133]
Hi,
We are running Informix 11.70. We have some tables that we drop/create via
batch job. However we are losing the views on those tables when we drop the
tables. Do you have a way to dynamically save the list of all the views on
the table as well as save the syntax of the views (with
permissions) so that after we recreate the tables we can simply recreate the
views?
Using sysdepend I see I can save the list of views for these specific
tables. Though the actual syntax to recreate the view seems to be in
sysviews and can span many rows. Any simple way to get this information into
a statement that can be used to recreate the views? Other suggestions?
--Dave
--047d7bf1987ea143080516713b2d
****************************************************************************
***
Forum Note: Use "Reply" to post a response in the discussion forum.
You can use Arts code. There is also code from dbdiff2.4gl on the in = site that will generate DDL for views. You could leverage that. j. > On May 19, 2015, at 11:47 AM, Informix DBA <in4mixdba@gmail.com> = wrote: >=20 > Hi,=20 >=20 > We are running Informix 11.70. We have some tables that we drop/create = via=20 > batch job. However we are losing the views on those tables when we = drop=20 > the tables. Do you have a way to dynamically save the list of all the=20= > views on the table as well as save the syntax of the views (with=20 > permissions) so that after we recreate the tables we can simply = recreate=20 > the views?=20 >=20 > Using sysdepend I see I can save the list of views for these specific=20= > tables. Though the actual syntax to recreate the view seems to be=20 > in sysviews and can span many rows. Any simple way to get this = information=20 > into a statement that can be used to recreate the views? Other = suggestions?=20 >=20 > --Dave=20 >=20 > --047d7bf1987ea143080516713b2d=20 >=20 >=20 > = **************************************************************************= *****=20 > Forum Note: Use "Reply" to post a response in the discussion forum.=20= >=20
The -F option to myschema will give you what you want. It also prints out
all foreign key constraints that reference the named table(s) since they
also disappear when you drop the referenced table:
$ myschema -d art -t no_privs -FWriting full schema DDL to: stdout
CREATE TABLE "art".no_privs (
one SERIAL(5) NOT NULL
) IN datadbs EXTENT SIZE 16 NEXT SIZE 16 LOCK MODE PAGE;
{
Please review extent sizing and adjust to allow for growth.
}
REVOKE ALL ON "art".no_privs FROM public;CREATE INDEX "art".no_privs_ak1 ON "art".no_privs (
one ASC
) USING btree IN TABLE;
GRANT ALL ON no_privs TO "fred" AS "art";
GRANT UPDATE (one) ON no_privs TO "fred" AS "art";
CREATE VIEW "art".tst_privs (
one)
AS
SELECT
x0.one
FROM
"art".no_privs x0 ;
CREATE VIEW "art".tst_privs2 (
one)
AS
SELECT
x0.one
FROM
"art".no_privs x0 ;
Art
Art S. Kagel, President and Principal Consultant
ASK Database Management
www.askdbmgt.com
Blog: http://informix-myview.blogspot.com/
Disclaimer: Please keep in mind that my own opinions are my own opinions
and do not reflect on the IIUG, nor any other organization with which I am
associated either explicitly, implicitly, or by inference. Neither do
those opinions reflect those of other individuals affiliated with any
entity with which I am affiliated nor those of the entities themselves.
On Tue, May 19, 2015 at 11:47 AM, Informix DBA <in4mixdba@gmail.com> wrote:
> Hi,
>
> We are running Informix 11.70. We have some tables that we drop/create via
> batch job. However we are losing the views on those tables when we drop
> the tables. Do you have a way to dynamically save the list of all the
> views on the table as well as save the syntax of the views (with
> permissions) so that after we recreate the tables we can simply recreate
> the views?
>
> Using sysdepend I see I can save the list of views for these specific
> tables. Though the actual syntax to recreate the view seems to be
> in sysviews and can span many rows. Any simple way to get this information
> into a statement that can be used to recreate the views? Other suggestions?
>
> --Dave
>
> --047d7bf1987ea143080516713b2d
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--047d7bd76aeae52bbb0516737bf0
Thank you all for your suggestions. Btw is there a way to get myschema to
print permissions on views?
On Tue, May 19, 2015 at 2:29 PM, Art Kagel <art.kagel@gmail.com> wrote:
> The -F option to myschema will give you what you want. It also prints out
> all foreign key constraints that reference the named table(s) since they
> also disappear when you drop the referenced table:
>
> $ myschema -d art -t no_privs -F> Writing full schema DDL to: stdout
>
> CREATE TABLE "art".no_privs (
>
> one SERIAL(5) NOT NULL
> ) IN datadbs EXTENT SIZE 16 NEXT SIZE 16 LOCK MODE PAGE;
>
> {
>
> Please review extent sizing and adjust to allow for growth.
> }
>
> REVOKE ALL ON "art".no_privs FROM public;> CREATE INDEX "art".no_privs_ak1 ON "art".no_privs (
>
> one ASC
> ) USING btree IN TABLE;
>
> GRANT ALL ON no_privs TO "fred" AS "art";
> GRANT UPDATE (one) ON no_privs TO "fred" AS "art";>
> CREATE VIEW "art".tst_privs (
>
> one)
> AS
> SELECT
>
> x0.one
>
> FROM
>
> "art".no_privs x0 ;
>
> CREATE VIEW "art".tst_privs2 (
>
> one)
> AS
> SELECT
>
> x0.one
>
> FROM
>
> "art".no_privs x0 ;
>
> Art
>
> Art S. Kagel, President and Principal Consultant
> ASK Database Management
> www.askdbmgt.com
>
> Blog: http://informix-myview.blogspot.com/
>
> Disclaimer: Please keep in mind that my own opinions are my own opinions
> and do not reflect on the IIUG, nor any other organization with which I am
> associated either explicitly, implicitly, or by inference. Neither do
> those opinions reflect those of other individuals affiliated with any
> entity with which I am affiliated nor those of the entities themselves.
>
> On Tue, May 19, 2015 at 11:47 AM, Informix DBA <in4mixdba@gmail.com>
> wrote:
>
> > Hi,
> >
> > We are running Informix 11.70. We have some tables that we drop/create
> via
> > batch job. However we are losing the views on those tables when we drop
> > the tables. Do you have a way to dynamically save the list of all the
> > views on the table as well as save the syntax of the views (with
> > permissions) so that after we recreate the tables we can simply recreate
> > the views?
> >
> > Using sysdepend I see I can save the list of views for these specific
> > tables. Though the actual syntax to recreate the view seems to be
> > in sysviews and can span many rows. Any simple way to get this
> information
> > into a statement that can be used to recreate the views? Other
> suggestions?
> >
> > --Dave
> >
> > --047d7bf1987ea143080516713b2d
> >
> >
> >
> >
>
>
*******************************************************************************
> > Forum Note: Use "Reply" to post a response in the discussion forum.
> >
> >
>
> --047d7bd76aeae52bbb0516737bf0
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--f46d04430438212caf05169e0a8f
Huh! Looks like I totally missed generating the privs for views. That's
very old code and no one, not even me, noticed it was missing before. I'll
get on that soonest and get you an update. Shouldn't take long, but I may
not get to look at the code until next week. Busy weekend already looming.
Art
Art S. Kagel, President and Principal Consultant
ASK Database Management
www.askdbmgt.com
Blog: http://informix-myview.blogspot.com/
Disclaimer: Please keep in mind that my own opinions are my own opinions
and do not reflect on the IIUG, nor any other organization with which I am
associated either explicitly, implicitly, or by inference. Neither do
those opinions reflect those of other individuals affiliated with any
entity with which I am affiliated nor those of the entities themselves.
On Thu, May 21, 2015 at 5:15 PM, Informix DBA <in4mixdba@gmail.com> wrote:
> Thank you all for your suggestions. Btw is there a way to get myschema to
> print permissions on views?
>
> On Tue, May 19, 2015 at 2:29 PM, Art Kagel <art.kagel@gmail.com> wrote:
>
> > The -F option to myschema will give you what you want. It also prints out
> > all foreign key constraints that reference the named table(s) since they
> > also disappear when you drop the referenced table:
> >
> > $ myschema -d art -t no_privs -F> > Writing full schema DDL to: stdout
> >
> > CREATE TABLE "art".no_privs (
> >
> > one SERIAL(5) NOT NULL
> > ) IN datadbs EXTENT SIZE 16 NEXT SIZE 16 LOCK MODE PAGE;
> >
> > {
> >
> > Please review extent sizing and adjust to allow for growth.
> > }
> >
> > REVOKE ALL ON "art".no_privs FROM public;> > CREATE INDEX "art".no_privs_ak1 ON "art".no_privs (
> >
> > one ASC
> > ) USING btree IN TABLE;
> >
> > GRANT ALL ON no_privs TO "fred" AS "art";
> > GRANT UPDATE (one) ON no_privs TO "fred" AS "art";> >
> > CREATE VIEW "art".tst_privs (
> >
> > one)
> > AS
> > SELECT
> >
> > x0.one
> >
> > FROM
> >
> > "art".no_privs x0 ;
> >
> > CREATE VIEW "art".tst_privs2 (
> >
> > one)
> > AS
> > SELECT
> >
> > x0.one
> >
> > FROM
> >
> > "art".no_privs x0 ;
> >
> > Art
> >
> > Art S. Kagel, President and Principal Consultant
> > ASK Database Management
> > www.askdbmgt.com
> >
> > Blog: http://informix-myview.blogspot.com/
> >
> > Disclaimer: Please keep in mind that my own opinions are my own opinions
> > and do not reflect on the IIUG, nor any other organization with which I
> am
> > associated either explicitly, implicitly, or by inference. Neither do
> > those opinions reflect those of other individuals affiliated with any
> > entity with which I am affiliated nor those of the entities themselves.
> >
> > On Tue, May 19, 2015 at 11:47 AM, Informix DBA <in4mixdba@gmail.com>
> > wrote:
> >
> > > Hi,
> > >
> > > We are running Informix 11.70. We have some tables that we drop/create
> > via
> > > batch job. However we are losing the views on those tables when we drop
> > > the tables. Do you have a way to dynamically save the list of all the
> > > views on the table as well as save the syntax of the views (with
> > > permissions) so that after we recreate the tables we can simply
> recreate
> > > the views?
> > >
> > > Using sysdepend I see I can save the list of views for these specific
> > > tables. Though the actual syntax to recreate the view seems to be
> > > in sysviews and can span many rows. Any simple way to get this
> > information
> > > into a statement that can be used to recreate the views? Other
> > suggestions?
> > >
> > > --Dave
> > >
> > > --047d7bf1987ea143080516713b2d
> > >
> > >
> > >
> > >
> >
> >
>
>
*******************************************************************************
> > > Forum Note: Use "Reply" to post a response in the discussion forum.
> > >
> > >
> >
> > --047d7bd76aeae52bbb0516737bf0
> >
> >
> >
> >
>
>
*******************************************************************************
> > Forum Note: Use "Reply" to post a response in the discussion forum.
> >
> >
>
> --f46d04430438212caf05169e0a8f
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--001a113efd6264407805169e3012