A DB2 UDB still image extender

Document options Document options requiring JavaScript are not displayed Sample code New site feature Check out our new article design and features. Tell us what you think. Rate this page Help us improve this content

Level: Intermediate

Knut Stolze (stolze@de.ibm.com), Information Integration Development, IBM Germany

07 Apr 2005

Integrate the latest image-processing capabilities with your images stored in IBM® DB2® Universal Database™ (DB2 UDB). This article shows you a method for integrating a third-party image processing library such as ImageMagick™ with DB2 UDB to provide enhanced functionality to manage and process still images directly inside a DB2 UDB database system. It describes the steps to implement the user-defined functions that connect DB2 to the library and shows how to compile and link everything together. In the end, you will have a completed a still image extension for DB2 based on that library. Code samples included.

Introduction

Many applications need to store and manage digital images inside a database. DB2 UDB offers the DB2 Image Extender as part of the DB2 UDB Audio, Image, and Video Extenders package [2]. Although that extender provides a certain set of image manipulation functions, additional functions are often desired, or the existing functions could be more general, such as rotation by arbitrary angles. The existing extender was originally based on DB2 UDB Version 5, and many new features have been added to the database engine since then. Unfortunately, the extenders package has not sufficiently exploited these new features.

This article demonstrates how the up-to-date image manipulation library ImageMagick can be coupled with DB2 UDB to provide a new extender to manage still images in a way that is state-of-the-art. It shows how you can implement the necessary user-defined functions (UDFs), register them in your database, and bring it all together with the ImageMagick library to reach the final goal of your very own image extender for DB2 UDB [5, 6].

The "extender" described in this article provides basic functionality to manipulate images that are stored in the database as binary large objects (BLOBs). The images can be rotated by arbitrary angles, scaled, resized, inverted, sheared, and manipulated in various other ways in shape, color, or content. The extender also offers a set of functions to retrieve image-specific attributes such as the height or width (measured in pixels) or the resolution in the X or Y dimension. In addition to the BLOB-based interface, the article also describes how you can implement a specific data type along the lines of the SQL/MM Part 5: Still Image standard [3].

The next sections give a brief overview of ImageMagick, followed by some sample code for selected image-related UDFs. You'll see how to compile and link this code together with ImageMagick into a shared library that can be handled by DB2. The SQL interface is described afterwards, so you can see the distinction between a simple interface that solely deals with BLOBs and an interface that takes advantage of DB2's object-relational features, in particular structured types, to encapsulate the image functionality.

Back to top

An overview of ImageMagick

ImageMagick [1] is a collection of libraries and tools that allow you to read, write, and manipulate still images in many different formats. Currently, more than 89 image formats are supported; for example, TIFF, JPEG, PNG, PDF, and GIF. An image can be resized, rotated, or sharpened, and the number of colors can be reduced or special effects can be added. ImageMagick offers a wide variety of interfaces, ranging from command line tools to interfaces for programming languages like C/C++, Perl, Java™, PHP and others. The extension for DB2 that is presented in this article takes advantage of the ImageMagick library and its C/C++ interfaces to connect it to DB2. The sample code that you can find in the Download section is based on version 6.1.9 of ImageMagick. If you want to build based on a different version, you might have to apply some modifications to the code, as the interfaces may have changed.

Back to top

Sample code for selected UDFs

Let's start with a quick overview of the C/C++ code for some of the UDFs you'll be implementing [6]. Note that all of the other functions use a very similar implementation. The main difference is usually a different ImageMagick function that is invoked.

Before I start describing the details of the actual UDFs, we'll look at some support functions that are used in the majority of the UDFs for things like error handling, or to retrieve the image data from DB2 using BLOB locators, or to write the result to another BLOB locator.

Support functions

All UDFs need some infrastructure to manage errors. The error handling implemented for our still image extender handles all ImageMagick errors. The error handling is encapsulated into the class IexError. That way, the localization of error messages can easily be added. The class also provides a single point of control for all errors and provides the necessary infrastructure to trace error information, which helps locating unexpected errors in the production environment. In addition to the class IexError we defined a set of macros named IEX_SET_ERROR* that are used to set new error information.

