By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
445,813 Members | 1,253 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 445,813 IT Pros & Developers. It's quick & easy.

Database connection through SP

P: n/a
Hello all,
i have Database1 and database2,
is it possible to make database connection to database2 by running
stored procedure on database1.

Thanks and Regards
Situ

May 16 '07 #1
Share this Question
Share on Google+
8 Replies


P: n/a
On May 16, 5:17 am, situ <SRIDHAR...@REDIFFMAIL.COMwrote:
Hello all,
i have Database1 and database2,
is it possible to make database connection to database2 by running
stored procedure on database1.

Thanks and Regards
Situ
It is possible.
But you may concern another option -- federated.

May 16 '07 #2

P: n/a
situ wrote:
Hello all,
i have Database1 and database2,
is it possible to make database connection to database2 by running
stored procedure on database1.
If the procedure is written in a different language than SQL, it would work.

But such scenarios are very rare and usually indicate that you work around a
possibly better solution. Would you mind telling us which problem you want
to solve?

--
Knut Stolze
DB2 z/OS Utilities Development
IBM Germany
May 17 '07 #3

P: n/a
On May 18, 2:28 am, Knut Stolze <sto...@de.ibm.comwrote:
situ wrote:
Hello all,
i have Database1 and database2,
is it possible to make database connection to database2 by running
stored procedure on database1.

If the procedure is written in a different language than SQL, it would work.

But such scenarios are very rare and usually indicate that you work around a
possibly better solution. Would you mind telling us which problem you want
to solve?

--
Knut Stolze
DB2 z/OS Utilities Development
IBM Germany
not at all.

we have one application instance for different databases.
in our application when a customer registers we are creating a
separate database for them,
the problem is, we are not able to bind a data source name to JNDI
name, for that to happen we need to restart the webspeare server,once
you restart then only it wil bind the data source name.

so what we are planning is to connect to a central db (database-1)
then through some procedure or through some program we make a
connection to
customer database (database-2) and direct all request to database-2.
so now i am planning to use schema instead of databases. is it going
to workout?

thanks
situ

May 18 '07 #4

P: n/a
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".

Cheers
Serge

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab
May 18 '07 #5

P: n/a
On May 18, 4:38 pm, Serge Rielau <srie...@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".

Cheers
Serge

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

May 18 '07 #6

P: n/a
On May 18, 4:38 pm, Serge Rielau <srie...@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".

Cheers
Serge

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab
ok. thanks for response, but we are using db2 8.2 ..
one quesion may be not appropriate ...

why we have to drop all the objects before droping the schema, does it
serve any purpose..

Thanks and regards
situ
May 18 '07 #7

P: n/a
situ wrote:
why we have to drop all the objects before droping the schema, does it
serve any purpose..
So that I can make money on developerWorks :-)
http://www.ibm.com/developerworks/db...dm-0602rielau/

Seriously though in DB2 9 a drop schema procedure is provided (not the
one in the article ;-).

Sometimes it's easier and just as effective to provide a stored
procedure that does the job than to enhance built-in statement like DROP
SCHEMA.
It's pretty common practice in the industry to supplement the base DDL
with value add procedures...

Cheers
Serge

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab
May 18 '07 #8

P: n/a
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

May 18 '07 #9

This discussion thread is closed

Replies have been disabled for this discussion.