Data truncation can be enabled for the following logging instances:
Server-wide logging - SQL logs the DBA-authorized data truncation event for all clients to the log files: SQL_data_truncate_server_<process-id>_<timestamp>_A.log and SQL_data_truncate_ server_<process-id>_<timestamp>_B.log. SQL begins to write to the log file SQL_data_truncate_ server_<process-id>_<timestamp>A.log and if the size of this file exceeds 500 MB, the log information is continued to be written in the file SQL_data_truncate_ server_<process-id>_<timestamp>B.log. If the size of this file also exceeds 500mb, SQL starts logging data to the previous file – SQL_data_truncate_ server_<process-id>_<timestamp>A.log, overwriting the previously written information. The logging process continues in this manner. A single log file is maintained for a single SQL server process. Information about all clients is logged in this file. For partitioned tables, the log information is written in the following format. The log file has a header and a body section. The logs details like table row, and partition ID for the truncated column are logged in the body.
A DBA can turn server-wide logging on or off for data truncation by executing the following command in SQL:
SET PRO_DATA_TRUNCATE_SERVER LOG <ON|OFF>
If logging is enabled for the server, the log data truncation event for all the clients is written to the process-wide log file. If logging is not enabled for the server, no information about the data truncation event is logged. By default, the server-wide logging is turned OFF (disabled).
Connection Specific Logging - To maintain connection-specific log information, SQL logs the DBA-authorized data truncation event for the current client to the client-specific log file:
SQL_data_truncate_connection_<connection-uid>_<timestamp >_A.log and SQL_data_truncate_ connection_<connection-uid>_< timestamp >_B.log. SQL begins to write to the log file SQL_data_truncate_ connection_<connection-uid>_<timestamp>_A.log and if the size of this file exceeds 500 MB, the log information continues to be written in the file SQL_data_truncate_ connection_<connection-uid>_<timestamp>_B.log. If the size of this file also exceeds 500 mb, SQL returns to the previous file and logs information in the file SQL_data_truncate_ connection_<connection-uid>_<timestamp>_A.log, overwriting the previously written information. The logging process continues in this manner.
These log files are maintained across the connection with a different log file for each client. A log file contains information only for its corresponding client.
For partitioned tables, the log information is written in the following format. The log file has a header and a body section. The body section logs contain details of the truncated column, table row, and partition ID.
Switching on connection-specific logging - A DBA can turn connection-specific logging on or off for data truncation by executing the following command in SQL:
SET PRO_DATA_TRUNCATE_CONNECTION LOG <ON|OFF>
If logging is enabled for the client, the log data truncation event is written for the current client to the client specific log file. If logging is not enabled for the connection, no information about the data truncation event is logged. By default, the connection-specific logging is turned OFF (disabled).
If the above command is executed from a client, logging is enabled only for that client and information of only that client is logged to the client-specific log file. To enable logging for individual clients, the command must be executed for each client.
There is no dependency between a server-wide log file and client-specific log file. If both are enabled, SQL writes the log information for all the clients to the server-wide log file and information of each client to the client-specific log file when any data is truncated.
If both are disabled, no log information is written to any of the files even if a data truncation event occurs. If only server-wide logging is enabled, SQL writes the log information of all the clients to the server-wide log file. No log information is written to the connection-specific log file. If only connection-specific logging is enabled, SQL writes the log information of the client to the client-specific log file. No log information is written to the server-wide log file.
For example, if there are five clients and both server-wide and connection-specific log files are enabled, five connection-specific files and a server-wide log file (a total of six log files) are generated.
Server-wide logging - SQL logs the DBA-authorized data truncation event for all clients to the log files: SQL_data_truncate_server_<process-id>_<timestamp>_A.log and SQL_data_truncate_ server_<process-id>_<timestamp>_B.log. SQL begins to write to the log file SQL_data_truncate_ server_<process-id>_<timestamp>A.log and if the size of this file exceeds 500 MB, the log information is continued to be written in the file SQL_data_truncate_ server_<process-id>_<timestamp>B.log. If the size of this file also exceeds 500mb, SQL starts logging data to the previous file – SQL_data_truncate_ server_<process-id>_<timestamp>A.log, overwriting the previously written information. The logging process continues in this manner. A single log file is maintained for a single SQL server process. Information about all clients is logged in this file. For partitioned tables, the log information is written in the following format. The log file has a header and a body section. The logs details like table row, and partition ID for the truncated column are logged in the body.