The second set of support functions can be found in the file IexUdfUtils.cpp. It is concerned with the management of the scratchpad and handles the transformation of image data from and to DB2's BLOB locators. The function IexReadImageToScratchPad retrieves the image data from an input BLOB locator, constructs the specific ImageMagick object in memory, and stores a pointer to that object in the data structure that is mapped onto the scratchpad. Likewise, the function IexWriteImageToLocator takes an ImageMagick object as input parameter, converts it to a binary stream, and writes that stream to the output BLOB locator. We will not discuss each line of code of the support functions; instead we will focus on the image-specific functions themselves.

The function SI_rotate

Each UDF is implemented as a separate C++ function. It takes the function-specific parameters as input (like the target format for a format conversion operation), a BLOB locator of the image to operator on and a BLOB locator for the resulting image as input parameters. Additionally, the usual null indicators and other mandatory parameters for the UDFs need to be supplied for the functions' signatures. Please note that we use locators in order to improve performance during runtime. For example, detection of the image-secific attributes often requires only the header of the image to be processed, and it is not necessary to pass the complete image data from DB2 to the UDF.

Call type & scratchpad parameters All of the UDFs will be declared with the options FINAL CALL and SCRATCHPAD. Thus, a memory area is available to carry information or pointers to other memory areas from one invocation of the function to the next. It provides the necessary support for performance improvements in the event that more than a single image is processed by a function, which is often the case for SQL statements, given that SQL is a set-oriented query language.

In Listing 1 you can see the code for the UDF SI_rotate. It serves as a representative for all other UDFs that manipulate the content of an image, and all of them are very similar. Right at the beginning of the function, after the function signature, starts the initialization of function-internal variables like the error object, ImageMagick-related variables, the pointer to the scratchpad to impose a structure on it, and the null indicator for the resulting image locator. The parameter angle that influences the image manipulation operation, e.g., defines how for the image is to be rotated, is verified and if necessary normalized to present an angle in the interval of [0, 360] degrees.

The next step is the actual processing. The support function IexReadImageToScratchPad (set in italics in the listing) is called to retrieve the image from the BLOB locator, construct the data structure required by ImageMagick and a pointer to that data structure is placed on the scratchpad. If this is not the first call made to the function, a previously allocated data structure might be reused. The rotation itself is done using the ImageMagick function RotateImage (set in bold in the code listing below). The result is placed again in the ImageMagick-specific data structure, which needs to be converted to a BLOB and passed to DB2 via a locator. This is done using the support function IexWriteImageToLocator (also set in italics).

/** Rotate the image.
 *
 * The given image is rotated using the ImageMagick function RotateImage().
 * The angle needs to be specified in degrees. The angle is taken modulo 360
 * degrees; in other words, angles larger than +360 degrees and smaller than
 * -360 degrees are accepted.
 *
 * Positive angles cause the image to be rotated counter-clockwise, and
 * negative angles rotate the image clockwise.
 *
 * NULL is returned if the given image is NULL. If the specified angle is
 * NULL, then the image is returned unchanged.
 */
