ChannelDB2

Using Data Federation with solidDB

I hadn't actually thought of using federation with an in-memory database, but someone asked and I decided to give it a try. Turns out it's very easy. I also discovered two things that can be useful to a DB2 person like me who's just getting started with solidDB. I'll use this post to talk about those and show you how you to try solidDB federation yourself. Of course, if you've never done anything with IBM's data federation, I recommend you spend 15 minutes with A Fast Start with DB2 Federation before implementing what I have for solidDB.

Let's start with the discoveries since we'll use one to help set up federation. Specifically, I found that federation let me use the DB2 clp and DB2 SQL scripts to create tables and indexes in solidDB. This was a huge convenience. Why? I had existing DB2 DDL I wanted to use. I was willing to edit it and pass to it solidDB, but I was unfamiliar with solidDB's interfaces. That left me at a minor impasse, so I decided to hold off and just get basic configuration done. After I got that working, I found that IBM federation's passthru mode made it easy to pass DDL to solidDB via DB2 clp scripts. I have an example of that in the Do-It-Yourself section of this post.

Second, I found that federation's nicknames let me use DB2's built-in SQL Replication to replicate from my DB2 database to solidDB. Again, this was a convenience to me. I already had SQL Replication working for something else and I just added solidDB as a target. However, be aware that you don't need SQL Replication with solidDB. That's because solidDB Universal Cache comes with its own built-in data replication. (Also, I know someone will ask, so... no, I haven't tried Q Replication yet. I think it will work, but that'll have to be a future post.)

The rest of this post shows you how to federate and replicate with DB2 and solidDB.

Prerequisites

To access solidDB, DB2's data federation needs to be configured with either the "ODBC wrapper" or the "JDBC wrapper." These are two types of adapters that let DB2 pass SQL to other databases. I will use the ODBC wrapper in this post. However, while DB2 does have some wrapper support built into it, the ODBC and JDBC support are not part of what's built in. You must install either InfoSphere Federation Server or InfoSphere Replication Server to get that support. If you do not have either of these products, you won't be able to implement what I show here. I used InfoSphere Federation Server 9.7 installed on top of DB2 9.7 ESE.

My solidDB is at 6.5, but I believe these instructions will work with earlier releases of v6 as well. My solidDB is on the same system as my DB2, but this isn't required.

Do-It-Yourself: Federation

After you have the prerequisites installed, I believe this should take you about 15-20 minutes. We'll use the DB2 Sample database.

1. Create the DB2 Sample database if you haven't already. Use the following command from a DB2 command window:

db2sampl

2. Create a wrapper, server, and user for federation.

These are the federation objects that let you work with a given solidDB database. I used instructions provided on developerWorks. You'll need to click the link and follow those instructions. I don't want to try to repeat them here because there are two sets - one for UNIX and one for Windows. I used the instructions for Windows. They are very short.


3. Copy and paste the first script at the bottom of this post into a file named myemp.sql.

Here's where I made my first discovery. I took my existing DB2 DDL for the Sample database's Employee table and added DB2's passthru SQL statement. That's all I needed to send my existing DDL to solidDB. The script also creates a DB2 nickname for the table in solidDB and then populates it with DB2 data using an insert with subselect. That insert and the following select prove that you've federated solidDB.


4. Run the SQL script by issuing the following command from a DB2 command window:

db2 -vtf myemp.sql > myemp.txt

If you now look at the myemp.txt, you should see that the select retrieved data from solidDB through federation's nickname.

Do-It-Yourself: Replication

Once you've got the EMP nickname created, you're ready to try replication. The instructions are divided in two groups. The first shows you how to set up replication. The second shows you how to run replication programs and replicate.

If you're familiar with SQL Replication, you'll recognize everything here as standard SQL Replication configuration. However, if you're new to SQL Replication, I recommend you start by checking out an older post of mine called A Fast Way to Get Started with DB2's SQL Replication.

Now, let's set up replication:

1. Enable archive logging on the Sample database. Use the following commands from a DB2 command window:

db2 update database configuration for sample using logarchmeth1 logretain
db2 backup database sample

2. Copy and paste the last script in this post into a file called solid.asnclp

3. Edit the file and make the changes described in the script's opening comments.

