Wednesday Dec 10, 2008

Java DB as a service on OpenSolaris 2008.11

The recently released OpenSolaris 2008.11 comes with Java DB 10.4.2.1. One of the new features compared to the Java DB version in OpenSolaris 2008.05 is support for SMF (Service Management Facility). Now it's very easy to start a Java DB Network Server and make it run as a service. SMF takes care of starting and stopping the server when the computer is booted or shut down. It will even attempt to restart the server process if it crashes. To enable the Java DB service, make sure the Java DB package is installed and then enable the service with svcadm(1M):

kah@opensolaris:~ % pfexec pkg install SUNWjavadb
...
kah@opensolaris:~ % svcadm enable javadb

Now check the status of the service. It should be online:

kah@opensolaris:~ % svcs -xv javadb
svc:/application/database/javadb:default (Java DB)
 State: online since Wed Dec 10 14:35:53 2008
   See: http://developers.sun.com/javadb/
   See: /var/svc/log/application-database-javadb:default.log
Impact: None.

Configuration options may be changed with svccfg(1M). For example, if you want the Java process in which the server is running to be started with -server -Xmx1024M, do this:

kah@opensolaris:~ % svccfg
svc:> select javadb
svc:/application/database/javadb> setprop javadb/java_args=("-server" "-Xmx1024M")
svc:/application/database/javadb> quit
kah@opensolaris:~ % svcadm refresh javadb
kah@opensolaris:~ % svcadm restart javadb

And finally, use ij to connect to the server:

kah@opensolaris:~ % java -jar /opt/SUNWjavadb/lib/derbyrun.jar ij
ij version 10.4
ij> connect 'jdbc:derby://localhost:1527/MyDB;create=true';
ij> select count(\*) from sys.systables;
1          
-----------
20         

1 row selected
ij> 

Wednesday Sep 10, 2008

Memcached UDF for Java DB on OpenSolaris

Trond Norbye recently blogged about how he installed Memcached user-defined functions for MySQL on OpenSolaris. Of course, I told him I could do the same thing in Java DB in less than half an hour, so now he won't stop nagging me until I can show him the code. So, here's how you can get Memcached user-defined functions for Java DB on OpenSolaris:

First, you need to make sure you have Java DB and Memcached installed. The versions in OpenSolaris should work fine. Just install them from pkg.opensolaris.org like this:

kah@tecra:~ % pfexec pkg install SUNWjavadb SUNWmemcached SUNWmemcached-java 
DOWNLOAD                                    PKGS       FILES     XFER (MB)
Completed                                    3/3       61/61     0.28/0.28 

PHASE                                        ACTIONS
Install Phase                                118/118 
PHASE                                          ITEMS
Reading Existing Index                           8/8 
Indexing Packages                                3/3

Next, you need a Java class with a couple of static methods that allow you to manipulate data on Memcached servers. I wrote four very simple methods, init(), set(), get() and delete(), to get access to the basic functionality:

import com.danga.MemCached.Logger;
import com.danga.MemCached.MemCachedClient;
import com.danga.MemCached.SockIOPool;

public class MemcachedUDF {
    public static void init(String servers) {
        SockIOPool pool = SockIOPool.getInstance();
        pool.setServers(servers.split(","));
        pool.initialize();
        // Make MemCachedClient less chatty
        Logger.getLogger(MemCachedClient.class.getName(), Logger.LEVEL_WARN);
    }
    public static void set(String key, String value) {
        MemCachedClient client = new MemCachedClient();
        client.set(key, value);
    }
    public static String get(String key) {
        MemCachedClient client = new MemCachedClient();
        return (String) client.get(key);
    }
    public static void delete(String key) {
        MemCachedClient client = new MemCachedClient();
        client.delete(key);
    }
}

Then compile the class:

kah@tecra:~/src % javac -classpath /usr/share/lib/java/java_memcached-release_2.0.1.jar MemcachedUDF.java

Finally, Java DB must be told how to call the methods. Put the required jar files and the directory where the MemcachedUDF class is located in your CLASSPATH variable. Then start the IJ client and execute CREATE FUNCTION and CREATE PROCEDURE statements to make the methods accessible from SQL.

kah@tecra:~/src % export CLASSPATH=/opt/SUNWjavadb/lib/derbyrun.jar:/usr/share/lib/java/java_memcached-release_2.0.1.jar:$HOME/src
kah@tecra:~/src % java org.apache.derby.tools.ij
ij version 10.3
ij> connect 'jdbc:derby:testdb;create=true';
ij> create procedure memcached_init(servers varchar(32672))
       language java parameter style java
       external name 'MemcachedUDF.init';
0 rows inserted/updated/deleted
ij> create procedure memcached_set(k varchar(32672), v varchar(32672))
       language java parameter style java
       external name 'MemcachedUDF.set';
0 rows inserted/updated/deleted
ij> create function memcached_get(k varchar(32672)) returns varchar(32672)
       language java parameter style java
       external name 'MemcachedUDF.get';
0 rows inserted/updated/deleted
ij> create procedure memcached_delete(k varchar(32672))
       language java parameter style java
       external name 'MemcachedUDF.delete';
0 rows inserted/updated/deleted

There! The UDFs have been defined, and are ready to be used. To test them, we also need a Memcached server to connect to. Simply execute the following command in another terminal window:

kah@tecra:~ % /usr/lib/memcached -p 12345 -l localhost

Let's see how the functions work by looking at an example. The SQL statements below initialize the UDFs and create a table with triggers that automatically update the cache as the contents of the table change.

ij> call memcached_init('localhost:12345');
0 rows inserted/updated/deleted
ij> create table my_table(id varchar(20) primary key, x varchar(20));
0 rows inserted/updated/deleted
ij> create trigger insert_trigger after insert on my_table
       referencing new as n
       for each row
       call memcached_set(n.id, n.x);
0 rows inserted/updated/deleted
ij> create trigger update_trigger after update of x on my_table
       referencing new as n
       for each row
       call memcached_set(n.id, n.x);
0 rows inserted/updated/deleted
ij> create trigger delete_trigger after delete on my_table
       referencing old as o
       for each row
       call memcached_delete(o.id);
0 rows inserted/updated/deleted

Now, if you insert a couple of rows into the table, the values will be readily available in the cache:

ij> insert into my_table values ('one', 'First row'), ('two', 'Second row');
2 rows inserted/updated/deleted
ij> values memcached_get('one'), memcached_get('two');
1
--------------------
First row
Second row

2 rows selected

Also, if you update or delete a row, the changes will be reflected in the cache:

ij> update my_table set x = 'First row - updated' where id = 'one';
1 row inserted/updated/deleted
ij> delete from my_table where id = 'two';
1 row inserted/updated/deleted
ij> values memcached_get('one'), memcached_get('two');
1
--------------------
First row - updated
NULL

2 rows selected

See, it works! :)

About

kah

Search

Categories
Archives
« April 2014
SunMonTueWedThuFriSat
  
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
   
       
Today