Introduction

Large data objects such as images and audio frequently need to be stored in a database. DB2® Universal Database^TM provides specialized data types for storing large data objects. These data types are known as large objects (LOBs). In this article, we'll be exploring the world of LOBs. More specifically, we'll explore LOBs from the perspective of a Java developer. You'll see how you can store and retrieve LOBs in your Java applications.

Back to top

The business scenario

It always helps to apply your learning to a real world example. Accordingly, in this article we'll be pretending that we are a bookstore. We want to be able to present our Web site visitors with a way to retrieve an image of the front cover of our books. Later, you'll see how we can store our book cover images as BLOBs. Also, we want to be able to store book abstracts in the database so the abstracts can be searched for keywords. As you'll see, our data type of choice will be a CLOB. The diagram below will give you an idea of the workflow we'll accomplish in this article.

Back to top

The LOB family of data types

DB2 provides three different types of large objects data types. All of these data types can house up to 2 gigabytes of data:

• CLOB (Character Large Object) - can contain up to 2 gigabytes of character data
• BLOB (Binary Large Object) - can contain up to 2 gigabytes of binary data. This binary data could in essence be anything (e.g., an image, an audio file, etc).
• DBCLOB (Double-Byte Character Large Object) - can contain up to 2 gigabytes of double-byte character data. Note that this data type should only be used if the database you created was configured for double-byte data.

These data types fall under the umbrella of SQL3 data types. As you'll see later in this article, JDBC 2.0 has support for these data types.

Back to top

Don't move it if you don't have to

Moving around large objects in memory can be a resource-taxing operation. Accordingly, DB2 provides features to interact with LOBs that can minimize such movement.

Back to top

Where's my data?

One major interesting point to note about LOBs in DB2 is that LOB values are not stored in a database table. What is actually stored is a descriptor. This descriptor points to the actual location of the LOB. The actual LOB values are stored in tablespaces. Tablespaces are units of physical storage.

Back to top

Locators to represent your LOB values

What is actually housed in a LOB column is not the actual LOB data but in fact a pointer to the LOB data. This pointer is known as a "locator." These so called locators are used to represent your LOB values. When you retrieve data in a ResultSet, you'll be retrieving locators and not the actual LOB values that they represent. As you'll see, you have to explicitly ask for LOB values to be retrieved. In database lingo, this retrieval is known as "materialization."

Back to top

Setting things up

Let's go ahead and hit the DB2 Command Line Processor to perform our LOB experimentation.

We'll start off by creating a database named "LOBDB":

db2 => create database LOBDB

We'll connect to the database using a user name of db2admin and a password of db2admin:

db2 => connect to LOBDB user db2admin using db2admin

For our learning purposes, we'll create a couple of tables... one that will house a BLOB and the other that will house a CLOB respectively:

db2 => create table bookcovers (bookisbn varchar(10) not null, bookcover blob (100K)
not null, primary key(bookisbn))

db2 => create table abstracts (bookisbn varchar(10) not null, bookabstract clob (100K)
not null, primary key(bookisbn))

The 100K values in the statements above are a declaration of the maximum length of the LOBs we want to store. Again, this length can vary from one byte up to 2 gigabytes. This maximum length information is used by your application program to allocate an appropriately sized buffer to hold data as it is materialized.

You can use the following suffixes to represent the number of bytes of length:

K: kilobytes (1,024 bytes)
M: megabytes (1,048,576 bytes)
G: gigabytes (1,073,741,824 bytes)

In both of our tables, our books are differentiated by the primary key of bookisbn, which is a column representation of a book's ISBN number.

Back to top

Compact or not compact?

When creating a table, you can specify the option of COMPACT or NOT COMPACT. If the option of COMPACT is specified, then the LOB data you store will take a minimum amount of space. However, if you perform an update to the LOB column that would increase the size of the LOB stored, then there is a performance penalty. On the other hand, if you specify NOT COMPACT (the default), your LOB values in essence have leeway to grow.

Back to top

Logged or not logged?

If you specify the option of LOGGED, then updates to a LOB column are recorded in the system log. Specifying the option of LOGGED provides the most protection for data that is housed so it can be reconstructed in case of media failure with a forward recovery process. However, this comes at a cost when it comes to disk space (not to mention the time costs associated to write to the disk). If you don't specify one, the option of LOGGED will be chosen by default.

Back to top

Inserting LOBs

Insertion of a BLOB via Java is rather straightforward. Take a look at the code sample below:

 
PreparedStatement preparedStatement =  
      connection.prepareStatement("INSERT INTO BOOKCOVERS VALUES(?,?)"); 
File imageFile = new File("c:\\redbookcover.jpg"); 
InputStream inputStream = new FileInputStream(imageFile); 
preparedStatement.setString(1," 0738425826"); 
preparedStatement.setBinaryStream(2,inputStream,(int)(imageFile.length())); 
preparedStatement.executeUpdate(); 

The short code snippet above takes a file named redbookcover.jpg located in the root of the C drive and stores it in the database. Note how we associate our file with an InputStream. This InputStream object is used to populate the value of the prepared statement standing in for our BLOB column. This code as well as the other code in this article can be found in the project zip file associated with this article.

Insertion of a CLOB is almost identical to the insertion of the BLOB shown above. You can see this in the project file named ClobInsertion.java. As you'll see in the ClobInsertion.java file, we store the abstract of our book in the form of a CLOB. This abstract is grabbed from a text file named redbookabstract.txt.

