Archive

Archive for the ‘DB2’ Category

Writing a Google Contacts UDF for DB2 in Java

April 27, 2010 4 comments

Introduction

User defined functions (UDF for short) are a very powerful feature of DB2. They allow you to extend the set of functions available for use in SQL commands. Table functions are one of the three possible types of UDF’s. They allow the creation of “fictive” tables. Table UDF’s can be written in the SQL procedure language or in an external programming language. When they are written in the SQL procedure language, they are similar to views; the only difference is that they support the pass-through of parameters.

External table UDF’s on the other hand are much more powerful. They make it possible to integrate all sorts of data into DB2 and represent it as a database table to the database users. For example: live stock data retrieved from a web service, the server’s file system, …

In this example, I will demonstrate how to create a table UDF in Java that allows querying the Google Contacts API. Writing a DB2 UDF in Java has a number of advantages:

  • Independent of the underlying server OS. For example: create and debug the UDF on DB2 for Windows and deploy it on a Z/OS mainframe.
  • Ease of development.

But if performance is very crucial, you should implement the UDF in C(++) and run it in unfenced mode. UDF’s in unfenced mode are allowed to run inside the DB2 process whereas otherwise they run in a separate process. The downside is that improperly written UDF’s in unfenced mode can take down the whole database. Unfenced UDF’s can only be written in C(++).

Implementation

Writing a Table UDF in Java is straightforward. First add the db2jcc.jar to your classpath. This jar file can be found in the DB2 installation folder. Next, we extend from the class UDF:

public class GoogleContactsUDF extends UDF

After that we can define our function itself:

public void retrieveContacts(String user, String pass, String rFirstName, String rLastName, String rNickName, String rEmail, String rPhone) throws Exception {

	switch (getCallType()) {
		// handle the different types of calls to the UDF
	}

}

In the function we need to check the type of call to the UDF. According to our current call, our actions will be different. 5 different calls are possible:

  • SQLUDF_TF_FIRST
  • SQLUDF_TF_OPEN
  • SQLUDF_TF_FETCH
  • SQLUDF_TF_CLOSE
  • SQLUDF_TF_FINAL

The five possible states correspond with the possible states that a SQL cursor can be in. For more information about these states, you should read IBM’s help manuals that can be found online on: http://publib.boulder.ibm.com/infocenter/iseries/v5r3/index.jsp?topic=/rzaha/udftable.htm.

The initialization of the table must be performed during the SQLUDF_TF_FIRST call. The open, fetch and close calls correspond to the actions that can be performed on a SQL cursor. During the final call, resources hold by the UDF can be released.

For this example we will retrieve the Google contacts during the SQLUDF_TF_FIRST call. This is very straightforward to implement thanks to the Google Contacts API that is available. We must first authenticate to the Google servers. Therefore we use the 2 arguments of our table UDF. If the username and/or password are not correct, we return an error to DB2:

