Advanced Database Feature Guide


Using Large Objects

The term large object and the generic acronym LOB are used to refer to any type of large object. There are three LOB data types: Binary Large Object (BLOB), Character Large Object (CLOB), and Double-Byte Character Large Object (DBCLOB). Instances of these LOB data types can be obtained by executing the following code fragments: UtyDB2BlobType precision: <integer>, UtyDB2ClobType precision: <integer>, and UtyDB2DBClobType precision: <integer> . The substitution parameter <integer> represents the maximum amount of data that can be retrieved/sent for a single instance of that type.

There are many cases where an application needs to select a large object value and operate on pieces of it, but it does not need or want the entire value to be transferred from the database server into application memory. In these cases, the application can reference an individual LOB value via a large object locator (LOB locator).

A LOB locator is a mechanism that allows an application program to manipulate a large object value in an efficient, random access fashion. A LOB locator is a run time concept. It is not a persistent type and it is not stored in the database. It is a mechanism used to refer to a LOB value during a transaction and it does not persist beyond the transaction in which it was created. The three LOB locator types can be created using the following code fragments: UtyDB2BlobLocatorType new, UtyDB2DB2ClobLocatorType new, and UtyDB2DBClobLocatorType new. These types are used to enable transfer of LOB locator values to and from the database server.

A LOB locator is a simple token value that represents a single LOB value. A locator is not a reference to a column or row, rather it is created to reference a large object value. No operation that can be performed on a LOB locator will effect the original value stored in the database.

To obtain a LOB Locator value, the connection must be using manual commit mode and the result table, which is to retrieve the LOB values must be configured to use a LOB Locator type rather then the default column type. This may be done in one of two ways. The simplest way is to manually change the data type of the column by sending the result table the dataTypeAt:put: method, where the index may refer to either a column index or a column name. The second way involves the use of the SQL descriptor’s column types attribute. This attribute, if used, but be set with a dictionary of substitute data types that are to be used in place of the named column’s default data type. The LOB locator example below uses the dataTypeAt:put: method.

Once an application has obtained a LOB locator, the following messages may be sent to it.

Size Answers the length of the value that is represented by the LOB locator.
IndexOfSubCollection:startingAt: Answer the position of a sub-collection within the receiver’s LOB value.
copyFrom:to: Answer a sub-collection of the receiver’s LOB value.
Value Answer the entire LOB value referenced by the receiver.

LOB values also provide an efficient means of moving data from one column of a table at the server to another column (of the same or different table) without having to pull the data first into application memory and then sending it back to the server. For example, the following INSERT statement inserts a LOB value that is a concatenation of two LOB values as represented by their LOB locators:

INSERT INTO lobtable values (CAST ? as CLOB(4K) || CAST ? AS CLOB(5K)) 

The locator can be explicitly freed before the end of a transaction by sending it the free message.

Note:Not all DB2 servers currently have Large Object support.

LOB Locator Example

| conn rslt locator index intetests | 
conn := ... 
"Setting the auto commit mode to false, prevents the locator from being 
closed when the statement is automatically committed." 
conn autoCommit: false. 
"Execute the SELECT statement." 
rslt := 
conn 
executeSql: 
'SELECT resume FROM emp_resume ', 
'WHERE empno = ? AND resume_format = ''ascii''' 
withParameterTypes: (Array with: (UtyDB2CharType precision: 5)) 
withParameterValues: (Array with: empno). 
"To use a different locator type, we must explicitly override the 
default LOB data type prior to accessing the result." 
rslt dataTypeAt: 1 put: (UtyDB2ClobLocatorType new). 
"Retrieve the locator object from the result table." 
locator := rslt first values first. 
"Locate the position of the 'Interests' sub-string in the CLOB." 
index := locator indexOfSubCollection: 'Interests' startingAt: 1. 
"Copy from the start of the interests section until the end 
into application memory." 
interests := locator copyFrom: index to: (locator size). 
locator close."Locators are closed (freed) automatically when 
the connection is committed. 
 


[ Top of Page | Previous Page | Next Page | Table of Contents | Index ]