©Copyright International Business Machines Corporation 2001. All Rights Reserved.

Introduction

Part 1 of this article introduced the object-relational features in DB2, and gave examples of how to create structured types and how to use those types to create tables for storing structured type objects. In this second part, I will describe how structured-types can be used in column definitions. I also describe how methods can be invoked on objects (stored in typed tables or columns), how transformation functions are used to enable applications to work on these objects, and on the SQLJ and JDBC extensions that will be useful for integrating these database objects into your Java applications.

Back to top

Storing objects in columns

Entity attributes might themselves be good candidates for representation by a structured type. Even something as simple as an address could be a candidate for a structured type. It has structure-a street number, a street name, a city, a state, a zipcode-which could be represented as attributes. And it is reusable. After the type and its methods are defined, it can be used in the many places where addresses are useful:

Create the address type and its methods.

CREATE TYPE Address_t AS
(Number VARCHAR (10),
Street VARCHAR(30),
City VRACHAR(20),
Zip VARCHAR (15)
...)

METHOD distance (address_t)
RETURNS FLOAT
LANGUAGE C
...
METHOD addrstring ()
RETURNS CHAR(70)
LANGUAGE C

...

Create the method bodies. (Note the use of the transform group specification for these external methods. For more information about transforms, see Transforming objects for the application

CREATE METHOD distance(address_t)
FOR Address_t
EXTERNAL NAME 'addrlib!distanceaddr'
TRANSFORM GROUP func_group
...
CREATE METHOD addrstring()
FOR address_t
external name 'addrlib!formataddress'
TRANSFORM GROUP func_group
...

Create the table using the structured type in the column specification.

CREATE TABLE properties
(ID integer,
Address Address_t,
Picture BLOB)
...

Back to top

Inserting and updating column objects

Whenever a user-defined structured type is created, DB2 automatically creates routines for you that help you construct and object, and to observe (retrieve) or mutate (update) its attributes. These routines correspond to gettors and settors in OO programming languages.

• If our type is Address_t, then the constructor function is simply Address_t(). The function returns an instance of type Address_t with all of its attributes set to null.
• There is one observer method for each attribute. For example, the observer method for the street attribute is Address_t .. street() and returns a VARCHAR(30) street name
• There is one mutator method for each attribute. For example, the mutator method for the street attribute is Address_t .. street (VARCHAR(30)) and returns the address with the street attribute updated as specified in the input parameter to the method.

Any subtype for Address_t inherits the above routines, but will also include its own observers and mutators for any additional attributes it has.

These methods, as are all methods on column objects in DB2, are invoked using the double dot syntax (type_instance..method). For example, to insert a new address, use both the constructor function (shown in bold) to construct an empty instance and then the mutator methods to set the attributes to the new values:

INSERT INTO Properties (ID, Address)
VALUES (1224, Address()..street('555 Bailey Avenue')
   ..city('San Jose')
   ..state('CA')
   ..zip('95141');

To update an existing value, the mutator can be used as follows:

UPDATE Properties
   SET Address = Address..street('Barley Avenue')..zip('95000')
   WHERE Address..street LIKE 'Bailey Avenue';
   

Or, an easier way to do the same thing is:

UPDATE Properties
   SET Address..street='Barley Avenue',
   Address..zip ='95000'
   WHERE Address..street LIKE 'Bailey Avenue';

This example shows the use of the observer method for the zip attribute being used in a WHERE clause:

SELECT ID, Picture
   INTO :id, :picture
   FROM properties
   WHERE Address..zip LIKE '95%'

The same invocation can be used on user-defined methods. This example invokes the distance method on an instance of an address:

SELECT ID
INTO :id,
FROM properties P, stores S
WHERE P.Address..distance(S.Address)< 1

This example invokes the addrstring method in the SELECT list:

SELECT ID, Address..addrstring()
FROM Properties
WHERE Address..zip LIKE '95141';

Back to top

Indexing column objects

User-defined index extensions are used to create indexes on structured type columns. Normal B-tree indexes are not adequate for efficient search for complicated domains, because these indexes depend on a "total order" of all data values, which make it easy to do searches like finding all salaries that are greater than 25000. There is no automatic comparison of structured types, so that is why indexes are instead created on behaviors. For example, you can create an index that understands distance and that is how it is possible to efficiently find points that are within a certain distance of another address. In other words, an index extension is your way of transferring semantic knowledge of your data to DB2 so that it can use that information in generating a more efficient query. As an example, index extensions are used by DB2's Spatial Extender to convert coordinate information to a form that can be recognized by DB2.

Invoking methods on objects stored in rows

Methods can be invoked on objects that are stored in rows, such as the objects that are stored in the Person_table hierarchy. All of the examples in this paper for row objects have focused on treating a row of a typed table as separate columns with different values.

However, to invoke a method on an employee, that row object must be turned into an object with encapsulated Attributes.

The DEREF function is how a row is turned into an object. DEREF operates on a REF type column (the object identifier of this table or a referenced table) by grabbing onto the referenced row and turning into an object. By invoking DEREF on the object identifier of the typed table, the returned row is turned into a structured type object with encapsulated attributes.

One of the things you can do with an object is insert it into a column of the same type. The SQL statement at right turns a row from the Person_table hierarchy into an object so that it can be inserted into a structured type colum of the Person type in another table.

You must also use DEREF to invoke a method on the object. Before showing examples of method invocation, let us first add a simple method, written in SQL, to calculate the total compensation for executives. Because this is a simple operation, we can simply write a method in SQL that adds together the observed attributes from salary and bonus.

ALTER TYPE executive ADD METHOD
total_comp ()
RETURNS integer
LANGUAGE SQL
...

CREATE METHOD total_comp ()
RETURNS INTEGER
FOR executive
RETURN
SELF..Salary + SELF..Bonus

Methods can be added to a type by the ALTER TYPE statement.

Here is the CREATE METHOD statement. For methods written in SQL, the method body is specified in the RETURN clause. SELF refers to the implicit first parameter of the method, which is always the type for which the method is specified, in this case, executive. SELF corresponds to "this" in C++ and Java. <

One way to invoke the total_comp method is to use the DEREF function, as shown above:

SELECT Name, DEREF(oid)..total_comp AS
total_comp FROM exec_table;

However, there is a simpler way to dereference an object and invoke the method using the dereference operator (->). If the name on the right side of the arrow is not an attribute of the target type, then it is treated as a method invocation:

SELECT Name, oid->total_comp AS total_comp
FROM exec_table;

The following example shows method invocation in the WHERE clause:

SELECT E. name, E. salary, E.dept->ID, E.dept->table;
FROM Employee_table E
WHERE E.dept->budgetPerPerson( ) > 150000;

Back to top

Additional query enhancements

The following enhancements are useful tools for finding out information about the the type of the object and to control query searches based on type.

Built-in functions to return type information: The built-in functions shown in the following example return the object's dynamic type name, type schema or internal type identifier:

SELECT name, TYPE_NAME(DEREF(oid)) AS typename,
  TYPE_SCHEMA(DEREF(oid)) AS type_schema,
  TYPE_ID (DEREF(oid)) AS type_id
FROM Person_table;

Type predicate: In part one, the use of ONLY in a FROM clause was used to restrict the query results to only the specified table; no subtable rows are included in the result. The type predicate is a more general form of ONLY. To use it, the dynamic type of each object that is returned on the query is compared to the list of types you have specified in the type predicate. The dynamic type of an object is the type of the table into which the object was originally inserted The predicate is only true if the type of the returned object is in that list. For example, the following query returns only those people who are born after 1960 and who are either exactly of type Person, or of type Student or any subtypes of Student.

SELECT *
FROM Person_table
WHERE birthyear > 1960 AND 
      DEREF(oid) IS 
        OF (ONLY Person, Student);

TREAT...AS to downcast a returned type: DB2 enforces type safety on method invocations. That is, a method defined on executives cannot be invoked on regular employees. In the following example, we tell DB2 to assume for the moment that every manager is an executive for the purpose of allowing the method to be invoked on any executives that are present.

SELECT ID, TREAT (deref(manager) AS
executive)..total_comp() AS total_comp FROM dept_table
WHERE DEREF(manager) IS OF ( ONLY executive);

More complex structures

Structured types can be nested within other types. For example, the address type could have additional subtypes, perhaps for business address and for US business addresses. That address type can be imbedded in another type, such as a business card type. The business card type can now be used as the type of a table or as a column in another table.

The following example inserts a row in a table that contains a structured type with a nested structured type attribute:

   INSERT INTO Employee_table (Name, Birthyear, ContactInfo)
   VALUES ('Sailesh', '1975',
     Business_Card_t()..('Chief Programmer',
      BusAddr_t()..(street('555 Bailey Avenue')
      ..city('San Jose')
      ..state('CA')
      ..zip('95141')))

Back to top

Transforming objects for the application

Because structured types can be any arbitrary shape or length, depending on how you define them, the transformation of that type into something that a host language program can recognize is determined by you. In general, this means flattening the object into a character string, BLOB, or CLOB, before handing it over to the application program. After the application has done what it needs to do with the data, the flattened structure must be returned to an object structure again before it is inserted back into the database. These "flattening" and "deflattening" processes are controlled by means of user-defined functions called transform functions. Currently, you define the transformation functions yourself that are tailored to what your application will be expecting. It is likely that default transformations will be available in a later release. After you have defined the transform functions, DB2 will invoke the transformations appropriately, as needed.

Back to top

Application integration

Since 1995, DB2 has continuously enhanced its support in object-relational technology. The focus in these initial releases has been on the new data types, hierarchies and references, and on developing user-defined functions and methods. All of the basic infrastructure is in place to use these complex data types in applications. However, standards have also been developed to provide the necessary layer between DB2 and Java to provide a completely integrated environment between the programming interface and the database. The real value to the application lies in making these data types easy to use with object-oriented programming languages, especially Java. This section briefly describes these standards and what you can do when they are implemented in DB2 (planned to be implemented in a future release).

Better integration with Java through JDBC 2.0 support:

IBM played a major role in designing the JDBC 2.0 feature that allows the materialization of structured types as Java objects.

By simply using the existing result set or prepared statement interfaces, a Java object is populated:

ResultSet rs = stmt.executeQuery(
"SELECT e..addr FROM Employee e");
rs.next( );
Residence addr =
(Residence)rs.getObject(1);

Better integration with Java using SQLJ Part 2:

SQLJ Part 2, an ANSI standard in which IBM was a major developer, allows the use of Java classes to define SQL types, which can then be used as columns in tables or as typed tables. The Java methods become SQL99 methods on the SQL type and those Java methods can be invoked in SQL. Here is a somewhat abbreviated example that shows the SQL address type being implemented by Java methods.

On the Java side...

public class Residence implements 

SQLData { 

     public int door;    

     public String street;  

     public String city;

     public Residence (int d, String s, String c) {

          door =d; 

          street = s; 

          city = c;

       } 


public String printAddress( ) { ...}; 

public void changeResidence(String  adr) { ... // parse and update fields

 ...}


// SQLData implementation  

}

On the SQL side...

CREATE TYPE Address  	

      EXTERNAL NAME 'Residence'

      LANGUAGE JAVA

      USING SQLDATA 	

      AS( number	INTEGER,	                        

          street	VARCHAR(100),	   

          city          VARCHAR(50))

CONSTRUCTOR METHOD Address (d INT, s 

VARCHAR(100),  c VARCHAR(50)) 	

              RETURNS Address

	SELF AS RESULT     

EXTERNAL NAME 'Residence',



METHOD print()  RETURNS  VARCHAR(200)

EXTERNAL NAME 'printAddress', 



METHOD changeAddress (varchar(200)) 	   

      RETURNS  Address 

      SELF AS RESULT   

EXTERNAL NAME  'changeResidence';

With the above mapping in place, you can use the NEW operator to construct a new address:

INSERT INTO employees
VALUES('John Doe', NEW Address('555','Bailey Avenue', 'San Jose'))

Then the address is updated using the changeAddress Java method.

UPDATE employees
SET addr =
addr..changeAddress('1234 Parkway Dr., San Leandro')
WHERE name = 'John Doe'

Back to top

Summary

DB2 leads the way in a standard-compliant implementation of object-relational features. An object-relational infrastructure provides the basis upon which relational database, a proven technology in terms of market acceptance, takes itself into the future, serving web-based applications, content-rich data, and a seamless integration with object-oriented programming languages. The features described in this article include large object types, user-defined distinct types and structured types, user-defined methods and functions, inheritance, path expressions, object views, and index extensions. The planned enhancements to Java integration make the boundary between data and application objects practically seamless. With these enhancements, the scope of applications that can access DB2 data is limited only by the imagination.

Back to top

Checklist of features

Back to top

Trademarks

The following terms are trademarks of the International Business Machines Corporation in the United States and/or other countries:

DB2 DB2 Universal Database IBM

The following terms are trademarks of other companies:

Microsoft, Windows, Windows NT, and the Windows logo are trademarks of Microsoft Corporation in the United States and/or other countries.

Java and all Java-based trademarks and logos are trademarks or registered trademarks of Sun Microsystems, Inc. in the United States, other countries, or both.

©Copyright International Business Machines Corporation 2001. All rights reserved.

Resources

• DB2 Universal Database(TM) V7, Application Development Guide. See in particular the chapter entitled "Object-Relational Programming."
  
  
• DB2 Universal Database V7, SQL Reference.
  
  
• Michael J. Carey, Serge Rielau, Bennet Vance. "Object View Hierarchies in DB2 UDB". EDBT 2000: 478-492,1999
  
  
• Michael J. Carey, Donald D. Chamberlin, Srinivasa Narayanan, Bennet Vance, Doug Doole, Serge Rielau, Richard Swagerman, Nelson Mendonca Mattos. "O-O, What's Happening to DB2?" SIGMOD Conference 1999: 511-512
  
  
• Michael J. Carey, Donald D. Chamberlin, Srinivasa Narayanan, Bennet Vance, Doug Doole, Serge Rielau, Richard Swagerman, Nelson Mendonca Mattos. "O-O, What Have They Done to DB2?" VLDB 1999: 542-553
  
  
• Chen, Weidong et al. "High Level Indexing of User-Defined Types." Proceedings of the 25th VLDB Conference, Edinburgh, Scotland, 1999
  
  
• N. M. Mattos, J. Kleewein, M. Tork Roth, K. Zeidenstein, "From Object-Relational to Federated Databases," Datenbanksysteme in Buro, Technik und Wissenschaft. March 1-3, 1999: 185-209.
  
  

About the author

Kathy Zeidenstein has been with IBM's Silicon Valley Lab since 1987. She started in the DB2 for OS/390 organization, which is where she got her first exposure to object-relational. After she began working on the SQL standards team, she became involved with the more advanced object-relational features in DB2, because much of the language design for that work was taken to the SQL standards committees.

Comments (Undergoing maintenance)

Back to top

Trademarks  |  My developerWorks terms and conditions

Table of contents

• Introduction
• Storing objects in columns
• Inserting and updating column objects
• Indexing column objects
• Additional query enhancements
• Transforming objects for the application
• Application integration
• Summary
• Checklist of features
• Trademarks
• Resources
• About the author
• Comments

Next steps from IBM

• Try: DB2 Enterprise
• Demo: DB2 serving ATOM feed
• Webcast: A smarter way to successful SOA - Information as a trusted strategic asset
• Briefing: Information on Demand
• Buy: DB2 Enterprise

My developerWorks community

Dig deeper into Information Management on developerWorks

DB2 pureScale

See how true transparent scaling architecture can be achieved on distributed platforms

Special offers