IDS Forum

windows-1252?Q52=65=3A=72=65=6C=61=74=65=64=20=6F=

Posted By: ART KAGEL, BLOOMBERG/ 731 LEXIN
Date: Thursday, 8 November 2007, at 8:58 a.m.

You should have included the -ss option to dbexport. Though I would have thought that it would have exported the view definitions anyway.

Views and all of the table's triggers and constraints are dropped automatically when a table is dropped. Referring foreign key constraints on other tables will not be dropped but their existence will prevent you from dropping the table in the first place. Stored procedures referring to the table will not be dropped but they will return errors (mostly -206, -111) when they attempt to access the missing table.

If you use myschema instead of dbschema, before dropping the table, then you can run it with the -F flag which will also print schema for foreign keys referencing the named table and views created on the table:

sqlcmd -d mydatabase
[1]> create table drop_test(one int);
[2]> create view drop_view( once ) as select * from drop_test;
[3]> q;
> myschema -d mydatabase -t drop_test -F
Writing full schema DDL to: stdout
{ TABLE drop_test row size = 4 number of columns = 1 index size = 0 }
CREATE TABLE drop_test (

one INTEGER
) IN pl_dbs1 EXTENT SIZE 16 NEXT SIZE 16 LOCK MODE PAGE;

{

Please review extent sizing and adjust to allow for growth.
}

REVOKE ALL ON drop_test FROM public;
GRANT SELECT, UPDATE, INSERT, DELETE, INDEX ON drop_test TO "public";

CREATE VIEW drop_view (

once)
AS
SELECT

x0.one

FROM

drop_test x0 ;

GRANT SELECT, UPDATE, INSERT, DELETE ON drop_view TO "kagel" WITH GRANT OPTION;

One of the reasons I bother to maintain the thing is the -F and other value added options I've built in. Myschema is part of the package utils2_ak downloadable from the IIUG Software Repository.

Question: Why export and import the data in the first place? Why not just upgrade the instance in-place? IDS can do that!

Art S. Kagel

----- Original Message -----
From: Jacques Lapeire <ids@iiug.org>
To: ids@iiug.org
At: 11/08 5:13:34

Hello,

does anyone have some info about possible objects (such as views) that
disappear when you drop a table ? Are there any other elements that disappear
?

After a migration from 9.21 (2Gb limit) to 9.40 , some weeks later we found
out that we lost a view on a table "fho".
This table had to be unloaded in differents parts due to the 2 Gb limit, and
had been dropped before the dbexport was launched.

Now we are wondering if some other things might have been lost ?

Thanks for any help.

Jacques Lapeire

*******************************************************************************
Forum Note: Use "Reply" to post a response in the discussion forum.

Messages In This Thread

• windows-1252?Q52=65=3A=72=65=6C=61=74=65=64=20=6F=
  ART KAGEL, BLOOMBERG/ 731 LEXIN -- Thursday, 8 November 2007, at 8:58 a.m.
  • Re: windows-1252?Q52=65=3A=72=65=6C=61=74=65=64=20
    H.G -- Thursday, 8 November 2007, at 2:17 p.m.

IDS Forum is maintained by Administrator with WebBBS 5.12.