IEX_EXTERNC void SQL_API_FN IexRotateImage(
        // input: locator to source image
        SQLUDF_LOCATOR *sourceLocator,
        // input: angle of the rotation
        SQLUDF_DOUBLE *angle,
        // output: locator to target image
        SQLUDF_LOCATOR *targetLocator,
        // null indicators
        SQLUDF_NULLIND *sourceLocator_ind, SQLUDF_NULLIND *angle_ind,
        SQLUDF_NULLIND *targetLocator_ind,
        SQLUDF_TRAIL_ARGS_ALL)
{
    int rc = IEX_SUCCESS;
    IexError error; 
    Image *result = NULL;
    ExceptionInfo exception;
    GetExceptionInfo(&exception);

    // we assume NULL result
    *targetLocator_ind = -1;
    
    // map the scratchpad
    struct scratchMap *scratch = (struct scratchMap *)SQLUDF_SCRAT->data;
    
    // clean up when the SQL statement is finished
    if (SQLUDF_CALLT == SQLUDF_FINAL_CALL) {
        goto cleanup;
    }
    
    // normalize the angle and test if we actually have something to do
    if (SQLUDF_NULL(angle_ind)) {
        *angle = 0.0;
    }
    *angle = fmod(*angle, 360);
    if (*angle == 0.0) {
        *targetLocator = *sourceLocator;
        *targetLocator_ind = 0;
        goto cleanup;
    }
    
    // read the image data
    rc = IexReadImageToScratchPad(sourceLocator, *sourceLocator_ind,
            scratch, SQLUDF_CALLT, error);
    if (rc || !scratch->image) {
        goto cleanup;
    }
    
    // rotate the image
    result = RotateImage(scratch->image, *angle, &exception);
    if (!result || IEX_HAVE_MAGICK_EXCEPTION(exception)) {
        IEX_SET_MAGICK_ERROR(exception);
        goto cleanup;
    }
    if (IEX_HAVE_MAGICK_EXCEPTION(result->exception)) {
        IEX_SET_MAGICK_ERROR(result->exception);
        goto cleanup;
    }
    
    // write the result to the target locator
    rc = IexWriteImageToLocator(result, targetLocator, error);
    if (rc) {
        goto cleanup;
    }
    *targetLocator_ind = 0;
    
  cleanup: 
    DestroyExceptionInfo(&exception);
    if (result) {
        DestroyImage(result);
    }
    IEX_COMMON_CLEANUP;
}

The function SI_getHeight

Our still image extension supports, besides the actual image manipulation operations, a set of functions to retrieve certain properties of an image. For example, each image has a height, measured in the number of pixels. The height is equivalent to the number of rows in the image. The C++ source code for the SI_getHeight UDF is shown in Listing 2.

ImageMagick comes with the function PingBlob. That function only reads the header of an image and extracts some attributes like the height, width, color space and so on. Pinging the image has the huge advantage that we only need to access a fraction of the image's data; that is, only the header.

A common function InitializeScratchWithPing (set in italics in the listing below) is used to read only the beginning portion of an image from the BLOB locator and to call the ImageMagick function PingBlob to ping the image. Of course, you do not know how big the header really is, and the first portion selected from the locator might have been too small. So if the pinging failed, choose a bigger portion and try the ping again. If that fails again, we give up and retrieve the complete image data to construct the ImageMagick object. Luckily, this final resort is rarely needed in practice.

With the ImageMagick object available on the scratchpad, you can simply access the ImageMagick object and collect the information about the image's height. This is shown in bold in the listing.

/** Get the image height.
 *
 * Get the image height from the BLOB (locator) provided as input parameter.
 */
IEX_EXTERNC
void SQL_API_FN IexGetImageHeight(
        // input: locator to image
        SQLUDF_LOCATOR *imageLocator,
        // output: height of the image
        SQLUDF_INTEGER *imageHeight,

        // null indicators
        SQLUDF_NULLIND *imageLocator_ind,
        SQLUDF_NULLIND *imageHeight_ind,
        SQLUDF_TRAIL_ARGS_ALL)
{
    int rc = IEX_SUCCESS;
    IexError error;

    // we assume NULL result
    *imageHeight_ind = -1;

    // map the scratchpad
    struct scratchMap *scratch = (struct scratchMap *)SQLUDF_SCRAT->data;
    rc = InitializeScratchWithPing(imageLocator, *imageLocator_ind, scratch,
            SQLUDF_CALLT, error);
    if (rc || !scratch->image) {
        goto cleanup;
    }

    // we got a result; set output parameter and null indicator
    *imageHeight = scratch->image->magick_rows;
    *imageHeight_ind = 0;

 cleanup:
    IEX_COMMON_CLEANUP;
}

Back to top

Building the functions

This section explains the steps to configure, compile, and link our code and the ImageMagick library. Once the ImageMagick library is installed on your system, you can proceed to compile the UDFs and link them into a shared library so that DB2 can invoke those UDFs when processing SQL statements inside the database. Or course, you can skip the first step if you already have ImageMagick installed. This is often the case on Linux systems where you merely have to install the ImageMagick package. Please make sure that the necessary header files - especially the file "magick/api.h" - are available; otherwise, you will not be able to successfully compile the UDF source code.