Back to top

Retrieving LOBs

So we inserted some LOBs. Now how do we retrieve them in Java? This again is a rather straightforward process.

 
PreparedStatement preparedStatement = 
connection.prepareStatement(
   "SELECT BOOKCOVER FROM BOOKCOVERS WHERE BOOKISBN=?"); 
preparedStatement.setString(1, "0738425826"); 
ResultSet resultSet = preparedStatement.executeQuery(); 
while (resultSet.next()) { 
	// materialization of the Blob 
	Blob blob = resultSet.getBlob(1); 
	InputStream inputStream = blob.getBinaryStream(); 
	File fileOutput = new 
	  File("C:\\clonedredbookcover.jpg"); 
	FileOutputStream fo = new
	  FileOutputStream(fileOutput); 
	int c; 
	while ((c = inputStream.read()) != -1) 
	fo.write(c); 
	fo.close(); 
	System.out.println("Blob retrieved"); 

In the code segment above, we execute a prepared statement in which we selected the BLOB we inserted in the code segment earlier. The important thing to note is that the actual BLOB is not materialized until the line:

InputStream inputStream = blob.getBinaryStream();

We use our input stream to store our retrieved BLOB in a file called clonedredbookcover.jpg.

To retrieve CLOBs, much of the syntax is similar to that of retrieving a BLOB. Take a look at the project file ClobRetrieval.java.

The JDBC API provides us with means to do some interesting things with our CLOB.

Check out the code below:

 
Clob clob = resultSet.getClob(1); 
//retrieve the first 200 characters 
String subString = clob.getSubString(1,200); 
System.out.println("First 200 characters of Clob (Book Abstract): + subString); 
// find the position of a given String in the Clob 
long positionOfString = clob.position("tool",1); 
System.out.println("Position of SubString: + positionOfString); 

In the code above, we use the getSubString method to grab a portion of our stored CLOB. Such a function might be used by client application programs that might want just a portion of our book abstract. The use of the position method is also demonstrated. If a given string is present within our CLOB, our method will return a long that represents the starting position of the string being sought; if not, a -1 will be returned. The position method of the CLOB interface works without materializing the stored data.

Actual materialization of the CLOB occurs with the code:

 
File fileOutput = ;new; 
  File("C:\\clonedredbookabstract.txt"); 
FileOutputStream fo = ;new; FileOutputStream(fileOutput); 
InputStream is = clob.getAsciiStream(); 
int c; 
while; ((c = is.read()) != -1) 
   fo.write(c); 
fo.close(); 

In the snippet above, we materialize our stored CLOB to a new file named clonedredbookabstract.txt. We do this by using the getAsciiStream method of our CLOB. Just like we did when retrieving our BLOB, we assign the stream to an InputStream which we then read from and in turn write what we read to our FileOutputStream.

Back to top

Conclusion

In this article, you've seen how DB2 UDB provides facilities for storing large data objects. Using the JDBC 2.0 API, you can interact with LOBs via Java. This article has introduced you to the use of the JDBC API. We did so by introducing you to a real world example of a bookstore that needed to store book cover images and book abstracts. You should now feel comfortable in the use of LOBs in your own Java applications.

Back to top

Acknowledgement

I would like to thank Robert Indrigo for his help in reviewing this article.

Back to top

Disclaimer

This article contains sample code. IBM grants you ("Licensee") a non-exclusive, royalty free, license to use this sample code. However, the sample code is provided as-is and without any warranties, whether EXPRESS OR IMPLIED, INCLUDING ANY IMPLIED WARRANTY OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE OR NON-INFRINGEMENT. IBM AND ITS LICENSORS SHALL NOT BE LIABLE FOR ANY DAMAGES SUFFERED BY LICENSEE THAT RESULT FROM YOUR USE OF THE SOFTWARE. IN NO EVENT WILL IBM OR ITS LICENSORS BE LIABLE FOR ANY LOST REVENUE, PROFIT OR DATA, OR FOR DIRECT, INDIRECT, SPECIAL, CONSEQUENTIAL, INCIDENTAL OR PUNITIVE DAMAGES, HOWEVER CAUSED AND REGARDLESS OF THE THEORY OF LIABILITY, ARISING OUT OF THE USE OF OR INABILITY TO USE SOFTWARE, EVEN IF IBM HAS BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES.

IBM, DB2, and DB2 Universal Database are trademarks or registered trademarks of IBM Corporation in the United States, other countries, or both.

Windows is a registered trademark of Microsoft Corporation in the United States, other countries, or both.

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.

UNIX is a registered trademark of The Open Group in the United States and other countries.

Other company, product, and service names may be trademarks or service marks of others.

IBM copyright and trademark information

Back to top

Download

Name	Size	Download method
lobprojectfiles.zip	28KB	FTP | HTTP

Information about download methods

About the author

Comments

Back to top

Trademarks  |  My developerWorks terms and conditions

Table of contents

• Introduction
• The business scenario
• The LOB family of data types
• Don't move it if you don't have to
• Where's my data?
• Locators to represent your LOB values
• Setting things up
• Compact or not compact?
• Logged or not logged?
• Inserting LOBs
• Retrieving LOBs
• Conclusion
• Acknowledgement
• Disclaimer
• Download
• 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