JDBC Type 4 Driver Programmer's Reference for SQL/MX Release 3.1 (H06.23+, J06.12+)

<baseTableName> is the table that contains LOB column(s). TableName> is the table that contains the LOB data.
Creating LOB Tables
Except as noted below, use the -create and -execute options of the Lob Admin Utility to create LOB tables.
Note: Partitioned LOB tables must be manually created. You cannot use the Lob Admin Utility if your site needs
partitioned LOB tables. Do not use the
-execute option of the Lob Admin Utility. Follow these steps to manually
create partitioned LOB tables:
1. Use the
-create and -out options of the Lob Admin Utility to have SQL statements written to a file.
2. Modify the generated SQL statements as needed for your partitioning requirements.
3. Add the modified SQL statements to an MXCI script file.
4. Move the MXCI script file to the OSS environment, and run it.
Using SQL/MX Triggers to Delete LOB Data
Use the Type 4 Lob Admin Utility to generate triggers that delete LOB data from the LOB table when the base row is deleted. These triggers
ensure that orphan LOB data does not occur in the LOB table. To manage the triggers, use these Type 4 Lob Admin Utility options:
-trigger
Generates SQL statements to create triggers.
-drop
Generates SQL statements to drop triggers.
-exec
Executes the SQL statements that are generated.
For example, the following command generates the SQL statements to create the triggers for the base table
sales.paris.pictures, which
contains a
BLOB column, and executes those statements. Note: This command must be typed on one line.
java -Dt4sqlmx.blobTableName=sales.paris.lobTable4pictures
com.tandem.t4jdbc.T4LobAdmin
-trigger
-exec sales.paris.pictures
Backing Up and Restoring LOB Columns
For basic information about backing up and restoring databases, see the SQL/MX Installation and Management Manual. That discussion
describes the special considerations you must take for backing up and restoring tables that have LOB columns implemented for the Type 4
driver (because both base tables and LOB tables are involved).
When backing up and restoring a base table, make sure that the name of the table is unchanged. LOB data is not be accessible after
restoration if the base table name has changed.
Triggers cannot be restored by using Backup and Restore 2.0. You can capture the DDL for the CREATE statements executed for these
objects and use this information to manually recreate these objects after a Restore operation.
Make sure that the time interval in the backup of the base tables and LOB tables is not large. A smaller time interval ensures that the data
referred to by the base table is present in the LOB table.
Limitations of LOB Data (CLOB and BLOB Data Types)
Limitations of the CLOB and BLOB data types, collectively referred to as LOB data, are:
LOB columns can only be in the target column list of these SQL statements:
INSERT statement
Select list of a SELECT statement
Column name in the SET clause of an UPDATE statement
LOB columns cannot be referenced in the SQL/MX functions and expressions.
LOB data is not deleted from the LOB table when the base row is deleted unless a trigger is established. For information about triggers, see
Using SQL/MX Triggers to Delete LOB Data.
LOB data is not accessible if the base table name is changed.
LOB columns cannot be copied to another table by using the SQL/MX utility commands.
The name of a base table that has CLOB or BLOB columns must be unique across all catalogs and schemas when more than one of these
base tables share a single LOB table.