Blobs, Clobs and Videotapes using Large Objects in DB2

1
Blobs, Clobs and Videotapesusing Large Objects
in DB2

• Pam Odden

2
Objectives

• Learn what large objects are
• Learn how they are stored
• Learn how DB2 processes LOBS
• Learn some SQL considerations for LOB columns

3
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

4
How Large is a Large Object?
Typical sizes of some types of large object
5
A 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.

6
LOB Example

• CREATE TABLE LITERATURE
• (TITLE CHAR(10).
• IDCOL ROWID NOT NULL GENERATED ALWAYS,
• MOVIELEN INTEGER,
• LOBMOVIE BLOB(2G),
• LOBBOOK CLOB(10M))
• When the ROWID column is defined as generated
  always, DB2 generates a new rowid value for each
  inserted row
• The ROWID column can also be defined as GENERATED
  BY DEFAULT, in which case you generate your own.
  You would not want to do this. Only use
  generated by default when you are inserting data
  from another table which already has valid
  rowids, such as in a data warehouse application.
• This statement creates a table with two lobs, a
  movie and a book. The base table holds the title
  and the length of the movie. All the movies in
  this table are stored in an auxiliary table just
  for movies. All the books are stored in a
  different auxiliary table just for books.

7
2GB Host Variables? Get Real!

• LOB values can be selected, inserted and updated
  like any other column values. However, the
  program must declare a host variable large enough
  to store the lob.
• 01 HV_MOVIE PIC X(????)
• SELECT LOBMOVIE, MOVIELEN
• INTO HV_MOVIE, HV_LENGTH
• FROM LITERATURE
• WHERE TITLE The Maltese Falcon
• There are 2 methods of coping with this problem
• Move LOB data to the application a piece at a
  time
• Avoid moving LOB data to the program at all by
  using a locator variable to represent the LOB
  data

8
Processing LOBs a Piece at a Time

• There are several built-in functions to assist in
  separating pieces of a large object for easier
  processing.
• The example uses the POSSTR function to identify
  the starting position of strings Chapter 1 and
  Chapter 2 in the longer string LOBBOOK.
  (Remember a LOB is a variable-length character
  string.)
• It then uses the starting positions of Chapters 1
  and 2 to determine the length of Chapter 1 in
  order to isolate it as a substring.
• SELECT POSSTR(LOBBOOK,Chapter 1),
  POSSTR(LOBBOOK,Chapter 2)
• INTO START, END
• FROM LITERATURE
• WHERE TITLE The Maltese Falcon
• SELECT SUBSTR(LOBBOOK, START, END - START)
• INTO HV_CHAPTER
• FROM LITERATURE
• WHERE TITLE The Maltese Falcon

9
Processing LOBs Using Locators

• Often a program doesnt need to actually retrieve
  LOB data in order to work with it. The program
  could start a video or display a photograph
  without actually moving the data to its own
  working storage
• A locator variable can be used to refer to the
  LOB without actually retrieving the data.
• In the following example, the select statement
  uses a locator variable to represent the movie.
  The insert statement moves the real movie into
  the auxiliary table associated with the Videolib
  table without ever moving it into the program.
• Locators can be defined in all host languages
  except Rexx, but they are declared through SQL.
  The precompiler creates the appropriate structure
  for the 4-byte value.
• 01 HV_MOVIE_LOC SQL TYPE IS
  BLOB_LOCATOR.
• SELECT MOVIELEN, LOBMOVIE
• INTO HV_LENGTH, HV_MOVIE_LOC
• FROM LITERATURE
• WHERE TITLE The Maltese Falcon
• INSERT INTO VIDEOLIB (LENGTH, FILM, DESC)
• VALUES (HV_LENGTH, HV_MOVIE_LOC, Uncut
  Original)

10
SQL Considerations for LOB Columns

• LOBs are not updated, they are deleted and then
  the modified version is inserted.
• LOB columns cannot be referenced in the following
  clauses
• GROUP BY, HAVING, ORDER BY, SELECT DISTINCT,
  COLUMN FUNCTION, DATETIME FUNCTION, DECIMAL OR
  NULLIF FUNCTION, WHEN CLAUSE OF A CASE
  EXPRESSION, SUBSELECT OF UNION WITHOUT THE ALL
  KEYWORD
• A substring of a LOB that is cast (converted) to a CHAR type to overcome some
  of these restrictions.

11
Summary

• Will we ever use large objects here? (Never say
  never!) Currently they sound cumbersome and
  space-consuming, but the technology is there to
  store a photo of each student if that were ever
  considered worth the effort and space.
• Large Objects have special functions and locators
  to make them easier to manipulate.
• They are stored separately from their base
  tables, in auxiliary tables, so as not to bog
  down performance of the rest of the data.