If you select the Include LOBs option for a task that has a Db2 for LUW, MySQL, Oracle, PostgreSQL, or SQL Server source, the captured LOB data might be truncated when it's loaded to the target. The truncation point primarily depends on the maximum size that the target allows.
Target-side truncation points:
•Binary large object types: BLOB, BYTEA, GEOGRAPHY, GEOMETRY, IMAGE, LONG RAW, LONG VARCHAR FOR BIT, or VARBINARY(MAX) columns are truncated before being written to BINARY columns on the target.
- For Amazon S3, Databricks, Google Cloud Storage, Microsoft Azure Data Lake Storage Gen2, Oracle, Oracle Cloud Object Storage, Microsoft Fabric OneLake, PostgreSQL, and SQL Server targets, the data is truncated to 16777216 bytes.
- For Amazon Redshift targets, the data is truncated to 1024000 bytes.
- For Microsoft Azure Synapse Analytics targets, the data is truncated to 1000000 bytes.
- For Google BigQuery and Snowflake targets, the data is truncated to 8388608 bytes.
• Character large object types: CLOB, DBCLOB, NCLOB, LONG, LONG VARCHAR, LONG VARGRAPHIC, TEXT, NTEXT, NVARCHAR(MAX), RAW, VARCHAR(MAX), or XML columns are truncated before being written to VARCHAR columns on the target.
- For Amazon S3, Databricks, Google Cloud Storage, Microsoft Azure Data Lake Storage Gen2, Oracle, Microsoft Fabric OneLake, Oracle Cloud Object Storage, PostgreSQL, and Snowflake targets, the data is truncated to 16777216 bytes.
- For Amazon Redshift targets, the data is truncated to 65535 bytes.
- For Google BigQuery targets, the data is truncated to 8388608 bytes.
- For Microsoft Azure Synapse Analytics targets, the data is truncated to 500000 bytes.
- For SQL Server targets, CLOB, TEXT, and VARCHAR(MAX) data is truncated to 16777216 bytes, NCLOB, NTEXT, and NVARCHAR(MAX) data to 33554432 bytes, and XML data to 33554442 bytes.
- For Azure Event Hubs targets, the overall record maximum size is 1 MB. If the record size exceeds 1 MB, Azure Event Hubs generates an error, and the task fails.
Source-side truncation considerations:
•For Db2 for LUW, MySQL, and Oracle sources, you can specify the custom properties dbmiSourceBlobTruncationSize and dbmiSourceClobTruncationSize for the source to control the number of bytes at which truncation occurs for blob and clob types of data, respectively, when you want the data truncated at a point less than the maximum size that the target allows. For MySQL sources, the default is 16 MB for all blob, text, and json types.
•For PostgreSQL sources in incremental loads and combined loads, if large-object columns contain more than 1 MB of data, the data is truncated to 1 MB.