On 5/18/2007 at 5:38 AM, in message <5b*************@mid.individual.net>,
Serge Rielau<sr*****@ca.ibm.comwrote:
situ wrote:
>so now i am planning to use schema instead of databases. is it going
to workout?
schema looks like a much better fit indeed.
Note that ISVs like SAP use schemas in a similar fashion (E.g. SAP1)
You will find that Db2 9 has procedures that make it easy to copy
schema's. See under "administrative routines".
This brings to mine something I've been wondering about. We just got
approved to purchase DB2 so we may even actually start using it now. :-)
Anyway, it's about how we do applications testing. First a little
background that will hopefully make clear why I am asking the questions...
We are a mainframe shop that uses DL/I databases and VSAM files for all of
our data. We are planning on utilizing DB2/LUW 9.1 going forward, when
feasible.
We have about 20 mainframe COBOL developers. There are times that more than
one developer may be working on projects that utilize the "same" data
repositories. By that I mean that developer 1 may be working on one project
that will require changes to programs that update the Demand System DL/I
Database. Developer 2 may be working on a different project that also
requires changes to programs that update the Demand System DL/I Database.
What happens right now is that we have eight development Demand System DL/I
Databases. The database structures are all identical (and also identical to
the Production Demand System DL/I Database). But the data in the databases
are all physically (and logically) separate. By doing this it allows more
than one developer to make changes without affecting anyone else. We also
have eight development CICS regions, each one being attached to one of the
eight versions of each DL/I Database (and each VSAM file).
Now to get to my questions. Any answer is probably feasible since we are
starting from scratch with regard to DB2 data. We have no DB2 data right
now, so I want to get started on the best possible development path prior to
actually working on real projects.
What is the best way to structure our DB2 database systems for development?
I was thinking that we'd have a single DB2/LUW instance and have eight (or
more!) development databases running within that instance. Production, of
course, would have it's own totally separate instance (probably on a totally
separate system) and have the single production database running there.
For development we'd want to have each separate database have, for the most
part, the exact same database structure (same schemas, same tables, etc.).
Only when the project at hand was to add new tables or new columns to
existing tables might one database look somewhat different than all of the
others. But even in that case, once we go in to production with the new
structure we'd want all of the other development databases, as well as
production of course, to once again look the same (with the new structure).
Does this sound reasonable? What problems might I run in to? I actually
also imagine having a "source" development database where every night we
would reload it entirely from the production database. Then when a
developer wanted production data for certain tables they could do a LOAD
from that "source" database in to the development database that they are
using.
Another question I have has to do with the use of schemas. I have never
gotten a good feeling as to what they are used for. From what "situ" is
saying it sounds like he's thinking of having a single production database
with many schema where all of the tables in each schema are the same for
each schema, with just the schema name being different. Might this be a
better way for us to design our development system? I have two possible
concerns. One is that I had thought we might utilize schema as kind of a
"system level" qualifier. By this I mean we have different systems such as
our Demand Account System (our bank checking accounts), our Savings Account
System, or Customer Information System, our General Ledger System, our Loans
System, etc.
My thought was that we might have the following schema: DEMAND, SAVINGS,
CUSTOMER, GL, LOANS, etc. Then we'd have a table called DEMAND.MASTER, one
called SAVINGS.MASTER, CUSTOMER.MASTER, etc. The DEMAND.MASTER and the
CUSTOMER.MASTER tables would not in any way resemble each other. They are
just the master tables for those particular systems. Then DEMAND would have
other tables such as DEMAND.TRANSACTIONS, DEMAND.MEMOS, etc. CUSTOMER might
have CUSTOMER.ADDRESSES, CUSTOMER.MEMOS, etc.
Doing this, however, pretty much nixes having a single development database
with many "development schemas". Another reason I don't think that using
the single database/multiple dev schema method would work has to do with
application packages. All of our application programs will be written in
COBOL and use embedded SQL static (and maybe some embedded dynamic SQL).
From what little I've done with this, it looks like we can create an
application package and bind it against a particular development database.
We then could take the resulting bind file and also bind it against all of
the other development databases without 'recompiling' the same program over
and over against each development database. The programs themselves would
simply address the fully qualified SCHEMANAME.TABLENAME, and they would
simply connect to whichever development database they want to work with.
(Obviously we would not hard code the database name into the programs.)
Essentially, it may be the case that two developers might want to run the
"same" program against the same database. But one person might be testing
the production version of the program, and thus would require the production
version of the package. Another developer might be modifying the same
application. He would then pre-compile, bind, and compile his program,
using the bind "VERSION" option so that he isn't overwriting the
"production" bind file that already exists within that development database.
Once the application was changed and put in to production then we'd, of
course, implement the new version of the package there and we'd also want to
change all of the "production" packages on all of the development
databases.
Does this all make sense? It all sounds rather more complicated that what
we do now, but hopefully not too bad.
By the way, is there a way to copy a package directly from one database to
another? Seems to be this would be simpler than having to use a source bind
file to bind new packages to each database.
Anyway, hope this all makes sense and that I'm at least going in somewhat
the right direction with all of this. As I said, we are starting from the
ground up, so any 'best practices' suggestions would be very welcome!
Thanks,
Frank
