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'
picturerepresents a LOB column and
infmx_emp_tablerepresents 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:
|Data source||Type of operations|
|Db2 for z/OS, Db2 for IBM i, Db2 Database1||
read and write
|Microsoft SQL Server||read only|
|Oracle (NET8 wrapper) 2||read and write|
|Teradata||read and write|
|Web services||read only and bind-out for CLOB only|
- 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.