Building ImageMagick

Once you have downloaded and unpacked the source code of ImageMagick, you will find a file named INSTALL in the ImageMagick directory. That file describes in detail the process and requirements for the compilation and installation of ImageMagick on your system. Please follow the steps outlined there because that information is updated for every new version of ImageMagick.

ImageMagick relies on various different libraries to handle certain image formats. For example, libjpeg is used to decode and encode JPEG images according to the JPEG 2000 standard [4], or the library libpng handles images in the Portable Network Graphics (PNG) format. Depending on which formats you anticipate to use, you also have to install those libraries. Please refer to the ImageMagick documentation for more details.

Building the UDFs

The source code for the UDFs can be found in the Download section. The code is split into several files to group similar functions together and to modularize the whole project. This modularization and the linking of the ImageMagick libary prevent you from using the bldrtn script unchanged. (The script can be found in the sqllib/samples/c/ directory). By means of an example we use the Linux version of the script to illustrate the necessary changes that need to be applied. The modified script is shown in Listing 3, with the new portions set in italics.

########################################################
# SCRIPT: bldrtn
# Builds Linux C routines (stored procedures or UDFs)
# Usage: bldrtn <prog_name> [ <db_name> ]

# Set DB2PATH to where DB2 will be accessed.
# The default is the standard instance path.
DB2PATH=$HOME/sqllib

# Default compiler/linker settings
LIB="lib"
EXTRA_C_FLAGS=""

# Determine our bitwidth (32 or 64) and hardware platform
BITWIDTH=`LANG=C db2level | awk '/bits/{print $5}'`
HARDWAREPLAT=`uname -m`

if [ $BITWIDTH = "\"32\"" ]
then
  LIB="lib32"
fi

# Set up compiler switches according to the current environment
if [ "$HARDWAREPLAT" = "x86_64" ] || [ "$HARDWAREPLAT" = "ppc64" ]
then
  if [ $BITWIDTH = "\"64\"" ]
  then
    EXTRA_C_FLAGS="-m64"
  else
    if [ "$HARDWAREPLAT" != "ppc64" ]
    then
      EXTRA_C_FLAGS="-m32"
    fi
  fi
fi

# Set the runtime path.
EXTRA_LFLAG="-Wl,-rpath,$DB2PATH/$LIB"

# If an embedded SQL program, precompile and bind it.
if [ -f $1".sqc" ]
then
  ./embprep $1 $2
fi

# Compile the program.
for i in IexAttributes.cpp IexCatalog.cpp IexError.cpp IexManipulation.cpp \
        IexUdfUtils.cpp IexImageBlob.cpp IexDisplay.cpp; do
    gcc $EXTRA_C_FLAGS -fpic -I$DB2PATH/include -c $i -D_REENTRANT
done

# Link the program and create a shared library
gcc $EXTRA_C_FLAGS -shared -o imageudfs *.o $EXTRA_LFLAG -L$DB2PATH/$LIB \
    -ldb2 -lpthread -ldb2apie -lMagick

# Copy the shared library to the function subdirectory.
# The user must have write permission to this directory.
rm -f $DB2PATH/function/imageudfs
cp imageudfs $DB2PATH/function

With the modifications applied you simply type ./bldrtn to start the build process. The resulting shared library will be placed in the sqllib/function/ directory of your DB2 instance. That's it. Now you merely have to register all the functions, a process that is described in the next section.

Back to top

SQL interface

As mentioned in the introduction, we defined two different SQL interfaces [5]. The first is BLOB-based and very lightweight. It only deals with BLOBs that encode the actual image data. No special image data type is created. The second interface builds on top of the first and it offers a special the data type SI_StillImage. Such a data type is also defined in the "SQL/MM Part 5: Still Image" standard [3], together with its methods.

Before you can use the functions explained in the previous sections, you have to register them in the database. To that end, you apply the scripts create_blob.sql and create_sqlmm.sql, respectively. Please note that the scripts use the '@' as a terminator for the SQL statements.

