Conference orarep::nomahs::sql

3893.0. "SET TRANS TWO DBs W/IN COMPOUND?" by M5::BLITTIN () Mon Jun 02 1997 10:57

    Customer is requesting help with the following.  I tried to explain and
    showed them in the SQL Guide to Programming where you can't set trans
    to two databases within the same compound statement.  Am I missing
    something and/or is there an alternative method. Of course they are
    getting the SQL-F-ONEDBINMOD....
    
    Customer statement below...
    
    Thank You,
    
    
    The problem we're encountering is that we need to access two Rdb
    databases from a single SQL procedure.  The procedure reads an ASCII 
    text file, and for each line it parses out certain fields, checks a 
    couple of them against one database, and then writes the formatted 
    records to a second database. 
       
    What we're looking for is an answer of how to handle this kind of 
    transaction -- one that needs to reference two databases, and to keep
    them open (for context purposes) at the same time.  In other words, it is
    NOT sufficient to open one, do all necessary processing, close it, then
    open the second, etc.  There's a constant "back-and-forth" for each record 
    processed. 
     
    BTW, the purpose for the two databases is that all of the tables were 
    originally included in a single database.  But one table in particular
    is HIGHLY dynamic, and has hundreds of thousands of additions and
    deletions EACH DAY.  It was causing severe performance problems when included
    with the dozens of other tables in our primary database, so we broke it out
    into its own database, both for performance and for maintenance purposes. 
    So we KNOW that all of the applications work (as far as logic, flow, syntax, 
    etc.) when all the tables are in a single database.  It's only after we 
    broke out the one table into its own database that the problems began. 
     
    Please let me know what solutions/suggestions you have.  We are
    basically in "outage" mode until this can get resolved, and are willing 
    to work through whatever days/hours are necessary to get this fixed.  We
    understand that the 3-hour time difference between us can be a factor, 
    but we'll do whatever is possible to accomodate.
    

A question.

	- Why did they think splitting the table into its own database
	would help performance?  I can't think of any good reason to
	do that.  In fact it makes the environment more complex (yeah they
	found that) and also means they needs 2PC overheads for the
	transaction.

	- It might be better to help them solve the performance problem
	by keeping the table in place.

Some answers:

	- you can not reference more than one database from a single
	procedure, this is because Rdb keeps the request in the context of the
	database attach.

	This is a documented restriction and so can not be "fixed".
	This never worked so they can't be in "outage" mode because of this
	restriction. (by the way)

	- I believe, but have never tested, that DBI (aka Distributed Option)
	for Rdb7 allows some procedure support for multiple databases.  It
	sits above the database instances.

	- In Rdb7 they could write an external function or procedure which
	attaches the second database.

	- The customer says "...are willing to work through whatever
	days/hours are necessary to get this fixed".  Are they willing to
	upgrade to Rdb7?

Ian

    
    Thanks Ian...they are looking into DBI.