RE: Restore SQL collection to a different name?

["Wilt, Charles" <CWilt@xxxxxxxxxxxx>]

> 
> You connect to databases. You qualify to schemas.
> 


I've gotta remember that one Bruce.

So back to David's post...

> -----Original Message-----
> From: midrange-l-bounces@xxxxxxxxxxxx
> [mailto:midrange-l-bounces@xxxxxxxxxxxx]On Behalf Of David Gibbs
> Sent: Wednesday, April 06, 2005 2:27 PM
> To: midrange-l@xxxxxxxxxxxx
> Subject: Re: Restore SQL collection to a different name?
> 
> 
> Wilt, Charles wrote:
> > I see your point, but I disagree with you.
> 
> As is your right :)
> 
> > What you're say you're asking for, backup and restore to a new
> > location.  Is in my mind different from what you are actually asking
> > for, backup and restore to a new schema/collection.
> 
> New collection, yes ... *NOT* a new schema (I consider the 
> schema to be
> the database structure).  A collection (as I understand it) 
> is simply a
> container for all the associated data, access paths, and other related
> bits of database information.  In other SQL systems it's 
> simply called a
>  database.

Bruce covered this.  But to reiterate; they are not the same.

Here's the hierarchy:

Instance
  |__Database
       |___Schema
             |___Tables/Index/Views/procedures/ect...

Oracle and DB2 use the term "schema" in the syntax diagram of the CREATE TABLE 
command.

SQL server on the other hand, uses the term "owner".  Both SQL server and 
Oracle tie the schema name to the user ID who owns the objects in the schema.  
DB2, at least on the iSeries doesn't require this tie.

> 
> > You want it to work like backup and restore of a library, 
> the problem
> > is an SQL schema is more complex than a simple library.  Heck, even
> > backup/restore has some glitches when you restore to a different
> > library.
> 
> It shouldn't be.  The iSeries is so tightly integrated that 
> changing the
> database / collection / library name should be simplicity 
> itself (from a
> users perspective).  If the system has to do a lot of work under the
> covers is something I don't care about ... and I shouldn't have to be
> aware of (except, maybe, that the restore takes longer than a normal
> library restore).

I don't think so.  On the iSeries its harder due to the integration.  Until 
v5r2, we had 1 Instance = 1 Database.  Whereas with the RDBMS that run on top 
of the OS, you can have multiple instances, each running multiple DBs.

As I said before, the idea of a schema is more complex than the simple library 
we're used to dealing with.  With any other RDBMS, you'd need another instance 
and/or DB to do testing.  The old way on the iSeries is simply to have another 
library....that idea is just not compatible with the modern needs of an RDBMS.


> 
> > I don't think any RDMS would be able to do what are actually asking
> > for.  To handle testing/development with any RDBMS, including the
> > iSeries, you need to set up separate database servers/instances.
> 
> Absolutely untrue.  I should be able to have as many copies of a
> database on a single system as I have capacity to store them. 
>  Having a
> separate system to host different databases is ridiculous.  Not being
> able to take a copy of a database for testing and/or development
> purposes without moving it to a different system is absurd.
> 
> With MSSQL server I can save a database and restore it to a new name
> easily.  I do it all the time.  It's part and parcel of doing software
> development ... some call it parallel testing.

You can do the same on the iSeries.  But as Bruce pointed too have multiple 
databases on the iSeries you need to use independent disk pools.  Try saving a 
schema on SQL Server or Oracle, ie. all objects owned by a particular user, and 
restoring it to another schema....can't be done in either one AFAIK.


Charles Wilt
iSeries Systems Administrator / Developer
Mitsubishi Electric Automotive America
ph: 513-573-4343
fax: 513-398-1121