DB2 Image Extender

1
DB2 Image Extender

• Pam Odden

2
Objectives

• What is an Extender
• What can Image Extender do for us?
• Review of LOB data
• What other extenders are available?

3
What is an Extender?

• Extenders are software packages that help in the
  use of large objects (LOBs)
• They define special data types and functions for
  many types of large objects, including
• Images
• Audios and videos
• Text
• XML documents
• Spatial objects (maps)
• Helps make these objects less cumbersome to
  manipulate with SQL

4
Image Extender

• DB2 Image Extender allows you to bring together
  images and related business data in one SQL
  query, for example, a photograph of a product and
  some description about it.
• The DB2 Image Extender supports a variety of
  image formats, such as GIF, JPEG, BMP, and TIFF.
• Some user-defined functions are provided for
  storing, accessing and manipulating images
• You can also define your own data types and
  functions for image data using DB2s built-in
  support for user-defined types and user-defined
  functions.

5
Some things Image Extender can do

• Import and export images and their attributes
  into and out of a database. When you import an
  image, Image Extender stores and maintains image
  attributes such as size in bytes, format, height,
  width, and number of colors.
• Control access to images with the same level of
  protection as traditional business data.
• Change the formats of images. You have the
  flexibility of importing or exporting an image in
  its source format, or converting to a different
  format. You can also scale an image, rotate it,
  do black-white image inversion, or change
  representational characteristics, such as bits
  per sample and compression type.
• Backup and recover images. Images and their
  attributes that you store in the database have
  the same security and recovery protection as
  traditional business data.

6
Some things Image Extender can do, cont.

• Search for images based on data that you
  maintain, such as a name, number, or description
  or by data that the DB2 Image Extender maintains,
  such as the format of the image or its
  distribution of colors.
• Generate and display image thumbnails and full
  images. A thumbnail is a miniature version of an
  image. When you import an image into a database,
  the DB2 Image Extender creates and stores a
  thumbnail of the image. You can use the DB2 Image
  Extender to retrieve a thumbnail or a full-size
  image. You can then use the DB2 Image Extender to
  invoke your favorite browser to display the
  thumbnail or full-size image.

7
Example

• EXEC SQL INSERT INTO STUDENTS VALUES (
• STUD_ID,
• STUD_NAME,
• DB2IMAGE(
• CURRENT SERVER,
• /student_dir/images/current.bmp
• ASIS,
• MMDB_STORAGE_TYPE_INTERNAL,
• STUD_PHOTO_TAG)
• END-EXEC
• DB2IMAGE is a user-defined function
• It takes parameters of the server and directory
  location of the image, its storage type, and a
  comment describing the image
• It reads info about the image from its header
  height, width, color, pixels, etc.)
• Enters the content of the image source file into
  the LOB table as a BLOB (a long string of binary
  characters)

8
Example, cont.

• Enters into an administrative table info about
  the image that the system uses to manipulate the
  image
• A handle for the image (name to refer to it by)
• Timestamp
• Image size in bytes
• A thumbnail sized image
• The comment contained in STUD_PHOTO_TAG
• The example uses the internal storage type to
  store the image in a DB2 table as a BLOB.
  Extenders make it possible to keep the image in
  its original file on the server, and just
  administer it through the database, using
  MMDB_STORAGE_TYPE_EXTERNAL

9
Refresher What is a Large Object?

• Large objects are a new set of data types that
  can store strings up to 2GB
• BLOB Binary Large Object
• Stores binary data like pictures and videos
• CLOB Character Large Object
• Stores single byte or mixed character data like
  documents
• DBCLOB Double Byte Character Large Object
• Stores double byte character data like documents
  in double byte languages
• LOBs are varying-length character strings

10
Refresher, cont New Way of Storing Data

• Since LOBs can represent very large amounts of
  data they are stored differently from other
  column values in DB2
• A ROWID column and a lob indicator column are
  stored in the base table like any other columns
• The large object itself is stored in its own LOB
  table space and resides in an auxiliary table.
  LOB columns are referred to as auxiliary columns.
• Every auxiliary table has an index, and the rowid
  of the base table is the index key.
• The lob indicator column is stored as VARCHAR(4)
  and has indicator flags to show whether the
  column is null or zero-length and whether there
  exists a valid associated LOB.

11
DB2 Video Extender

• The Video Extender can store as many as 3
  representative frames per video. By displaying
  the frames you get an effective view of the
  videos content
• Sample programs are provided that demonstrate how
  to build and display a video storyboard. The
  storyboard allows preview of a video before it is
  downloaded, reducing traffic on the network.

12
DB2 Text Extender

• Enables storage of text documents in DB2
• Provides high-speed search capability
• Provides intelligent search using IBMs
  high-performance linguistic search technology
• Search for docs that contain specific text,
  synonyms of a word or phrase, or words in
  proximity, such as in the same sentence or
  paragraph
• Make a fuzzy search for words having a spelling
  similar to the search term. This is useful for
  finding words even when misspelled.
• Make a free-text search in which the search
  argument is expressed in natural language
• Search for words that sound like the search term
• Search docs in various languages and document
  formats

13
DB2 Text Extender, cont.

• The same query can search both document content
  and business data
• The Text Extender programming interface allows
  users to browse selected documents
• SELECT FROM MYTEXTTABLE
• WHERE VERSION 2
• AND DB2TX.CONTAINS (
• DB2BOOKS_HANDLE,
• authorization
• IN SAME PARAGRAPH AS table
• AND SYNONYM FORM OF delete ) 1
• DB2TX.CONTAINS is a search function
• DB2BOOKS_HANDLE is a handle column that refers to
  table column DB2BOOKS that contains the text docs
  to be searched
• The remainder of the statement is a search
  argument that looks for authorization occurring
  in the same paragraph as table and delete or
  a synonym (drop, erase, remove).

14
Summary

• Extenders make storing, accessing, and
  manipulating LOBS more convenient
• They provide functions that can be called rather
  than having to include code for working with LOBs
• Information about stored images and other objects
  is stored in special tables called Administration
  Tables
• The Administration tables are used by DB2 when it
  works with the objects