$ db2 "connect to <your-database>"
$ db2 -td@ -f create_blob.sql
$ db2 -td@ -f create_sqlmm.sql
$ db2 "connect reset"

If you are not interested in the SQL/MM interface, then you don't need to run the second SQL script. However, the first script is mandatory even if you don't intend to use the BLOB interface, because the SQL/MM interface builds on top of the BLOB functions.

Dealing with images as BLOBs

The following functions are provided to retrieve attributes of an image. The input parameter for each UDF is a BLOB that contains the image. The complete C++ code for all those functions can be found in the file IexAttributes.cpp that is available in the Download section.

Table 1. List of functions to collect an image's attributes

Note: The function SI_getNumColors requires that the complete image data is read because all the pixels need to be accessed. Thus, pinging of the image is not sufficient to retrieve the information. All of the other functions can get their results from a simple ping, as described above.

In case you are interested in more than a single image attribute at a time, we implemented the table function SI_getImageAttr shown in the table above to return all attributes (except the number of colors) of a given image. The single function will result in better performance because (a) you save several function calls and, thus, the code path to be executed inside DB2 is much shorter, and (b) the processing in ImageMagick only takes place once.

The majority of the functions is concerned with the various ways to manipulate an image. All those functions take an image (BLOB) as input parameter and, if necessary, a set of parameters that influence the respective operation. As result, each UDF returns a BLOB with the new image. The next table lists all those functions and describes the parameters.

Table 2. List of functions to manipulate an image

Implementing the SQL/MM Interface

Part 5 of the SQL/MM standard ISO/IEC 13249 defines a data type called SI_StillImage along with a set of methods to handle still images in a database system. The above described that the BLOB interface does not conform to this standard. However, the BLOB-based functions allow us to easily come up with a standard-conforming extender. In this section we will outline our approach. We would like to refer to the Download section where you can find all the SQL scripts to set up the database (along with the rest of the code).

We define a structured type that encapsulates an image, its properties, and applicable methods as shown in Listing 5. That type must have the capability to store the image data, so it needs an attribute of type BLOB. Additionally, we add attributes for the image's height, width, format, compression scheme, and color space. Those attributes are not required from a functionality point of view, but if you often deal with the size of the image or the format, then the direct attribute access is, of course, much faster than calling the UDF that extracts the requested information directly from the raw image data.

CREATE TYPE image.SI_StillImage
   AS (
      image_data   BLOB(10M),
      height       INTEGER,
      width        INTEGER,
      format       VARCHAR(128),
      compression  VARCHAR(128),
      color_space  VARCHAR(128)
   )
   INSTANTIABLE
   NOT FINAL
   MODE DB2SQL
   REF USING BIGINT@

Once the data type is defined, we can add the methods. For illustration, we define the method to query the image's height, and we also add a method to rotate an image by a given angle in Listing 6. The rotation method will use internally the function SI_rotate(BLOB, DOUBLE); additionally, it has to ensure that all of the attributes in the structured type are updated properly.

ALTER TYPE image.SI_StillImage
   ADD METHOD SI_getHeight()
      RETURNS INTEGER
      SPECIFIC image.SQLMM_getHeight
      LANGUAGE SQL
      DETERMINISTIC
      NO EXTERNAL ACTION
      CONTAINS SQL
      
   ADD METHOD SI_rotate(angle DOUBLE)
      RETURNS INTEGER
      SPECIFIC image.SQLMM_rotate
      SELF AS RESULT
      LANGUAGE SQL
      DETERMINISTIC
      NO EXTERNAL ACTION
      CONTAINS SQL@
      
CREATE METHOD SI_getHeight()
   FOR image.SI_StillImage
   RETURN SELF..height@
   
CREATE METHOD SI_rotate(angle DOUBLE)
   FOR image.SI_StillImage
   RETURN SELECT SELF..image_data(result.image)..
                       height(attr.height)..width(attr.width)..
                       format(attr.format)..compression(attr.compression)..
                       color_space(attr.colorSpace)
          FROM   TABLE ( VALUES ( SI_rotate(SELF..image_data, angle) ) )
                    AS result(image),
                 TABLE ( SI_getImageAttr(result.image) ) AS attr
          WHERE  SELF IS NOT NULL@

