Skip to main content

By clicking Submit, you agree to the developerWorks terms of use.

The first time you sign into developerWorks, a profile is created for you. Select information in your profile (name, country/region, and company) is displayed to the public and will accompany any content you post. You may update your IBM account at any time.

All information submitted is secure.

  • Close [x]

The first time you sign in to developerWorks, a profile is created for you, so you need to choose a display name. Your display name accompanies the content you post on developerworks.

Please choose a display name between 3-31 characters. Your display name must be unique in the developerWorks community and should not be your email address for privacy reasons.

By clicking Submit, you agree to the developerWorks terms of use.

All information submitted is secure.

  • Close [x]

DB2 10.1 fundamentals certification exam 610 prep, Part 1: Planning

Norberto Gasparotto Filho
Norberto Gasparotto Filho is a database specialist with more than eight years of experience with database administration. He was the winner of the first edition of "DB2's Got Talent" contest in 2011. He also worked as programmer using a variety of technologies, and has certifications in both programming and database administration areas. In his blog ("Insights on DB2 LUW database admin, programming and more"), Norberto shares lessons learned in the day-to-day database administration work, tips and knowledge. During his spare time, Norberto likes to run, ride a bike and have fun with his kids and wife. Learn more in Norberto's profile in the developerWorks community.

Summary:  This tutorial introduces you to the basics of the IBM® DB2® 10.1 product editions, functionalities and tools, along with underlying concepts that describe different types of data applications such as OLTP, data warehousing / OLAP, non-relational concepts and more. It will briefly introduce you to many of the concepts you'll see in the other tutorials in this series, helping you to prepare for the DB2 10.1 Fundamentals certification test 610.

View more content in this series

Date:  18 Oct 2012
Level:  Introductory PDF:  A4 and Letter (2292 KB | 50 pages)Get Adobe® Reader®


Non-relational data concepts in DB2

Now, more than ever, relational database management systems are being used to store not so conventional types of data such as audio clips, video clips, images, graphs, binary files and practically any other type of data that you can imagine. Such data can be stored in DB2 databases through the use of Large Object (LOB), Extensive Markup Language (XML), and structured data types. Although you’ll learn about data types in more detail in other parts of this tutorial series, we’ll explore the LOB, XML, and structured data types here.

Large Objects (LOBs)

DB2 has several built-in data types that can be used to store various types of “traditional" information, such as numbers, characters and character strings, dates, times, and timestamps. But there may be times when you want or need to store large chunks of data that is “non-traditional" or too large to be stored in one of the more conventional data types. For these situations, DB2 provides the following large object (LOB) data types:

  • Binary Large Object (BLOB), which is used to store files and any kind of varying length binary data;
  • Character Large Object (CLOB), which is used to store large text data values;
  • Double-Byte Character Large Object (DBCLOB), which is used to store large graphic/double-byte character data;
  • National Character Large Object (NCLOB), which is essentially the same as DBCLOB, and is provided for compatibility with Oracle applications. NCLOB is only available in DB2 10.1 for LUW. (Not present in DB2 10 for z/OS.)

From an application perspective, accessing large object data is done a little differently, depending on the type of LOB data being retrieved. CLOB data can be retrieved and stored methods that would be used if the data was stored in a more traditional character data type. Consequently, locating, retrieving, inserting, updating, and deleting data is imperceptible to applications.

For BLOB data, as the binary values usually don’t make sense to our eyes, applications have to be programmed to deal with such data. Some programming languages use statements like SELECTBLOB and UPDATEBLOB to specially fetch and update binary data.

LOB locators

LOB and XML values can be very large (up to 2 GB in size), and the transfer of such values from the database server to a client application program host variable can be time consuming. Because of this, most application programs prefer to process LOB values in pieces, rather than as a whole. C/C++ and embedded SQL applications can reference a LOB value by using what is known as a large object locator. LOB locators represent a value for a LOB resource that is stored in a DB2 database and enable applications to operate on small chunks of that resource at a time, instead of having to retrieve the entire LOB data value. They behave as a snapshot of a piece of an LOB value, and not as a pointer to a row or a location in the database.

XML data

Earlier, we saw that DB2 offers native XML manipulation and storage through the pureXML feature, available in all DB2 editions (including DB2 for z/OS) at no-charge. One of the components of pureXML is the XML data type, which enables DB2 to store XML documents in their native hierarchical format, rather than as text or mapped as a different data model.

While other database management products store XML data as plain text or through shredding, DB2 enables efficient search, retrieval and updates to well-formed XML documents that are natively stored in DB2 databases through pureXML. The access and manipulation of such XML data can be done through XQuery, SQL, or a combination of the two.

Structured data types

Structured types are user-defined types available on DB2 LUW that contain one or more attributes - which can be mapped to any supported data type, including LOBs and data types other than those that are available with DB2 for Linux, Unix and Windows. (You can create new user-defined structured types and use them as columns in a table.)
Structured types can be compared to objects / classes in an Object-Oriented model. It is possible to instantiate structured data type objects, use inheritance, create/use methods (actions), and much more. Structured data types are stored much like LOBs and XML documents are stored.

Storage for XML, LOB and structured data types

At one time, LOB, XML and structured data types were stored in a separate object within their tables and in the case of LOB data, LOB descriptors were used to reference the location of every value stored. (Figure 36 shows how LOB data has traditionally been stored on DB2 for Linux, Unix and Windows - as DB2 for z/OS uses a different mechanism, based on columnar tables.)

Figure 36. How LOB data is stored
LOBs are stored through use of LOB descriptors.

Starting with DB2 LUW 9.7, is has been possible to store such data values inline. That means that, for smaller values, it is possible to have LOB, XML and structured data be stored together with other data in a table. Inlining is enabled by specifying the INLINE LENGTH clause when defining LOB, XML, and structured data type columns with CREATE and ALTER TABLE statements. Figure 37 shows how inlined LOB data values are stored.

Figure 37. How inlined LOB data values are stored
DB2 can store LOBs inline to speed-up access.

LOB and XML data can also be inlined in DB2 10 for z/OS.

6 of 10 | Previous | Next


Zone=Information Management
TutorialTitle=DB2 10.1 fundamentals certification exam 610 prep, Part 1: Planning