Denys Beauchemin ([log in to unmask]) wrote:
> The problem goes deeper. If you start creating views to access the data in
> the attached IMAGE datasets, these views disappear when the database is
> detached. The current versions of the mainstream database tools do not save
> the views.
When you issue a DETACH command in IMAGESQL.PUB.SYS, an SQL "DROP TABLE"
statement gets issued for each data set (mapped table). A side effect of
a DROP TABLE is that all views and authorities (i.e. GRANTS) associated
with the table (and views) are also dropped.
You can use SQLGEN.PUB.SYS to save the information that will be dropped.
SQLGEN is described in the ALLBASE/SQL Database Administation Guide
(36216-90005).
Use the "GENERATE VIEWS" command to generate a file that has CREATE VIEW
statements for one or more views that you are interested in. You can
either generate all views in the DBE, all views for a particular owner,
or one or more specific views.
Here is an example using musicdbe which sends the definitions of all views
in the DBEnvironment to a file called "myfile":
:sqlgen.pub.sys
THU, NOV 9, 1995, 7:54 PM
HP36216-02A.G1.10 SQL Command Generator/3000 ALLBASE/SQL
(C) COPYRIGHT HEWLETT-PACKARD CO. 1986,1987,1988,1989,1990,1991,
1992,1993 ALL RIGHTS RESERVED.
>> generate views
A DBE session has not been started. (DBWARN 701)
Calling STARTDBE for you before proceeding with command.
DBEnvironment Name >> musicdbe
DBEnvironment successfully started.
ALLBASE/SQL Command Generator for Views
Schema File Name or '//' to STOP command >> myfile
Do you wish to generate associated View Authority (n/y)? y
Please enter Owner Names. Type @ for all, ? for a list of
Owner Names, or RETURN to quit.
Owner Name >> @
Do you wish to specify View Names for each Owner (n/y)? n
Generating CREATE VIEW MUSIC.SELECTIONS_A_V0
Generating Authority for MUSIC.SELECTIONS_A_V0
>> exit
DBEnvironment has been RELEASED.
Now you can look at the contents of the file:
:print myfile
/* This file was created with a user language environment of */
/* NATIVE-3000 */
SET OWNER MGR@TESTACCT;
CREATE VIEW MUSIC.SELECTIONS_A_V0
(SELECTIONNAME) AS
select SELECTIONNAME from MUSIC.SELECTIONS_A;
GRANT SELECT
ON MUSIC.SELECTIONS_A_V0 TO PUBLIC
BY MGR@TESTACCT;
COMMIT WORK;
You can edit the file so that it only contains the SQL statements that
you are interested in. When you are ready to recreate the views (after
you have reattached to the DBE, do the following:
isql=>connect to 'musicdbe';
isql=>set echo on; <---to echo commands to your display
isql=>start 'myfile'; <---to execute the SQL in 'myfile'
This isn't the best example because MUSIC.SELECTIONS_A_V0 is actually
a view created by IMAGE/SQL itself, but you should be able to get the
idea.
Hope this helps,
Leslie-Anne
______________________________________________________________________________
Leslie-Anne Bain
Hewlett-Packard Company Voice: 408/447-0666
19447 Pruneridge Ave, MS 47UX FAX: 408/447-7902
Cupertino, CA 95014-9913 E-Mail: [log in to unmask]
______________________________________________________________________________
|