The method SI_rotate is implemented in such a way that the VALUES constructor takes the image data from the subject parameter (SELF) and passes it to the SI_rotate function, along with the angle. The resulting BLOB is the rotated image, and that is passed to the table function SI_getImageAttr to extract the image attributes. The construction of the new SI_StillImage object happens in the SELECT clause where the new values are set for the attributes of the SI_StillImage data type.

The other functions and methods of the SQL/MM standard can be implemented along the same lines. Note, however, that you could achieve better performance if you would use a single call to external code to perform the image manipulation and the attribute extraction at once. The coding for that is left as an exercise to the interested reader.

A final piece for the SQL/MM support is the implementation of constructor functions. DB2 does not yet support native constructor methods, so we resort to functions for the same task. A constructor function takes the necessary input parameter(s) and returns a value of type SI_StillImage. Once that value is constructed, you can store it in a column of type SI_StillImage and/or invoke the methods defined for the type. Listing 7 shows the constructor function where we use again the functionality implemented for the BLOB interface.

CREATE FUNCTION image.SI_StillImage(image BLOB)
   RETURNS image.SI_StillImage
   SPECIFIC image.SI_StillImage
   LANGUAGE SQL
   DETERMINISTIC
   NO ExTERNAL ACTION
   CONTAINS SQL
   RETURN SELECT SI_StillImage()..image_data(image)..
                       height(attr.height)..width(attr.width)..
                       format(attr.format)..compression(attr.compression)..
                       color_space(attr.colorSpace)
          FROM   TABLE ( SI_getImageAttr(image) ) AS attr
          WHERE  image IS NOT NULL@

For the sake of completeness and as reference, I have provided in Table 3 the full list of supported methods and constructor functions. You will notice many similarities in the names chosen for the methods and the above listed names of functions that operate on BLOBs. The underlying functionality for those correspondences is identical, and Tables 1 and 2 above give a description of the various parameters. The SI_StillImage value is implicitly available in each method as the so-called subject parameter; i.e., SELF, and thus the image data is available.

Table 3. List of methods of the SI_StillImage type

Back to top

Catalog

One piece of information is important for this still image extender: the list of supported image formats. A catalog view named SI_SUPPORTED_FORMATS is provided to reflect that information to the user. The view shows the identifier for each format; i.e., its name, and whether ImageMagick is able to decode (read) and encode (write) the specific format. If the format can be decoded and/or encoded, then the respective value will be 'Y'; otherwise, an 'N' will be used. Added to that comes a short description for each format. The view has the following structure.

IMAGE.SI_SUPPORTED_FORMATS(
   format       VARCHAR(128),
   decode       CHARACTER(1),
   encode       CHARACTER(1),
   description  VARCHAR(254)
)

Internally, the view is based on a table function. The code for the function can be found in the file IexCatalog.cpp. It queries ImageMagick for all formats that are available and converts the returned information in the above described table structure.

The OPEN call made to the function gets a pointer to an array of MagickInfo structures and places that pointer on the scratchpad, along with the information how many elements can be found in the array. The subsequent FETCH calls retrieve one format indication each, along with the associated description and decode/encode information based on the presence of the respective decode and encode function pointers. Then the UDF advances to the next MagickInfo structure for the next FETCH call. If there is no further format that can be processed, the function returns the SQLSTATE '02000' to indicate to DB2 that no further row is returned by the function and the table is completed. The last step happens in the FINAL call, where the array of MagickInfo structures needs to be freed to prevent possible memory leaks.

Back to top

Sample scenarios

