in reply to Concurrent access to db2 and sybase databases

mayankshah:

I've never had any luck in accessing two databases such that I could transfer any data between them without getting the data locally and then retransmitting it. However, some databases (e.g. MS SQL Server) have the ability to link to a different server such that you can transfer data between the databases using a simple select. You might investigate that possibility.

In fact, I'm working on a project right now that connects to over 20 different databases to generate some summary reports via statements like:

INSERT Summary ( <fieldlist> )
SELECT <expressions>
FROM   <Server>.<Database>.<schema>.<table> ALIAS1
JOIN   <Server2>.<Database2>.<schema2>.<table2> ALIAS2
       ON ALIAS1.CommonKey = ALIAS2.CommonKey
WHERE  <expression>

[download]

If you can swing it, this is the way you'd want to go as it will save network traffic (the data goes straight from one server to the other without having to visit the computer running your program), simplify your coding (half as many SQL statements to run, other databases are easier to access by just adding an additional qualifier to their name).

If you're in a large shop, this can help you document data flows between systems, as the linked server definitions can be accessed by the DBAs.

...roboticus

Replies are listed 'Best First'.
Re^2: Concurrent access to db2 and sybase databases
by mayankshah (Initiate) on Mar 26, 2008 at 13:29 UTC
    i think i wasnt able to define the problem clearly... i am getting the data locally in "@row" and then using this data as parameter for the subsequent query which accesses a different database on a different host .. the trouble is usage of two database handles... they dont work together ... if the 1st connection is open using $dbh1 the second connection cannot be opened using $dbh2 , i have close the connection and open a new connection on same database handle to prepare the the next query i would like to use different databse handles for each new connections i open
[reply]
      mayankshah:

      Oh, I see what you want. Yeah, you can't expect two databases to be able to share a single database connection. However, you can get the results you want using something like the (untested) bit below:

      $dbh1->connect("to db2 database"); 
$dbh2->connect("to sybase database"); 
$sth1=$dbh1->prepare("select apples, bananas from fruit_salad");
$sth2=$dbh2->prepare("insert snack (apples, bananas) values (?,?)");
$sth1->execute(); 
while (@row=$sth1->fetchrow_array()) {
   $sth2->execute(@row);
}

      [download]

      This way, you get the data out of your statement handle in a nice, simple array which you feed to your *other* statement handle.

      ...roboticus
[reply]
[d/l]
        thanks a ton it worked
[reply]
        thanks it worked
[reply]

In Section Seekers of Perl Wisdom