So I have a potential bug in DB2 9.7 FP3a and am curious for some
feedback before opening a PMR.

To reproduce:

create table test1.table (c1 int);
create view test1.view (c1) as select c1 from test1.table;

This creates a view that is explicitly dependent on TEST1.TABLE. If I
extract the DDL for this view using db2look it returns as expected:


$ db2look -d sample -e -z test2 -v view
-- No userid was specified, db2look tries to use Environment
variable USER
-- USER is: IBJORHOV
-- Specified SCHEMA is: TEST1
-- Creating DDL for table(s)

-- Schema name is ignored for the Federated Section
-- This CLP file was created using DB2LOOK Version "9.7"
-- Timestamp: Mon Oct 31 15:43:44 PDT 2011
-- Database Name: SAMPLE
-- Database Manager Version: DB2/AIX64 Version 9.7.3
-- Database Codepage: 1208
-- Database Collating Sequence is: IDENTITY


-- [output edited for brevity]

----------------------------
-- DDL Statements for Views
----------------------------
SET CURRENT SCHEMA = "IBJORHOV";
SET CURRENT PATH =
"SYSIBM","SYSFUN","SYSPROC","SYSIBMADM","IBJORHOV" ;
create view test1.view (c1) as select c1 from test1.table;


If I call SYSPROC.ADMIN_COPY_SCHEMA to copy the TEST1 schema to TEST2:

call sysproc.admin_copy_schema('TEST1', 'TEST2', 'COPY',
NULL, NULL, NULL,
'DBA','E');


This creates the objects TEST2.TABLE and TEST2.VIEW as expected.
However, TEST2.VIEW is still dependent on TEST1.TABLE - not
TEST2.TABLE, as you might expect:

$ db2look -d opadwd -e -z test2 -v view
-- No userid was specified, db2look tries to use Environment
variable USER
-- USER is: IBJORHOV
-- Specified SCHEMA is: TEST2
-- Creating DDL for table(s)

-- Schema name is ignored for the Federated Section
-- This CLP file was created using DB2LOOK Version "9.7"
-- Timestamp: Mon Oct 31 15:57:10 PDT 2011
-- Database Name: SAMPLE
-- Database Manager Version: DB2/AIX64 Version 9.7.3
-- Database Codepage: 1208
-- Database Collating Sequence is: IDENTITY


-- [output edited for brevity]

----------------------------
-- DDL Statements for Views
----------------------------
SET CURRENT SCHEMA = "IBJORHOV";
SET CURRENT PATH =
"SYSIBM","SYSFUN","SYSPROC","SYSIBMADM","IBJORHOV" ;
create view "TEST2 ".view (c1) as select c1 from test1.table;


The ADMIN_COPY_SCHEMA procedure correctly alters the CREATE VIEW
statement to create the view in the correct schema, but does not
change the other instances of the source schema to the target schema.

I expected that the objects in the newly-created schema depend on
the other objects in the newly-created schema, not on objects in the
"old" source schema.

Is this a reasonable expectation? Or would you expect that views in
the newly-created schema still depend on the source schema?


Thanks,

On 2011-11-01 00:04, Ian wrote:
[...]

[...]

Does the same thing happen with other referenced objects, say for
example foreign keys (I can't try myself right now)? I can't make up my
mind whether I consider this a bug or not, but it would be nice if the
docs described this behaviour.


/Lennart

On Oct 31, 9:03*pm, Lennart Jonsson <erik.lennart.jons... (AT) gmail (DOT) com>
wrote:
Foreign keys are handled properly –*If TEST1.TABLE2 has a foreign
key referencing TEST1.TABLE1, then when I use ADMIN_COPY_SCHEMA ,
the resulting TEST2.TABLE2 will reference TEST2.TABLE1.

On Nov 2, 8:44*pm, Ian <ian.bjorho... (AT) gmail (DOT) com> wrote:


This is the expected behaviour. The ADMIN_COPY_SCHEMA will not attempt
to modify the schema for any qualified objects within object bodies.
Therefore, using a different target schema name may lead to problems
if there are qualified objects within the object body. The inter-
dependecies must be handled seperately.

ADMIN_COPY_SCHEMA is similar to db2move with -CO option .