After all the explanations, it is finally time to present some examples to demonstrate that the described functionality actually works. In order to do that, we need some images that can be loaded into the database. You can either use your own application or the DB2 IMPORT or LOAD commands to get the images from files into BLOBs in your database. Alternatively, we provided the two functions SI_loadImage(VARCHAR(256)) and SI_exportImage(BLOB, VARCHAR(256)). The first function takes an absolute filename as input parameter, opens the referenced file, and returns the file's content as BLOB. Thus, you can simply load images from files residing on the database server. The second function takes a BLOB and a filename as input and writes the BLOB to the specified file. That way, you can modify the images and export them and use any external image viewer to visualize the results. Furthermore, the function SI_display(BLOB, VARCHAR(255)) is available, and that function uses an X connection to open a window on the display (specified in the second parameter) of the client machine. The given image is shown in that window. When the window has focus and you type 'q', it will be closed and the UDF returns. In order to register these three functions in your database, you can run the scripts create_load.sql and create_display.sql, as shown in Listing 9. Note that the second script supplies a method for the SI_StillImage type. Therefore, you should have executed create_blob.sql and create_sqlmm.sql first. Alternatively, you can also modify the script and remove the SQL/MM-related functionality.

$ db2 "connect to <your-database>"
$ db2 -td@ -f create_load.sql
$ db2 -td@ -f create_display.sql
$ db2 "connect reset"

Note that we placed all of the functions, tables, and views that we implemented in the schema IMAGE. Thus, you either have to qualify all functions with that schema name, or you set the DB2 special register CURRENT FUNCTION PATH to include the mentioned schema. Both ways will allow DB2 to find the functions we want to call.

First, we retrieve a list of all supported image formats. As explained before, a catalog view provides that information. As you can see in the output in Listing 10, our installation of the ImageMagick library supports quite a few different formats, and also several versions for the formats, such as BMP.

$ db2 -td@

db2 => SELECT COUNT(*) FROM image.si_supported_formats@

1
-----------
        147

  1 record(s) selected.

db2 => SELECT * FROM image.si_supported_formats FETCH FIRST 13 ROWS ONLY@

FORMAT     ENCODE DECODE DESCRIPTION
---------- ------ ------ --------------------------------------
A          Y      Y      Raw alpha samples
ART        N      Y      PFS: 1st Publisher
AVI        N      Y      Microsoft® Audio/Visual Interleaved
AVS        Y      Y      AVS X image
B          Y      Y      Raw blue samples
BMP        Y      Y      Microsoft Windows® bitmap image
BMP2       Y      N      Microsoft Windows bitmap image v2
BMP3       Y      N      Microsoft Windows bitmap image v3
C          Y      Y      Raw cyan samples
CAPTION    N      Y      Image caption
CIN        Y      Y      Cineon Image File
CIP        Y      N      Cisco IP phone image format
CLIP       Y      N      Image Clip Mask

  13 record(s) selected.

The next group of functions extracts attributes from the image itself. For that, we really need to have an image available as BLOB in the database. We load an image using the SI_loadImage function and apply the functions to get the attribute information. Note that Listing 11 shows the functions that work directly on the LOBs and, in addition, the SQL/MM conforming interface based on the SI_StillImage data type. The images used for the test are those:

$ db2 -td@

db2 => SET CURRENT FUNCTION PATH = image, CURRENT FUNCTION PATH@
db2 => VALUES SI_getHeight(SI_loadImage('/home/stolze/test_1.jpg'))@

1
-----------
        128

  1 record(s) selected.

db2 => VALUES SI_getFormat(SI_loadImage('/home/stolze/test_2.gif'))@

1
------------------------
GIF

  1 record(s) selected.

db2 => VALUES SI_StillImage(
db2 (cont.) => SI_loadImage('/home/stolze/test_2.gif'))..SI_getWidth()@

1
-----------
        481

  1 record(s) selected.

db2 => VALUES SI_StillImage(
db2 (cont.) => SI_loadImage('/home/stolze/test_1.jpg'))..SI_getYResolution()@

1
------------------------
  +1.11000000000000E+002

  1 record(s) selected.

So far everything has gone quite well, and we can move on to the more sophisticated functions that actually manipulate the image's content. In Listings 11 and 12 we employ two different approaches to visualize the result. In one case we apply the SI_display UDF to pop up a window on our client machine, and in the second case the resulting image will be written to another file, and an external image viewer is used to examine the result of the preceeding operation.

