Blog do projektu Open Source JavaHotel

czwartek, 21 czerwca 2012

DB2, HADR and server code

Introduction
So far we have been talking about data. But what about server sql code. We have deployed new version of our very important packages, stored procedures, trigger and UDFs and we have to switch to secondary server.
Test continue
Let's create a new UDF.
CREATE OR REPLACE FUNCTION MULTIPLY (IN FACTOR1 INTEGER, IN FACTOR2 INTEGER)
 RETURNS INTEGER
 NO EXTERNAL ACTION
F1: BEGIN ATOMIC
  RETURN FACTOR1 * FACTOR2; 
END
@
Then, being connected to primary server (db2had1) deploy it.
db2 -td@ -sf MULT.sql
And check how it works.
db2 "values(multiply(2,2))"

1          
-----------
          4

  1 record(s) selected
Make takeover, switch to secondary server 
And now force takeover on a secondary server(db2had2) and down the primary server (db2had1). All the time the client is connected. When takeover is finished rerun the command again.
 db2 "values(multiply(2,2))"
SQL30108N  A connection failed but has been re-established. Special register 
settings might have been replayed. Host name or IP address of the new 
connection: "think". Service name or port number of the new connection: 
"50002".  Reason code: "1".  SQLSTATE=08506
The sql error code is as expected and we can rerun the command again.
db2 "values(multiply(2,2))"

1          
-----------
          4

  1 record(s) selected.
So after switching to secondary server 2 by 2 is 4 all the time. As we see also sql server code is replicated to secondary server. Now make a little mischief - modify our business procedure a little bit.
CREATE OR REPLACE FUNCTION MULTIPLY (IN FACTOR1 INTEGER, IN FACTOR2 INTEGER)
 RETURNS INTEGER
 NO EXTERNAL ACTION
F1: BEGIN ATOMIC
  RETURN FACTOR1 * FACTOR2 + 1;
END
@
And deploy modified UDF - this time it is deployed to db2had2 acting as primary server. db2 -td@ -sf MULT.sql DB20000I The SQL command completed successfully.
Switch the role again, make db2had1 primary server. 
Log in to db2had1 and make db2had1 as primary
db2start
db2 activate database sample
db2 takeover hadr on database sample
 db2 get db cfg for sample | grep HADR
 Rola w bazie danych HADR                                                 = PRIMARY
All the time the client is connected. And now rerun the command again - keep in mind that it throws SQL30108N for the first time after server switching. And grande finale :
db2 "values(multiply(2,2))"

1          
-----------
          5

  1 record(s) selected.
Summary
So not only data but also sql server code is duplicated to secondary server. No worry about update or upgrade server code in HADR environment. It is good news. But there is also bad news. DB2 is able to duplicate only sql code. So if we have external procedure or function (for instance C++ or Java) the corresponding .jar or .so file is not replicated and it should be done manually.

Brak komentarzy:

Prześlij komentarz