Re: Restoring a SQL collection library with constraints

Works fine for me.  Ran through a whole test for you.  Read note at bottom 
also.



> create collection davemid 
  Schema DAVEMID created. 
> CREATE TABLE DAVEMID/B (MYKEY INT NOT NULL, PRIMARY KEY (MYKEY)) 
  Table B created in DAVEMID. 
> INSERT INTO DAVEMID/B VALUES(1) 
  1 rows inserted in B in DAVEMID. 
> CREATE TABLE DAVEMID/A (THISKEY INT NOT NULL, MYKEY INT NOT NULL,
  PRIMARY KEY (THISKEY), FOREIGN KEY (MYKEY) REFERENCES DAVEMID/B 
  (MYKEY) ON DELETE NO ACTION ON UPDATE NO ACTION) 
  Table A created in DAVEMID. 
If you notice, I created B as the 'master' file.  This was to test if an 
alphabetical restore of A first caused any issues.  As you you will see 
later, it didn't.  I also created some views and indexes over file B that 
might be alphabetically first.  No problem.  Now, none of these spanned 
libraries.
> INSERT INTO DAVEMID/A VALUES(500, 1) 
  1 rows inserted in A in DAVEMID. 
> CREATE VIEW DAVEMID/ab AS SELECT * FROM davemid/b
  View AB created in DAVEMID. 
> CREATE INDEX DAVEMID/ab2 ON DAVEMID/B (MYKEY ASC) 
  Index AB2 created in DAVEMID on table B in DAVEMID.

SAVLIB LIB(DAVEMID) DEV(*SAVF) SAVF(ROB/ROB) DTACPR(*HIGH)

DLTLIB LIB(DAVEMID)

RSTLIB SAVLIB(DAVEMID) DEV(*SAVF) SAVF(ROB/ROB)
26 objects restored from DAVEMID to DAVEMID.

STRSQL
> SELECT * FROM DAVEMID/A
....+....1....+....2....+....3 
      THISKEY           MYKEY 
          500               1 
********  End of data  ********

WRKPTFGRP
PTF Group             Level  Status 
SF99530                5032  Installed
SF99529                  41  Installed
SF99503                   3  Installed
SF99298                   7  Installed
SF99295                   2  Installed
SF99287                   7  Installed
SF99282                   3  Unknown 
SF99275                   5  Installed
SF99269                   4  Installed
SF99185                   5  Installed
SF99173                   2  Installed
SF99139                   1  Installed
SF99099                   4  Installed


Many moons ago, I saved a library with lots of constraints to tape.  And I 
could never restore that.  Sent the tape off to IBM and they couldn't 
restore it either.  Further saves didn't help.  (We were testing an 
unload/reload to another machine.)  IBM suggestion was to 'rebuild' the 
files.  However they were created in my foolish youth and I didn't save 
the SQL statements.

Rob Berendt
-- 
Group Dekko Services, LLC
Dept 01.073
PO Box 2000
Dock 108
6928N 400E
Kendallville, IN 46755
http://www.dekko.com





David Gibbs <david@xxxxxxxxxxxx> 
Sent by: midrange-l-bounces@xxxxxxxxxxxx
03/31/2005 07:29 PM
Please respond to
Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>


To
midrange-l@xxxxxxxxxxxx
cc

Subject
Restoring a SQL collection library with constraints






Folks:

I just tried to restore a customers database that was created with a
'create collection' command (via interactive SQL).  Many of the tables
in this library are supposed to have primary key and foreign   key
constraints.

However, some of the tables (in the restored library) that are supposed
to have the constraints, do not.

I see a bunch of 'CPF9812-File ISSUES in library DATALIB not found.' and
'CPD32B0-Constraint is not valid.' (referring to the issues table)
diagnostic messages in the joblog.

The CPD32B0 message is most concerning, because the file it was
complaining about has not yet been restored.

At the end of the restore, the ISSUES table (and others) does not have a
primary key constraint, even though it should.

Is there a special way I should be restoring the library to get the
constraints applied correctly?  Or is it more likely that the table
didn't have the constraint on it when it was saved?

Both the system that the database was saved from, and is being restored
to, are V5R3.

Thanks!

david
-- 
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing 
list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.