Using Sbspace Logging

When an sbspace is logged, the database server slows down, and the logical logs fill up quickly. If you use logging for sbspaces, you must ensure that the logical logs are large enough to hold the logging data. For more information, see Estimating the Log Size When Logging Smart Large Objects.

When you turn on logging for a database, the database server does not begin logging until you perform a level-0 backup. However, when you turn on logging for a smart large object, the database server begins logging changes to it immediately. To reduce the volume of log entries, you could load smart large objects with logging turned off and then turn logging back on to capture updates to the smart large objects.

For more information, see Backing Up Sbspaces and the IBM Informix Backup and Restore Guide.

Choosing Logging for Smart Large Objects

Use logging for smart large objects if users are updating the data frequently or if the ability to recover any updated data is critical. The database server writes a record of the operation (insert, update, delete, read, or write) to the logical-log buffer. The modified portion of the CLOB or BLOB data is included in the log record.

To increase performance, turn off logging for smart large objects. Also turn off logging if users are primarily analyzing the data and updating it infrequently, or if the data is not critical to recover.

Logging for Updated Smart Large Objects

When you update a smart large object, the database server does not log the entire object. Assume that the user is writing X bytes of data at offset Y with logging enabled for smart large objects. The database server logs the following:

• If Y is set to the end of the large object, the database server logs X bytes (the updated byte range).
• If Y is at the beginning or in the middle of the large object, the database server logs the smallest of these choices:
  • Difference between the old and new image
  • Before-image and after-image
  • Nothing is logged if the before- and after-images are the same

Turning Logging On or Off for an Sbspace

If you want to use logging in an sbspace, specify the -Df LOGGING=ON option of the onspaces command when you create the sbspace. If logging is turned off in the sbspace, you can turn on logging for smart large objects in specific columns. One column that contains smart large objects could have logging turned on while another column has logging turned off.

To verify that smart large objects in an sbspace are logged, use the following command:

oncheck -pS sbspace | grep "Create Flags"

If you create smart large objects in the sbspace with the default logging option and you see the LO_NOLOG flag in the output, the smart large objects in this sbspace are not logged. If you see the LO_LOG flag in the output, all smart large objects in this sbspace are logged.

You can modify the logging status of an sbspace in any of the following ways.