|DB2 supports three types of large objects (LOBs): character large objects |(CLOBs), double-byte character large objects (DBCLOBs) and binary large objects |(BLOBs). For general information about DB2 LOB support, see the following |DB2 books: |
|In a federated database system, you can access and manipulate LOBs at remote |data sources. Because LOBs can be very large, transferring LOBs from a remote |data source can be time consuming. The DB2 federated database attempts to |minimize transferring LOB data from the data sources, and also attempts to |deliver requested LOB data directly from the data source to the requesting |application without materializing the LOB at DB2.
|This section discusses: |
|DB2 federated systems use two mechanisms to retrieve LOBs: LOB streaming |and LOB materialization.
|In LOB streaming, LOB data is retrieved in stages. DB2 uses LOB streaming |for data in result sets of queries that are completely pushed down. For example, |consider the following query:
|SELECT empname, picture FROM orc_emp_table WHERE empno = '01192345'
|where picture represents a LOB column and orc_emp_table |represents a nickname referencing an Oracle table containing employee data. |The DB2 query processor marks the picture column for streaming |if it decides to run the entire query at the Oracle data source. At execution |time, if DB2 notes that a LOB is marked for streaming, it retrieves the LOB |in stages from the data source. DB2 then transfers the data to the application |memory space.
|In LOB materialization, the remote LOB data is retrieved by DB2 and stored |locally at the federated server. DB2 uses LOB materialization when: |
|Applications can request LOB locators for LOBs stored in remote data sources. |A LOB locator is a 4-byte value stored in a host variable that a program can |use to refer to a LOB value (or LOB expression) held in the database system. |Using a LOB locator, a program can manipulate the LOB value as if the LOB |value was stored in a regular host variable. The difference in using the LOB |locator is that there is no need to transport the LOB value from the server |to the application (and possibly back again). See the DB2 Application Development Guide for additional information about LOB locators.
|DB2 can retrieve LOBs from remote data sources, store them at DB2, and |then issue a LOB locator against the stored LOB. LOB locators are released |when: |
|When using and retrieving LOBs, consider that: |
|There are a few cases in which you can map a DB2 LOB data type to a non-LOB |data type at a data source. When you need to create a mapping between a column |with a DB2 LOB type and its counterpart column at a data source, it is recommended |that you use a LOB data type as a counterpart if at all possible.
|To create a mapping, use the create type mapping DDL statement. For example:
|CREATE TYPE MAPPING my_oracle_lob FROM sysibm.clob TO SERVER TYPE oracle TYPElong
|where:
|
If an application that retrieves remote LOBs returns an error message indicating there is not enough system resources to process the statement, increase the value of the application heap size parameter, APPLHEAPSZ, in the database configuration file. For example:
DB2 UPDATE DB CFG FOR EMPLOYEE USING APPLHEAPSZ 512
where EMPLOYEE is the name of the database you are tuning and 512 is the value of the application heap size parameter.