When column statistics are updated in a database, a sample of column values is collected and stored in persistent system tables associated with statistics. These sampled values, along with other statistical metadata, are used by the query optimizer to generate an efficient query execution plan.

If the database tables are enabled for Transparent Data Encryption (TDE), the sampled column values are encrypted before being stored in these persistent system tables. To control the encryption behavior, a Database Administrator (DBA) can execute the ALTER DATABASE SET COLUMN STATISTICS command to specify the cipher used for encrypting column values in TDE-enabled tables before they are stored in system tables.

Syntax

The syntax for the ALTER DATABASE SET COLUMN STATISTICS command is as follows:
ALTER DATABASE SET COLUMN STATISTICS CIPHER '<CIPHER_CHOICE>' [WITH DELETE]
CIPHER <CIPHER_CHOICE>
Defines the cipher to be used for encrypting column statistics. The supported cipher choices are:
  • AES_CFB_128
  • AES_CFB_256
WITH DELETE
This optional clause:
  • If included—Deletes only the encrypted column statistics data from the system tables for TDE-enabled user tables. This operation does not affect column statistics for non-TDE tables.
    Note: After executing this SQL statement with the WITH DELETE option, it is recommended to regenerate column statistics for all TDE-enabled tables, as the encrypted column statistics data for these tables will have been removed.
  • If omitted—Performs re-encryption of the existing encrypted column statistics, which involves:
    • Decrypting the current encrypted statistics using the existing cipher.
    • Re-encrypting them using the newly specified cipher.
Note: In both cases, whether WITH DELETE is included or omitted, the new cipher is recorded in the system table to ensure consistency for future encryption operations.

Examples

The encryption cipher for encrypting column statistics in TDE-enabled tables is changeable. The following command sets it to AES_CFB_256 and then re-encrypts any existing column statistics using the newly specified AES_CFB_256 cipher: 
ALTER DATABASE SET COLUMN STATISTICS CIPHER 'AES_CFB_256'
The following command sets the encryption cipher to AES_CFB_128. The WITH DELETE clause deletes any existing encrypted column statistics data from system tables for TDE-enabled user tables before applying the new cipher: 
ALTER DATABASE SET COLUMN STATISTICS CIPHER 'AES_CFB_128' WITH DELETE
Note: After executing this command with the WITH DELETE option, it is recommended to regenerate column statistics for all TDE-enabled tables, as their encrypted statistics data will have been removed.

Error handling

The following table outlines various error scenarios that may occur when executing the ALTER DATABASE SET COLUMN STATISTICS CIPHER command, along with example commands, corresponding error messages, and the resulting system behavior.
Scenario Example command Error message Result
Invalid cipher name 
ALTER DATABASE SET COLUMN STATISTICS CIPHER 'AES_CB_200';
Invalid encryption specification. (15265) Operation aborts and no changes are made to the encryption configuration.
Unsupported cipher 
ALTER DATABASE SET COLUMN STATISTICS CIPHER 'RC4_ECB_128';
 Invalid encryption specification. (15265) Command fails gracefully, and no changes are applied to the encryption configuration.
Null or empty cipher 
ALTER DATABASE SET COLUMN STATISTICS CIPHER '';
Invalid encryption specification. (15265) Command fails gracefully, and no changes are applied to the encryption configuration.
Insufficient privileges Command is executed by non-DBA user Access denied (Authorization failed) (7512) The command is denied. You should have DBA privileges to perform this operation.

Effects of cipher change for column statistics

When the cipher for column statistics is successfully changed using the ALTER DATABASE SET COLUMN STATISTICS CIPHER command, it affects both the current session and other active sessions in the database environment. This section describes the behavior and implications of this change.
  • Impact on the current session—When you successfully execute the ALTER DATABASE SET COLUMN STATISTICS CIPHER command, all new SQL queries executed in the session use the updated cipher for encryption and decryption of column statistics.
  • Impact on other active sessions—When you successfully execute the ALTER DATABASE SET COLUMN STATISTICS CIPHER command, the following changes affect other sessions across the database environment:
    • All active sessions are notified of the cipher update.
    • Subsequent queries in all sessions are used with the new cipher for encryption and decryption of column statistics.

Notes

  • The ALTER DATABASE SET COLUMN STATISTICS is an online operation.
  • You must have SQL DBA privileges to set the encryption cipher used for column statistics in TDE-enabled database.
  • Setting the cipher for column statistics in TDE-enabled tables does not affect the cipher used to encrypt the actual table data.
  • If the ALTER DATABASE SET COLUMN STATISTICS CIPHER command is not executed, the system defaults to using the AES_CFB_128 cipher for encryption of column data in column statistics for TDE enabled tables.
  • After you have set a new cipher using the ALTER DATABASE SET COLUMN STATISTICS CIPHER command, the OpenEdge SQL engine uses the newly specified cipher when executing UPDATE [ALL] COLUMNS STATISTICS to encrypt column statistics data of TDE-enabled tables. If existing column statistics are present at the time the ALTER DATABASE SET COLUMN STATISTICS CIPHER command is executed, they must be re- encrypted using the newly specified cipher unless the WITH DELETE clause is explicitly included, in which case the existing statistics are deleted prior to applying the new cipher.

    For more information, see UPDATE STATISTICS.