$ db2 -td@

db2 => SET CURRENT FUNCTION PATH = image, CURRENT FUNCTION PATH@
db2 => CREATE TABLE t1 ( id INTEGER, img BLOB(10M) )@
db2 => INSERT INTO t1 VALUES ( 1, SI_loadImage('/home/stolze/test_1.jpg') )@
db2 => UPDATE t1 SET img = SI_rotate(img, 45) WHERE id = 1@
db2 => SELECT SI_exportImage(img, '/home/stolze/test_1.jpg') FROM t1 WHERE id = 1@

1
-----------
      10237

  1 record(s) selected.

db2 => QUIT@

$ xv /home/stolze/res_1.jpg

If everything is executed successfully, then the resulting image should have a size of about 181 by 181 pixels and look like the one shown in Figure 3.

$ db2 -td@

db2 => SET CURRENT FUNCTION PATH = image, CURRENT FUNCTION PATH@
db2 => CREATE TABLE t2 ( id INTEGER, img image.SI_StillImage )@
db2 => INSERT INTO t2 VALUES ( 1, SI_StillImage(
db2 (cont.) => SI_loadImage('/home/stolze/test_2.gif')) )@
db2 => SELECT img..SI_shear(10, 0)..SI_roll(50, 0)..SI_display()
db2 (cont.) => FROM t2 WHERE id = 1@

1
-----------
          0

  1 record(s) selected.

A window will open while processing each row to be returned from the SELECT statement in the above sample scenario. In that window you should see the picture from Figure 4. The image is sheared along the X dimension, and then the resulting images was rolled to the right; i.e., 50 columns in the image were stripped away from the right side and appended on the left border. Once you close the window by clicking q or select the Quit option from the menu that opens when you right-click inside the window, the UDF terminates and returns 0. If another row were to be processed, a new window would open and the next image would be shown there. We only used a single image, though. Note that all the available menus stem from ImageMagick.

Back to top

Summary

In this article you've seen an approach for integrating an external library into your DB2 database. Using an example, we implemented a still image extender. We also explained two different interfaces. The first interface is very simple and just deals with images as BLOBs. The second interface builds on top of the first version and conforms to the SQL/MM Still Image standard. The article includes the complete source code and the instructions to build the shared library that actually implements the image-specific functionality.

Back to top

Download

Description	Name	Size	Download method
ImageMagick Extender for DB2 UDB souce code	image-ext.zip	43 KB	FTP|HTTP

	Information about download methods

Resources

• [1] ImageMagick is the image manipulation library that is used for the Still Image Extension presented in this article.
  
  
• [2] IBM provides an DB2 Image Extender with a different scope as part of the DB2 UDB Audio, Image, and Video package.
  
  
• [3] The ISO/IEC 13249-5 SQL/MM Part 5: Still Image standard defines the interface for still image extensions in relational database systems.
  
  
• [4] The ISO/IEC 15444 JPEG 2000 standard specifies the encoding of images in the JPEG/JFIF format.
  
  
• [5] The DB2 SQL Reference specifies the functionality available in DB2 UDB.
  
  
• [6] The DB2 Application Development Guide explains the details how to implement the user-defined functions, types, and methods shown in this article.
  

About the author

		Knut Stolze started his work with DB2 when he joined IBM as a visiting scientist at the Silicon Valley Lab where he worked on the DB2 Image Extender. For the following two years he moved on to the DB2 Spatial Extender team and was responsible for several enhancements to improve the usability, performance, and the standard-conformance of the extender. Currently, he works as a teaching assistant at the University of Jena, Germany, and continues his work for IBM in the area of the WebSphere Information Integrator development. He can be reached through the newsgroups comp.databases.ibm-db2 and ibm.software.db2.udb.spatial or via stolze@de.ibm.com.

Rate this page

Please take a moment to complete this form to help us better serve you. Did the information help you to achieve your goal? Yes No Don't know   Please provide us with comments to help improve this page:   How useful is the information? 1 2 3 4 5 Not useful Extremely useful

Share this.... Digg this story del.icio.us Slashdot it!

Back to top