Federated LOB support

With a federated database system, you can access and manipulate large objects (LOBs) at remote data sources.

A federated system supports SELECT operations on LOBs at DRDA, Informix®, Microsoft SQL Server, Oracle, and Sybase data sources. For example:

SELECT empname, picture FROM infmx_emp_table 
   WHERE empno = '01192345'
Where picture represents a LOB column and infmx_emp_table represents a nickname referencing an Informix table containing employee data.
A federated system supports SELECT, INSERT, UPDATE, and DELETE operations on LOBs at the following data sources, using the DRDA wrapper:
  • Db2® for z/OS® (Version 7 or higher)
  • Db2 for IBM® i (Version 5)
  • Db2 Database (Version 7 or higher)
The read and write operations supported by Db2 Database are listed in the following table:
Table 1. Read and write support for LOBs
Data source Type of operations
Db2 for z/OS, Db2 for IBM i, Db2 Database1
read and write
BioRS read only
Informix read only
JDBC read only
Microsoft SQL Server read only
Oracle (NET8 wrapper) 2 read and write
ODBC read only
Sybase read only
Teradata read and write
Web services read only and bind-out for CLOB only
XML read only
Note:
  1. Db2 for IBM i (Version 5 or later) is required for LOB support.
  2. To run insert, update, and delete operations on Oracle LONG columns, you need to migrate the remote columns from LONG to LOBs and recreate the nicknames.
Teradata LOBs
Teradata LOBs are slightly different than Db2 LOBs. Teradata does not have any data types as large as the LOBs supported in Db2 software. However, there are some Teradata data types that can be up to 64000 bytes long. These data types are CHAR, VARCHAR, BYTE, VARBYTE, GRAPHIC, and VARGRAPHIC. These Teradata data types are mapped to Db2 LOB data types when the length of the Teradata data type exceeds the limits of the corresponding Db2 data type. However, you cannot apply LOB write operations on LOB columns that are mapped from Teradata non-LOB columns.
LOB lengths
Some data sources, such as Oracle and Informix, do not store the lengths of LOB columns in their system catalogs. When you create a nickname on a table, information from the data source system catalog is retrieved including column length. Since no length exists for the LOB columns, the federated database assumes that the length is the maximum length of a LOB column in Db2 Database. The federated database stores the maximum length in the federated database catalog as the length of the nickname column.