Procedures added for disabling LOB compression [CR 352758]

In ESD #4, two stored procedures were added to control the compression (done when writing database buffers to disk) for columns of data type LOB. This functionality saves CPU cycles because certain data formats stored in a LOB column (e.g., jpg files) are already compressed and gain nothing from additional compression.

Sets compression for LOB/CLOB data types (long binary and long varchar, respectively).

call dbo.sp_iqsetcompression ( owner, table, column, on_off_flag )

Requires DBA authority.

The sp_iqsetcompression system stored procedure lets you control compression of LOB/CLOB audiotape columns. The compression setting only applies to IQ base tables.

A side effect of sp_iqsetcompression is that a COMMIT occurs after the compression setting is changed.

Example 3

For this example, assume the following table definition:

CREATE TABLE USR.pixTable (picID INT NOT NULL,
picJPG LONG BINARY NOT NULL);

To turn off compression on the LOB column picJPG, call the sp_iqsetcompression procedure using the following command (you must have DBA permission):

CALL sp_iqsetcompression('USR', 'pixTable', 'picJPG',
'OFF') ;

This command returns no rows.

Displays compression settings for columns of LOB/CLOB data types (long binary and long varchar, respectively).

call dbo.sp_iqshowcompression ( owner, table, column )

Requires DBA authority.

Returns the column name and compression setting. Compression setting values are 'ON' (compression enabled) or 'OFF' (compression disabled).

Example 4

For this example, assume the following table definition:

CREATE TABLE USR.pixTable (picID INT NOT NULL,
picJPG LONG BINARY NOT NULL);

To check the compression status of the columns in the pixTable table, call the sp_iqshowcompression procedure using the following command (you must have DBA permission):

CALL sp_iqshowcompression('USR', 'pixTable',
'picJPG') ;

This command returns one row:

'picJPG','ON'