Chapter 8. Performance with LOBs 241
Figure 8-1 DB2 materialization overview illustration
8.1.1 The different cases of materialization
In order to give you a better picture of LOB materialization and when it occurs, we describe
different scenarios.
In general, you can UPDATE, INSERT, DELETE, or SELECT a LOB value. DB2 9 introduces
new and exciting ways of manipulating LOBs with LOB file reference variables. The FETCH
CONTINUE clause is introduced by DB2 9 to make the programmer’s life easier and the code
less complicated.
With versions prior to DB2 9, there are several good reasons for using LOCATORs when
processing LOB values. A LOCATOR is recommended if you care about storage allocation
within your users’ address spaces, overall performance, and useful handling of your LOB
data.
The V9 LOB file reference variables are very helpful when all you need to do is transfer data
between DB2 and a file that is external to DB2.
Materialization within your DB2 DBM1 virtual storage depends on the way the LOB value is
accessed.
SELECT
We assume we have three applications that select a LOB for further processing. This could
be printing a LOB that contains a book or storing it into a non-DB2 data set. Let us say we
have application A using a LOCATOR, application B using neither a LOCATOR nor file
reference variables, but rather a host variable, and application C using LOB file reference
Stored
Procedure
Address Space
User
Address Space
DB2 DDF
Address Space
Disk environment
DB2 DBM1 Address
Space
Virtual Pool
Buffer Manager
LOB Manager
Data Manager
Materialized LOBs
2G bar
DB2 DBM1 Address
Space
242 LOBs with DB2 for z/OS: Stronger and Faster
variables. From the point of view of DB2 LOB materialization, application A, application B, and
application C do not materialize the LOB in DB2 virtual storage when they only select a LOB
value. See Figure 8-2.
Figure 8-2 LOB Materialization In user address space in case of data retrieval
Application A uses a locator and might just use the space it needs for the chunks of data
pointed to through the defined locator. Application B might require the complete LOB data.
Therefore, the private user address space might require, based on the size of the LOB, a
huge amount of storage. Application C uses LOB file reference variables, eliminating any
need to allocate any kind of variable for storing chunks of the LOB in its local address space.
If you have applications in a distributed environment selecting LOB values through the
network, they involve the DB2 DDF address space. You can minimize the server’s storage
consumption by utilizing the DRDA flow optimization functionality. See 4.3, “DRDA LOB flow
optimization” on page 81.
If you use stored procedures, the selected LOB data is not materialized within a DBM1
address space. Stored procedures use LOCATORs and move the data in small chunks from
disk through the stored procedure address space.
INSERT
Assume a scenario where your application A and application B INSERT into your LOB table
space. From the point of view of materialization, the picture is different. Now there are space
allocations in virtual storage involved. The materialization in DB2 virtual storage occurs if you
are using LOCATORs, and it probably does not occur if you use other techniques. However,
not using LOCATORs, like in application B, causes you to require more storage in your private
user address space, and depending on the size of your LOB, this could consume a large
amount of storage. See Figure 8-3 on page 243.
User
Address Space
B
User
Address Space
A
User
Address Space
C
Disk environment
DB2 DBM1 Address
Space
Virtual Pool
Buffer Manager
LOB Manager
Data Manager
2G bar
DB2 DBM1 Address
Space
Get LOBs with DB2 for z/OS: Stronger and Faster now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.
