How to Query Data on CouchDB via Db2 Federation

Answer

To configure a federation to access CouchDB data source through Rest Service wrapper, you must provide the federation with information about the data sources and objects that you want to access, then create wrapper, server, user mapping and nickname about the JSON file on remote CouchDB data source.

Before you begin

Check that the restservicewrapper.jar file and its dependencies are installed and configured in CLASSPATH on the server that acts as the federation. The restservicewrapper.jar should be installed in $INST_DIR/sqllib/federation/restservice/ path.

1. Enable Federation server and restart Db2

# db2 update dbm cfg using federated YES

# db2stop force

# db2start

2. Test the connection to the CouchDB data source and verify the service is started correctly.

telnet <CouchDB_server_ip>  <port>
If the connection is successful, you will receive the following similar output from the command.

# telnet 9.30.244.212 5984
Trying 9.30.244.212...
Connected to 9.30.244.212.
If the connection is failure, you will receive an error. Check the CouchDB service status.

3. Create wrapper, server, user mapping, nickname and query the nickname.

# connect to testdb

# create wrapper JAVA library 'libdb2qgjava.so' options(unfenced_wrapper_class 'com.ibm.federation.wrapper.unfenced.RestServiceWrapper')

# create server JSERV3 type couchdb version 2.54 wrapper java options(host '9.30.244.212', port '5984', dbname 'test',  LOG_PATH '/home/db2inst1/fed_couchdb/log_couchdb')

# create user mapping for user server JSERV3 OPTIONS( remote_authid 'root',remote_password 'passw0rd')

# create nickname restaurants(name varchar(40) options(jpath '$.name'), id varchar(10) options(jpath '$.restaurant_id'), borough varchar(16) options(jpath '$.borough'), address varchar(64) options(jpath '$.address'), cuisine varchar(6) options(jpath '$.cuisine'), socre int options(jpath ‘$.grades[0].score’), date_0 date options(jpath '$.grades[0].date'))for server JSERV3 options ( doc 'restaurants');

# select * from restaurants

NAME            ID         BOROUGH          ADDRESS                                                          CUISINE SCORE   DATE0   
---------------------------------------- ---------- ---------------- ---------------------------------------------------------------- ------- ----------- ----------
Dj Reynolds Pub And Restaurant           30191841   Manhattan        {building=351, coord=[-73.98513559999999,40.7676919], street=Wes Irish             2 09/05/2014
Regina Caterers                          40356649   Brooklyn         {building=6409, coord=[-74.00528899999999,40.628886], street=11  Americ           12 07/17/2014
Wendy'S                                  30112340   Brooklyn         {building=469, coord=[-73.961704,40.662942], street=Flatbush Ave Hambur            8 12/29/2014
Wilken'S Fine Food                       40356483   Brooklyn         {building=7114, coord=[-73.9068506,40.6199034], street=Avenue U, Delica           10 05/28/2014
Taste The Tropics Ice Cream              40356731   Brooklyn         {building=1839, coord=[-73.9482609,40.6408271], street=Nostrand  Ice Cr           12 07/13/2014
Wild Asia                                40357217   Bronx            {building=2300, coord=[-73.8786113,40.8502883], street=Southern  Americ           11 05/27/2014
C & C Catering Service                   40357437   Brooklyn         {building=7715, coord=[-73.9973325,40.61174889999999], street=18 Americ            5 04/15/2014
May May Kitchen                          40358429   Brooklyn         {building=1269, coord=[-73.871194,40.6730975], street=Sutter Ave Chines           21 09/15/2014
1 East 66Th Street Kitchen               40359480   Manhattan        {building=1, coord=[-73.96926909999999,40.7685235], street=East  Americ            3 05/06/2014
Carvel Ice Cream                         40360076   Brooklyn         {building=203, coord=[-73.97822040000001,40.6435254], street=Chu Ice Cr            2 02/09/2014
 

Notice: Create nickname on JSON file in CouchDB is different from relational database table, it should follow jpath rule, following is How to create nicknames for CouchDB collections.