LOB and XML materialization

Materialization means that Db2 puts the data that is selected into a buffer for processing. This action can slow performance. Because LOB values can be very large, Db2 avoids materializing LOB data until absolutely necessary.

Beginning in DB2® 10, LOB and XML materialization has been reduced or eliminated within Db2 for several local and distributed cases including utilities (LOAD and cross-loader). Some of the cases where materialization has been eliminated or reduced include during DRDA streaming, file reference variable processing, CCSID conversion and distributed XML fetch processing. However, whether the values will be materialized and how much will be materialized also depends on the number and size of each LOB or XML.

Db2 stores LOB values in contiguous storage. Db2 must materialize LOBs when your application program performs the following actions:

  • Calls a user-defined function with a LOB as an argument
  • Moves a LOB into or out of a stored procedure
  • Assigns a LOB host variable to a LOB locator host variable

The amount of storage that is used for LOB and XML materialization depends on a number of factors including:

  • The size of the LOBs
  • The number of LOBs that need to be materialized in a statement

Db2 loads LOBs into virtual pools above the bar. If insufficient space is available for LOB materialization, your application receives SQLCODE -904.

Although you cannot completely avoid LOB materialization, you can minimize it by using LOB locators, rather than LOB host variables in your application programs.