Moving Data to LOBs in a Threaded Environment

Incorrect procedure

The following sequence, requires a new connection when using a threaded environment, adversely affects performance, and is inaccurate:

1. Create an empty (non-NULL) LOB

2. INSERT using the empty LOB

3. SELECT-FOR-UPDATE of the row just entered

4. Move data into the LOB

5. COMMIT. This releases the SELECT-FOR-UPDATE locks and makes the LOB data persistent.

The Correct Procedure

Note: There is no need to 'create' an empty LOB. You can use the RETURNING clause as part of the INSERT/UPDATE statement to return a locked LOB locator. This eliminates the need for doing a SELECT-FOR-UPDATE, as mentioned in step 3.

Hence the preferred procedure is as follows:

1. INSERT an empty LOB, RETURNING the LOB locator.

2. Move data into the LOB using this locator.

3. COMMIT. This releases the SELECT-FOR-UPDATE locks, and makes the LOB data persistent.

Alternatively, you can insert >4,000 byte of data directly for the LOB columns but not the LOB attributes.