4. Run the script through DB2's CLP for data replication. Use the following command:

asnclp -f solid.asnclp >solidasn.txt

You're now ready to start the replication programs and replicate data:

5. Start the Capture program from a DB2 command window:

asncap capture_server=sample

6. Start the Apply program from a different DB2 command window:

asnapply control_server=sample apply_qual=MYQUAL1

7. Query the data in the EMP (target) table from a third DB2 command window:

db2 connect to sample
db2 select salary from emp

8. Update rows in the Employee (source) table from the same DB2 command window:

db2 update sample set salary=salary+1
db2 commit

9. Wait one minute and then query the EMP table again:

db2 select salary from emp

The target data now reflects the changes in the source. In case you're wondering, you need to wait up to a minute for the last query because the Apply program will go into a sleep mode for one minute anytime it finds no data on the source.

That's it. You're done :)

Scripts

There's two scripts here. The first, myemp.sql, is for setting up federation and issuing a command that shows federation working. The second, solid.asnclp shows how to create everything needed to use SQL Replication with the federation objects created in myemp.sql.


connect to sample;

--
-- After this statement, all SQL goes directly to solidDB.
--
set passthru solid65;

CREATE TABLE "EMP2" (
"EMPNO" CHAR(6) NOT NULL ,
"FIRSTNME" VARCHAR(12) NOT NULL ,
"MIDINIT" CHAR(1) ,
"LASTNAME" VARCHAR(15) NOT NULL ,
"WORKDEPT" CHAR(3) ,
"PHONENO" CHAR(4) ,
"HIREDATE" DATE ,
"JOB" CHAR(8) ,
"EDLEVEL" SMALLINT NOT NULL ,
"SEX" CHAR(1) ,
"BIRTHDATE" DATE ,
"SALARY" DECIMAL(9,2) ,
"BONUS" DECIMAL(9,2) ,
"COMM" DECIMAL(9,2) )
;

CREATE INDEX "XEMP2" ON "EMP2"
("WORKDEPT");

commit work;

--
-- After this statement, all SQL is processed by DB2.
--
set passthru reset;

--
-- Point DB2 to the solidDB table.
--
create nickname emp for solid65.dba.emp2;

--
-- Insert DB2 data into the solidDB table.
--
insert into emp2 select * from employee;

--
-- Take a look at what's in the solidDB table using DB2 SQL.
--
select * from emp2;
commit;


#
# Before running this script, do the following:
#
# 1. Change all occurances of 'db2admin' to your DB2 user.
# 2. Change all occurances of 'mypw' to your DB2 user's password.
#

#
# Identify databases involved.
# ('Control' and 'Target' are usually the same)
#
SET SERVER CAPTURE TO DB SAMPLE ID db2admin PASSWORD "mypw";
SET SERVER CONTROL TO DB SAMPLE ID db2admin PASSWORD "mypw";
SET SERVER TARGET TO DB SAMPLE ID db2admin PASSWORD "mypw";

SET RUN SCRIPT NOW STOP ON SQL ERROR ON;

#
# Add tables to hold relication metadata.
#
CREATE CONTROL TABLES FOR CAPTURE SERVER;
CREATE CONTROL TABLES FOR APPLY CONTROL SERVER;

#
# Identify source table.
#
CREATE REGISTRATION (EMPLOYEE) DIFFERENTIAL REFRESH STAGE CDEMPLOYEE;

#
# Add a container to hold subscriptions
#
CREATE SUBSCRIPTION SET SETNAME SET01 APPLYQUAL MYQUAL1 ACTIVATE YES
TIMING INTERVAL 1 START DATE "2008-01-01" TIME "01:00:00.000000";

#
# Add a subscription that maps the source to the target nickname
#
CREATE MEMBER IN SETNAME SET01 APPLYQUAL MYQUAL1 ACTIVATE YES
SOURCE EMPLOYEE
TARGET NAME EMP2;

# Now, run the file through the asnclp command
# and you are ready to replicate data.
QUIT;

--

Views: 480

Comment

You need to be a member of ChannelDB2 to add comments!

Join ChannelDB2

Featured Downloads

Try BLU Acceleration on Cloud

© 2017   Created by channeldb2.   Powered by

Badges  |  Report an Issue  |  Terms of Service