switch (getCallType()) {
	case SQLUDF_TF_FIRST:
		// do initialization for the whole statement
		// (the statement may invoke tableUDF more than once)
		myService = new ContactsService("GoogleContacts-DB2-UDF");
		myService.setUserCredentials(user, pass);
		try {
			retrieveContacts();
		}
		catch(GoogleService.InvalidCredentialsException e) {
			setSQLstate("75001");
			setSQLmessage(e.getMessage());
		}
		catch(Exception e) {
			setSQLstate("75002");
			setSQLmessage(e.getMessage());
		}
		myService = null; //relase ContactsService

		break;

After that we can retrieve all the contacts and keep them in memory:

	private void retrieveContacts() throws Exception {
		contacts = new ArrayList<GoogleContact>();

		String myContactsId = getMyContactsGroupId();

		// Request the feed
		URL feedUrl = new URL("http://www.google.com/m8/feeds/contacts/default/full");
		Query query = new Query(feedUrl);
		query.setMaxResults(200);
		query.setStringCustomParameter("group", myContactsId);
		ContactFeed resultFeed = myService.getFeed(query, ContactFeed.class);

		for (int i = 0; i < resultFeed.getEntries().size(); i++) {
		    ContactEntry entry = resultFeed.getEntries().get(i);

		    String first = null;
		    String last = null;
		    String nick = null;
		    String email = null;
		    String phone = null;
		    boolean firstNotNull = false;
		    boolean lastNotNull = false;
		    boolean nickNotNull = false;
		    boolean emailNotNull = false;
		    boolean phoneNotNull = false;

		    if(entry.hasName()) {
		    	Name name = entry.getName();

		    	if(name.hasGivenName()) {
		    		first = name.getGivenName().getValue();
		    		firstNotNull = true;
		    	}

		    	if(name.hasFamilyName()) {
		    		last = name.getFamilyName().getValue();
		    		lastNotNull = true;
		    	}

		    	if(name.hasFullName()) {
		    		nick = name.getFullName().getValue();
		    		nickNotNull = true;
		    	}
		    }

		    if(entry.hasEmailAddresses()) {
		    	email = entry.getEmailAddresses().get(0).getAddress();
		    	emailNotNull = true;
		    }

		    if(entry.hasPhoneNumbers()) {
		    	phone = entry.getPhoneNumbers().get(0).getPhoneNumber();
		    	phoneNotNull = true;
		    }

		    contacts.add(new GoogleContact(first, firstNotNull,
		    		                       last, lastNotNull,
		    		                       nick, nickNotNull,
		    		                       email, emailNotNull,
		    		                       phone, phoneNotNull));
		}
	}

	private String getMyContactsGroupId() throws Exception {
		URL feedUrl = new URL("http://www.google.com/m8/feeds/groups/default/full");
		ContactGroupFeed resultFeed = myService.getFeed(feedUrl, ContactGroupFeed.class);

		for (int i = 0; i < resultFeed.getEntries().size(); i++) {
			ContactGroupEntry groupEntry = resultFeed.getEntries().get(i);

			if(groupEntry.getTitle().getPlainText().equals(CONTACTS))
				return groupEntry.getId();
		}

		throw new Exception("The 'My Contacts' systemgroup could not be found");
	}

During the open call we (re)set the current row number to first row:

case SQLUDF_TF_OPEN:
	row = 0;
	break;

During the fetch call we pass the next record to DB2 or we return and +200 SQL state code to warn DB2 that no more rows are available for retrieval. The values for each column is set by using the “set” function that is part of the class UDF that we have extended. The column number of the first column is always 1 + <number of arguments>

case SQLUDF_TF_FETCH:
	if(row >= contacts.size()) {
		// Set end-of-file signal and return
		setSQLstate ("02000");
	}
	else {
		// Set the current output row and increment the row number
		GoogleContact contact = contacts.get(row);

		if(contact.hasFirstName())
			set(3, contact.getFirstName());
		if(contact.hasLastName())
			set(4, contact.getLastName());
		if(contact.hasNickName())
			set(5, contact.getNickName());
		if(contact.hasEmail())
			set(6, contact.getEmail());
		if(contact.hasPhone())
			set(7, contact.getPhone());
		row++;
	}
	break;

In this example we must not perform a clean up of resources. Therefore we don’t need to do anything during the close and final calls.

We are now almost finished the only thing that we still need to do is registering our table UDF in DB2. To do this, we need the execute the following SQL command’s:

CALL SQLJ.INSTALL_JAR('file:C:\lib\gdata\java\lib\gdata-core-1.0.jar', 'gdata-core');
CALL SQLJ.INSTALL_JAR('file:C:\lib\gdata\java\lib\gdata-contacts-3.0.jar', 'gdata-contacts');
CALL SQLJ.INSTALL_JAR('file:C:\lib\gdata\java\lib\gdata-client-1.0.jar', 'gdata-client');
CALL SQLJ.INSTALL_JAR('file:C:\lib\google-collect\google-collect-1.0.jar', 'google-collect');

CREATE FUNCTION GoogleContacts(user varchar(80), pass varchar(30))
RETURNS TABLE (firstname VARCHAR(80), lastname VARCHAR(80), nickname VARCHAR(160), email VARCHAR(80), phone VARCHAR(80))
EXTERNAL NAME 'derycke.GoogleContactsUDF.retrieveContacts'
LANGUAGE JAVA
PARAMETER STYLE DB2GENERAL
FENCED
NO SQL
DISALLOW PARALLEL
FINAL CALL;

Because this article was meant as an introduction, I will not explain the options used for configuring the UDF. But you will need to replace the file path to the google API’s with the correct path for your system. You will also need to copy the compiled *.class file to the folder for UDF’s. On my system this is: C:\ProgramData\IBM\DB2\DB2COPY1\function. For more information about all this, you should best consult the online IBM DB2 manuals.

The table UDF in action

The hard work is done. We can now retrieve our online contacts with SQL commands; for example:

SELECT firstname, lastname, nickname, email, phone
FROM TABLE(GoogleContacts(‘<username>’, ‘<password>’))

Conclusion

I hope that this short introduction to table UDF’s was instructive and clear to understand. When I started learning about UDF’s, I found it a pity that there were not much code samples available on the Internet.

UDF’s are an advanced feature of DB2; this article outlines only a few of the possible techniques. If you want more information about this subject I suggest you to go check out the DB2 SDK code samples and documentation.

If you have questions or suggestions, feel free to leave a message!

Source code

The source code can be downloaded from rapidshare: http://rapidshare.com/files/380908245/google_contacts_udf.zip.html

Advertisements
Categories: DB2, Java