Configure sbspaces for temporary smart large objects

Applications can use temporary smart large objects for text, image, or other user-defined data types that are only required during the life of the user session. These applications do not require logging of the temporary smart large objects. Logging adds I/O activity to the logical log and increases memory utilization.

You can store temporary smart large objects in a permanent sbspace or a temporary sbspace.

  • Permanent sbspaces

    If you store the temporary smart large objects in a regular sbspace and keep the default no logging attribute, changes to the objects are not logged, but the metadata is always logged.

  • Temporary sbspaces

    Applications that update temporary smart large objects stored in temporary sbspaces are significantly faster because the database server does not log the metadata or the user data in a temporary sbspace.

To improve performance of applications that update temporary smart large objects, specify the LOTEMP flag in the mi_lo_specset_flags or ifx_lo_specset_flags API function and specify a temporary sbspace for the temporary smart large objects. The database server uses the following order of precedence for locations to place temporary smart large objects:
  • The sbspace you specify in the mi_lo_specset_sbspace or ifx_lo_specset_sbspace API function when you create the smart large object

    Specify a temporary sbspace in the API function so that changes to the objects and the metadata are not logged. The sbspace you specify in the API function overrides any default sbspaces that the SBSPACETEMP or SBSPACENAME configuration parameters might specify.

  • The sbspace you specify in the IN Sbspace clause when you create an explicit temporary table with the TEMP TABLE clause of the CREATE TABLE statement

    Specify a temporary sbspace in the IN Sbspace clause so that changes to the objects and the metadata are not logged.

  • The permanent sbspace you specify in the SBSPACENAME configuration parameter, if you do not specify an sbspace in the SBSPACETEMP configuration parameter
If no temporary sbspace is specified in any of the above methods, then the database server issues the following error message when you try to create a temporary smart large object: 
-12053  Smart Large Objects: No sbspace number specified.