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.
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 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.
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 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.
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
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
LOB and XML data can also be inlined in DB2 10 for z/OS.